Re: Create user in stored procedure

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 13 Jul 1999 18:23:42 GMT
Message-ID: <378b8240.20074375@inet16.us.oracle.com>

On Tue, 13 Jul 1999 09:57:06 -0400, "Terry Sharp" <tsharp_at_witsys.com> wrote:

>I have a procedure that accepts two varchar2 variables as input (say a
>v_login and v_password). I check to see if the login exists in dba_users.
>If not I'd like to issue the create user v_login identified by v_password
>command within the stored procedure. Is there a way to do this?
>

Yes,

you could create a procedure like...

  procedure execute_immediate( p_command varchar2 ) is     l_cursor number;
    l_status number;
  begin
    l_cursor := dbms_sql.open_cursor;
    dbms_sql.parse( l_cursor, p_command, dbms_sql.native );     l_status := dbms_sql.execute( l_cursor );     dbms_sql.close_cursor( l_cursor );
  exception
    when others then

      if dbms_sql.is_open( l_cursor ) then
        dbms_sql.close_cursor( l_cursor );
      end if;
      raise;

  end execute_immediate;

then your create_user procedure could be...

  procedure create_user(

    p_username varchar2,
    p_password varchar2,
    p_default_ts varchar2 default null,
    p_temp_ts varchar2 default null ) is

  --
    l_command long;
  begin
    l_command := 'create user ' || p_username ||

                 ' identified by ' || p_password;

    if p_default_ts is not null then
      l_command := l_command || ' default tablespace ' || p_default_ts;

    end if;

    if p_temp_ts is not null then
      l_command := l_command || ' temporary tablespace ' || p_temp_ts;     end if;

    execute_immediate( l_command );

  end create_user;

NOTE: Make sure the owner of the execute_immediate procedure has the privilege 'CREATE USER' granted directly to them.

hope this helps.

chris.

>TIA
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jul 13 1999 - 13:23:42 CDT