Forum Stats

  • 3,839,816 Users
  • 2,262,538 Discussions
  • 7,901,063 Comments

Discussions

creating trigger with dbms_sql causes ORA-01912

herzal
herzal Member Posts: 26
edited Aug 19, 2009 10:49AM in SQL & PL/SQL
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 =)
Tagged:

Best Answer

  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
    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

Answers

This discussion has been closed.