| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: CREATE USER DYNAMICALLY USING PL/SQL?
A copy of this was sent to "Ellie Fillmore" <efillmore_at_earthlink.net>
(if that email address didn't require changing)
On Tue, 2 Mar 1999 21:45:51 -0500, you wrote:
>I have a need to create a new oracle user upon insertion of a row into a
>table. Right now it is a manual process and we need to automate it. I have
>tried with a stored procedure using DBMS_SQL.PARSE but I cannot get past the
>create user portion of it. Any suggestions or even code if available would
>help??!!
>Thanks,
>Ellie
>
>
>
I create a procedure like:
create or replace procedure execute_immediate( sql_stmt in varchar2 )
as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );
end;
and then from other procedures I can:
...
execute_immediate( 'create user bob identified by hello' );
...
For this to work, you need to make sure that the OWNER of the execute_immediate procedure has the correct privs granted directly to them (eg: you will need to grant CREATE USER to the owner of the above procedure for it to work).
Also, if you are attempting to do this from a trigger it won't work since DDL (create is DDL) commits and you cannot commit in a trigger. The way to do DDL from a trigger in 8.0 and less is to use dbms_job. You would write a procedure that is run in the background AFTER you commit (that is a nice side effect actually, if the insert rollsback -- the create user doesn't happen)....
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |