Re: Dynamic PL/SQL in proc called from trigger

Paul Passarelli wrote:
>
> I'm trying to issue a CREATE USER statement
> in a trigger, but moved it to a procedure where it's now like:
>
> /* Parse and immediately execute dynamic SQL statement built by
> concatenating user name to CREATE USER command. */
> dbms_sql.parse( cid,
> 'CREATE USER ' || tUserName ||
> ' IDENTIFIED BY ' || tPassword ||
> ' DEFAULT TABLESPACE user_data' ||
> ' TEMPORARY TABLESPACE temporary_data' ||
> ' QUOTA UNLIMITED ON user_data' ||
> ' QUOTA UNLIMITED ON temporary_data',
> dbms_sql.v7 ) ;
>
> but this generates an exception ORA-04092 cannot COMMIT in trigger, because
> DDL has an implicit commit, AFAIK.

A trigger cannot contain DDL or transaction control statements nor can it call stored procedures which do. Source: Oracle7 Server Application Developer's Guide, pg. 9-10.

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"