DDL and Dynamic SQL

From: <mmellin_at_my-dejanews.com>
Date: Wed, 04 Nov 1998 17:19:14 GMT
Message-ID: <71q2ah$8k9$1@nnrp1.dejanews.com>

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 - 11:19:14 CST