RE: Creating users via PL/SQL - Example

>dstrait_at_omni.voicenet.com wrote (with deletions):
>
> I am trying to write a small package to create a user. While I can get
> this to work through a script, PL/SQL doesn't seem to know how to deal
> with the keywords CREATE or GRANT. I've looked through the PL/SQL
> User's guide and grepped through the scripts that Oracle installs,
> but I can't find anything about creating users in a package. Can
> someone point me in the correct direction?

The following example should work. In this example the procedure is created in the SYS account. A procedure like this must be created in an account that has been granted the privileges to create new user accounts. My previous response to this posting cited two references for using the "dbms_sql" package and dynamic sql.

create procedure sys.make_user (name in varchar2, password in varchar2) is

--
-- Sample stored procedure used to create a new Oracle user. 
--
declare
   create_command    varchar2 (200);
   command_cursor    integer;
   ignored_status    integer;
begin
   command_cursor := dbms_sql.open_cursor;
   create_command := 'CREATE USER ' || name || ' IDENTIFIED BY ' || 
      password;
   dbms_sql.parse (command_cursor, create_command, dbms_sql.native);
   ignored_status := dbms_sql.execute (command_cursor);
   dbms_sql.close_cursor (command_cursor);
exception
   when others then
      dbms_output.put_line (SQLERRM);
      if dbms_sql.is_open (command_cursor) then
         dbms_sql.close_cursor (command_cursor);
      end if;
      raise;
end;      
/