Re: Oracle "IF" Question

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 02 Aug 1999 18:21:40 GMT
Message-ID: <37a5df3f.20335861@inet16.us.oracle.com>

On Mon, 02 Aug 1999 16:41:55 GMT, Steven McAllister <smcallister_at_softblox.com> wrote:

>Hello. I am new to SQL and Oracle, and I have a script that I'm trying
>to run on an Oracle database. However, the following line fails; the
>interpreter claims that it is an invalid SQL statement:
>
>IF NOT EXISTS (SELECT * FROM sys.DBA_USERS WHERE username = 'Admin')
>CREATE USER Admin IDENTIFIED BY password
>
>Does any have any ideas what I am doing wrong?

Yup,

You can't use the if like that. Also if you want to create a user via pl/ql you will need to use dynamic sql.

eg.

procedure create_user(

  p_username varchar2,
  p_password varchar2 ) is
  l_cursor number;
  l_status number;
  l_found boolean := false;

begin

  for c in ( select *

               from sys.dba_users
              where username = p_username )

  loop
    l_found := true;
  end loop;

  if not l_found then
    l_cursor := dbms_sql.open_cursor;
    dbms_sql.parse( l_cursor. 'create user ' || p_username ||

                              ' identified by ' || p_password,
                    dbms_sql.native );

    l_status := dbms_sql.execute( l_cursor );     dbms_sql.close_cursor( l_cursor );
  end if;

end create_user;
/

NOTE:
The owner of this procedure will need the privileges 'create user' and 'select on dba_users' granted directly to them and not via a role, since roles are not enabled within pl/sql.

ANOTHER NOTE:
If you are using Oracle 8i ( 8.1.5 ) then you can rewrite the if-then to read:

    if not l_found then

      execute immediate 'create user ' || p_username ||
                        ' identified by ' || p_password;

    end if;

Dynamic SQL is native in pl/sql in 8i.

hope this helps.

chris.

>
>Thanks,
>Steve

--
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 Mon Aug 02 1999 - 13:21:40 CDT