Dynamilly creating users from a table 2

[tonyblacks]

I am trying to dynamically create new users from a table using a trigger on insert. I tried writing a procedure that would be called once the trigger was fired. Unfortunately, My procedure compiles with errors.My assumption is that you can't write Create statements in a procedure.
Please advise.

 

[rcurva]

Your assumption is correct if you did not use dynamic sql. You can isssue DDL (create, drop, truncate) statements inside a stored procedure by using this feature.

Syntax;

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {define_var1 [, define_var2] ... | plsql_record}]
[USING [IN | OUT | IN OUT] bind_arg1 [,
[IN | OUT | IN OUT] bind_arg2] ...]
[{RETURNING | RETURN} INTO bind_arg3 [, bind_arg4] ...];

Simple Example;

create or replace procedure sample_proc is
str varchar2(200);
val varchar2(20);
begin
str := 'CREATE USER :b1 IDENTIFIED BY psword_here';
val := 'samp_usr1';
EXECUTE IMMEDIATE str USING val;
end;


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[jee]

If you want to do call this stored procedure from a trigger, it will have to be set up as an Autonomous Transaction. This is because issuing DDL does an implicit commit and you normally cannot commit from within a trigger (but you can get around that using an autonomous transaction).