plsql create PROCEDURE, PROCEDURE to Create user Options

[shay_b]

Hi i need to create PROCEDURE to create user in oracle

CREATE OR REPLACE PROCEDURE "CREATE_USER_ORACLE8"
(

USER_ID in VARCHAR2,
PASSWORD in VARCHAR2,
ROLES in VARCHAR2,
nReturnCode OUT NUMBER
)

BEGIN

CREATE USER user_id PROFILE DEFAULT
IDENTIFIED BY password DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;
GRANT roles TO user_id;

lastError := SQLCODE;
IF ( nReturnCode != 0 )
THEN
DBMS_OUTPUT.put_line ('User user_id existing in the database');


ROLLBACK;
RETURN;

END;
/



Compilation errors for PROCEDURE NOG.CREATE_USER_ORACLE8

Error: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:

; is with authid deterministic parallel_enable as
Line: 9
Text: BEGIN

i want that the custumer execute PROCEDURE (user_id,password,PROCEDURE )

Thanks

[burleson]

Hi Shay,

You forgot your declare keyword!

Did you issue the "show errors" command after you tested this?

[shay_b]

SQL> CREATE OR REPLACE PROCEDURE CREATE_USER_ORACLE8
2 (
3 USER_ID in VARCHAR2,
4 PASSWORD in VARCHAR2,
5 ROLES in VARCHAR2,
6 nReturnCode OUT NUMBER
7 )
8
9 BEGIN
10
11 CREATE USER user_id PROFILE DEFAULT
12 IDENTIFIED BY password DEFAULT TABLESPACE USERS
13 TEMPORARY TABLESPACE TEMP
14 ACCOUNT UNLOCK;
15
16 GRANT roles TO user_id;
17
18 --lastError := SQLCODE;
19 --IF ( nReturnCode != 0 )
20 --THEN
21
22 --DBMS_OUTPUT.put_line ('User user_id existing in the database');
23
24 --ROLLBACK;
25 --RETURN;
26 END;
27 /

Warning: Procedure created with compilation errors.

SQL>
SQL> show error
No errors.

Procedure created with compilation errors.

[burleson]

Hi Shay,

Why didn't you add the DECLARE, like I told upi?

I don't mind heloing, you you must follow by advice!

See here, a sample PL/SQL:

http://www.dba-oracle.com/t_sample_pl_sql_code.htm

*************************************
The command is "show errors" not "show error"!

Get the Easy PL/SQL book today:

http://www.rampant-books.com/book_0501_easy_plsql.htm

It has working code snippets to help you . . .

I could easily write this for you, but then you will have learned nothing . . .

[shay_b]

YOu have an error here:

CODE

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/6 PLS-00103: Encountered the symbol "PS_ADDUSER_ORACLE8" when
expecting one of the following:
:= . ( @ %;
The symbol ":=" was substituted for "PS_ADDUSER_ORACLE8" to

Which is it?

"is begin" or "as begin"?

[dvsoukup]

YOu have an error here:

CODE

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/6 PLS-00103: Encountered the symbol "PS_ADDUSER_ORACLE8" when
expecting one of the following:
:= . ( @ %;
The symbol ":=" was substituted for "PS_ADDUSER_ORACLE8" to

Which is it?

"is begin" or "as begin"?

You can use IS or AS interchangably for oracle procedures. I believe the Oracle documentation states it that they are synonyms for each other. Could be wrong... But I know I've used either "IS" OR "AS" and it's worked fine.