Re: Dynamical generating of Oracle triggers problem

Alexander Miroshnikov <alex_dba_at_hotmail.com> wrote in message news:7mvjet$pic$1_at_nclient3-gui.server.ntli.net...
> Dear Sir,
> ---- > Oracle 7.3.3 < ----
>
> Connected to:
> Oracle7 Server Release 7.3.3.0.0 - Production Release
> With the distributed option
> PL/SQL Release 2.3.3.0.0 - Production
>
> SQL> declare
> 2 cursordesc integer;
> 3 resultdesc integer;
> 4 begin
> 5 cursordesc := dbms_sql.open_cursor;
> 6 dbms_sql.parse(cursordesc,'create or replace trigger
> test_beforeinsert before insert on test for each row begin null;
> end',dbms_sql.v7);
> 7 resultdesc := dbms_sql.execute(cursordesc);
> 8 dbms_sql.close_cursor(cursordesc);
> 9 end;
> 10 /
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> ---- > Oracle 8.1.5 < ----
>
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
> With the Partitioning and Java options
> PL/SQL Release 8.1.5.0.0 - Production
>
> SQL> declare
> 2 cursordesc integer;
> 3 resultdesc integer;
> 4 begin
> 5 cursordesc := dbms_sql.open_cursor;
> 6 dbms_sql.parse(cursordesc,'create or replace trigger
> test_beforeinsert before insert on test for each row begin null;
> end',dbms_sql.v7);

You should put a semi-colon after the "end". Lacking of the semi-colon causes the syntex error.

> 7 resultdesc := dbms_sql.execute(cursordesc);
> 8 dbms_sql.close_cursor(cursordesc);
> 9 end;
> 10 /
> ERROR:
> ORA-24344: success with compilation error
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 487
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at line 6
Received on Tue Jul 20 1999 - 23:38:04 CDT