creating trigger with dbms_sql causes ORA-01912

herzal

I use the following procedure to execute statements that are stored in clobs. These statements are very long 'create trigger' statements.

SQL> create or replace procedure executeClob(clob_stmt in clob) is
  2  
  3      n_size number := ceil(dbms_lob.getlength(clob_stmt) / 255);
  4      n_counter number;
  5      
  6      t_lines dbms_sql.varchar2s; 
  7      i_cursor integer := dbms_sql.open_cursor;
  8  begin
  9   
 10      for n_counter in 1..n_size loop
 11          t_lines (n_counter) := to_char(substr(clob_stmt, 1 + 255 * (n_counter - 1), 255));
 12      end loop;
 13      
 14      dbms_sql.parse(i_cursor, t_lines, t_lines.FIRST, t_lines.LAST, false, DBMS_SQL.native);
 15      dbms_sql.close_cursor(i_cursor);
 16  end;
 17  /

Every time I call this procedure, I get the following error

ORA-01912: keyword ROW expected
ORA-06512: in "SYS.DBMS_SYS_SQL", Line 1485
ORA-06512: in "SYS.DBMS_SQL", Line 26
ORA-06512: in "NTSDEV6.EXECUTECLOB", Line 14
ORA-06512: in "NTSDEV6.CREATELOGTRIGGER", Line 150

Has anyone an idea what the heck is going on? I can't explain that ORA-01912.

Thanks for your help

Peter Gjelstrup

Herzal wrote:
yes, the create-statement works fine when I paste it directly into sql*plus

When and how did you test that? - Before putting it into the clob of after splitting clob up into strings.

Have you tried writing out the contents of your collection before parsing it? - It sounds like a missing chr(10) after FOR EACH ROW


Regards
Peter

Karthick2003

Has anyone an idea what the heck is going on? I can't explain that ORA-01912.

Did you check for the syntax of the Trigger in the CLOB.

herzal

yes, the create-statement works fine when I paste it directly into sql*plus

Peter Gjelstrup

Herzal wrote:
yes, the create-statement works fine when I paste it directly into sql*plus

When and how did you test that? - Before putting it into the clob of after splitting clob up into strings.

Have you tried writing out the contents of your collection before parsing it? - It sounds like a missing chr(10) after FOR EACH ROW


Regards
Peter

herzal

For the second time: Thank you, Peter

I removed the chr(10) a few hours ago because I thought they would cause a problem, stupid me =p

Now I get another error, ORA-01031: insufficient privileges, but that's something I have to dig into first.

Again: thank you