Re: DDL and Dynamic SQL

Hey Mike,

     Roles are disabled when compiling and executing PL/SQL code. You may have been running the stored procedure as System, but if System doesn't have CREATE USER explicitly granted to it, sp_adduser won't work.

Try the following (assuming you have a "mike" account): log into SQL*Plus as system.
 grant ADD USER to Mike
 connect mike/password_at_system
 @c:\temp\adduser.sql
 BEGIN
 sp_adduser('jay', 'foo', 'tablespace', 'temp_table');  END;
 /

     I don't believe you need to give exec_user a default value, you can change to:

exec_user INTEGER;

...and in your PARSE statement, you don't need to terminate the string with two single quotes, change to:

QUOTA UNLIMITED ON '||temp_tablespace_in, DBMS_SQL.V7);

Jay!!!

mmellin_at_my-dejanews.com wrote:

> I am converting a MSSQL database. In our application we utilize the
> sp_adduser stored procedure for MSSQL. The developers want me to duplicate
> the procedure in Oracle. This is what I have so far . I run the procedure as
> System and get insufficient priveledges error messages. This happens when I
> try to execute any DDL command with Dynamic Sql.
>
> CREATE OR REPLACE PROCEDURE RESPONSENOW.SP_ADDUSER
> (username_in IN VARCHAR2,
> password_in IN VARCHAR2,
> tablespace_in IN VARCHAR2,
> temp_tablespace_in IN VARCHAR2)
> IS
> adduser_cursor INTEGER;
> exec_user INTEGER := 0;
>
> BEGIN
> adduser_cursor := dbms_sql.open_cursor;
> dbms_sql.parse(adduser_cursor,
> 'CREATE USER '||username_in||'
> IDENTIFIED BY '||password_in||'
> DEFAULT TABLESPACE '||tablespace_in||'
> TEMPORARY TABLESPACE '||temp_tablespace_in||'
> QUOTA UNLIMITED ON '||tablespace_in||'
> QUOTA UNLIMITED ON '||temp_tablespace_in||'' , DBMS_SQL.V7);
> exec_user := dbms_sql.execute(adduser_cursor);
> dbms_sql.close_cursor(adduser_cursor);
> END;
>
> Thank you for any help.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Wed Nov 04 1998 - 13:09:40 CST