Re: Oracle "IF" Question

Okay, I have tried your suggestion, and I no longer get an “Invalid SQL Statement” error. Unfortunately, the command does not work. Here is the command that I am running. Forgive the fact that it is all on one line; it has to be this way, because I have to pass the SQL line to CDatabase::ExecuteSQL (on Windows NT).

declare l_found boolean; l_cursor number; l_status number; begin for c in (select * from sys.dba_users where username = 'ADMIN') loop l_found := true; end loop; if not l_found then l_cursor := dbms_sql.open_cursor; dbms_sql.parse(l_cursor, 'CREATE USER ADMIN IDENTIFIED BY password', dbms_sql.native); l_status := dbms_sql.execute(l_cursor); dbms_sql.close_cursor(l_cursor); end if; end;

I am using system/manager to log into the Oracle server ODBC connection, and I am using Oracle 8.1.5i. I can create the user using CREATE USER directly on the same login, so I assume privileges are set correctly.

Please let me know what I might be doing wrong.

Thanks,
Steve

Christopher Beck wrote:

> 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 Tue Aug 03 1999 - 10:48:01 CDT