Re: stored procedure

Koen Van Tolhuyzen <Koen.VanTolhuyzen_at_cronos.be> wrote in message news:7v1h4a$l2o$1_at_news3.Belgium.EU.net...
> 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.
>
> 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

1. Eliminate the semicolon ';' in the end of SQL statement.
2. Put a space before 'IDENTIFIED BY'. That's why the error ORA-922 occured.
3. Make sure the definer has the directly granted privileges. In default, only SYS (but not SYSTEM) has such privileges.