DDL in Stored Procedure

[coolmandba]

Hi all,

I'm trying to have these lines below as part of a procedure

cSql_statement:='create user '||AddNewFITSUser.MDSUser||' identified by '||AddNewFITSUser.MDSUser;

execute immediate cSql_statement;

cSql_statement:='grant maintenance to '||AddNewFITSUser.MDSUser;

execute immediate cSql_statement;

compiler didn't complain nor when I was calling the procedure, it said procedure executed successfully. But when I check the user, it wasn't created. Anybody know why?

Thanks.

[akkerend]

Maybe there is an error, but it's handled in the exception handler, something like:
WHEN OTHERS THEN
NULL;

[coolmandba]

Can't found what's wrong! Tried everything possible.

[sambavan]

I doubt that you can run the DDL in a procedure. The way to do this would be to call the dbms_sql.execute and pass the statement to that. That would help you to execute the DDL, I hope. It had been a v.long time I was in the coding.

Sam

[mber]

Run DDL in procedure like this way is possible, i suspect some role related problems? Try set role none or all separately, and then run test the code.

Take care