-
Dynamic Sql
Hi,
I'm trying to create table, trigger and procedure with dynamic name. My script works but with compilation error in my trigger and procedure. However, if I spool the source code executed and execute it in sqlplus everything work! why???
I user Oracle 8.1.7
Here is an example on the creation of the table and a trigger:
DECLARE
var_schm_name VARCHAR2(30) := '&schema_name';
var_str VARCHAR2(1000);
BEGIN
var_str := 'CREATE TABLE '||var_schm_name||'_schema_version('||
'schema_version VARCHAR2(5),'||
'modification_date DATE,'||
'modified_by VARCHAR2(30))';
EXECUTE IMMEDIATE var_str;
var_str := 'CREATE OR REPLACE TRIGGER '||var_schm_name||'_tg_schema_version_iu'||CHR(13)||
' BEFORE INSERT OR UPDATE'||CHR(13)||
' ON '||var_schm_name||'_schema_version'||CHR(13)||
' FOR EACH ROW '||CHR(13)||
' DECLARE'||CHR(13)||
' v_nb_row NUMBER(1);'||CHR(13)||
' BEGIN'||CHR(13)||
... other code
' EXCEPTION'||CHR(13)||
' WHEN OTHERS THEN'||CHR(13)||
' RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM);'||CHR(13)||
' END;';
EXECUTE IMMEDIATE var_str;
END;
/
Thanks
DONTBSHY
-
Perhaps you could post the error message
-
-
if you dont see anything when you do show error look user_errors
-
In user_errors there is the same error than in toad...
DONTBSHY
-
may I ask why you use chr(13) instead of chr(10)
also it´s trigger error not your PL/SQL code error how do we suppose to know what´s the error if we dont see any trigger code
-
Thanks Pando,
I don't know why I used the carriage return ascii code instead of the new line code... The script works with chr(10).
Thanks a lot
DONTBSHY
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|