PL/SQL procedure

[mzhu]

Hi there,

I am very new to Oracle. Can someone show me the script to generate a PL/SQL procedure that simply creates a table using dynamic SQL?

I want to grant the EXECUTE on this procedure to other users, then other users have the privilege to create table for the user who owns the procedure.

Thanks for any help.

[timgmckee]

create or replace procedure create_table (statement_text)

cursor_num PLS_INTEGER:=DBMS_SQL.OPEN_CURSOR;

begin

dbms_sql.parse(cursor_num, statement_text, DBMS_SQL.NATIVE);
dbms_sql.execute(cursor_num);
dbms_sql.close_cursor(cursor_num);

end;

As long as the procedure owner has Create Any Table, this should work for
anyone executing the procedure, but I haven't tried it. I don't think the
Create Any Table priv will work if granted through a Role though.

-Tim