Re: stored procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 25 Oct 1999 12:41:04 -0400
Message-ID: <eocUOBzUG3jbbd1GVLVGyteOKibM@4ax.com>

A copy of this was sent to "Koen Van Tolhuyzen" <Koen.VanTolhuyzen_at_cronos.be> (if that email address didn't require changing) On Mon, 25 Oct 1999 14:08:41 +0200, you wrote:

>Hi, I created a stored procedure to create a user, but I keep on getting an
>error.
>Can anyone tell me what is wrong ? I'm using Oracle 8i.
>
>SQL> create or replace PROCEDURE CREATE_USER
> 2 (p_username IN VARCHAR2
> 3 ,p_password IN VARCHAR2)
> 4 IS
> 5 BEGIN
> 6 EXECUTE IMMEDIATE 'CREATE USER ' || p_username || 'IDENTIFIED BY ' ||
>p_password || ';' ;
> 7 EXECUTE IMMEDIATE 'GRANT CONNECT TO ' || p_username || ';' ;
> 8 end;
> 9 /
>
>Procedure created.
>

lose the semi column in the create user command. the semi colon is just a terminator used by SQLPLUS to terminate commands -- its not part of the SQL command itself.

tkyte_at_8.0> create user x identified by y; -- this will error out   2 /
create user x identified by y; -- this will error out

                             *

ERROR at line 1:
ORA-00911: invalid character

tkyte_at_8.0> create user x identified by y -- this will not   2 /

User created.

Also -- it looks like you are missing a space before the I in 'IDENTIFIED BY '. It is glueing the username and IDENTIFIED together so the command is probably:

tkyte_at_8.0> create user xidentified by y; -- this is your error   2 /
create user xidentified by y; -- this is your error

                        *

ERROR at line 1:
ORA-00922: missing or invalid option

>SQL> call create_user('fab','fab');
>call create_user('fab','fab')
> *
>ERROR at line 1:
>ORA-00922: missing or invalid option
>ORA-06512: at "SYSTEM.CREATE_USER", line 6
>ORA-06512: at line 1
>
>Thanks
>
>Koen
>Koen.VanTolhuyzen_at_cronos.be
>
>

--
See http://osi.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 Mon Oct 25 1999 - 11:41:04 CDT