| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle "IF" Question
A copy of this was sent to Steven McAllister <smcallister_at_softblox.com>
(if that email address didn't require changing)
On Tue, 03 Aug 1999 15:48:01 GMT, you wrote:
>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.
>
line breaks are a good thing :)
it is the logic. you have code like this:
SQL> declare
2 l_found boolean;
3 begin
4 dbms_output.put_line( 'running test...' ); 5 for x in ( select * from all_users where username = 'NOT A USER' ) 6 loop 7 l_found := TRUE; 8 end loop; 9 9 if ( not l_found ) then 10 dbms_output.put_line( 'l_found is FALSE' ); 11 elsif ( l_found ) then 12 dbms_output.put_line( 'l_found is TRUE' ); 13 end if; 14 dbms_output.put_line( 'done...' );15 end;
PL/SQL procedure successfully completed.
l_found is neither TRUE nor is it FALSE. It is NULL which is not true or false (tri-valued logic)....
default l_found to be FALSE or NVL() it....
1 declare
2 l_found boolean default FALSE;
3 begin
4 dbms_output.put_line( 'running test...' ); 5 for x in ( select * from all_users where username = 'NOT A USER' ) 6 loop 7 l_found := TRUE; 8 end loop; 9 if ( not l_found ) then 10 dbms_output.put_line( 'l_found is FALSE' ); 11 elsif ( l_found ) then 12 dbms_output.put_line( 'l_found is TRUE' ); 13 end if; 14 dbms_output.put_line( 'done...' );15* end;
>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
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 03 1999 - 11:51:14 CDT
 |
 |