Re: Dynamical generating of Oracle triggers problem

Alexander Miroshnikov wrote:
>
> Dear Sir,
>
> I have been using the dynamical generating of Oracle triggers utilising
> DBMS_SQL package on Oracle 7.3.3. I have found that on Oracle 8.1.5 my
> trigger creating code stopped working. Is there any way to make it working?
>
> ---- > 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);
> 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
>
> SQL>
>
> Thank you,
> Alex.

your code ran, but your trigger did not compile...

use "show errors trigger trigger_name" and see what oracle had to say...

HTH

-- 
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"
connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue."