Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> plsql create PROCEDURE, PROCEDURE to Create user
shay_b
post Jan 19 2012, 01:11 PM
Post #1


Advanced Member
***

Group: Members
Posts: 184
Joined: 20-July 05
Member No.: 2,545



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
Go to the top of the page
 
+Quote Post
burleson
post Jan 19 2012, 07:26 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,437
Joined: 26-January 04
Member No.: 13



Hi Shay,

You forgot your declare keyword!

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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
shay_b
post Jan 22 2012, 03:03 AM
Post #3


Advanced Member
***

Group: Members
Posts: 184
Joined: 20-July 05
Member No.: 2,545



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.
Go to the top of the page
 
+Quote Post
burleson
post Jan 22 2012, 02:41 PM
Post #4


Advanced Member
***

Group: Members
Posts: 11,437
Joined: 26-January 04
Member No.: 13



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 . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
shay_b
post Jan 23 2012, 03:05 AM
Post #5


Advanced Member
***

Group: Members
Posts: 184
Joined: 20-July 05
Member No.: 2,545



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"?
Go to the top of the page
 
+Quote Post
dvsoukup
post Jan 23 2012, 03:09 PM
Post #6


Newbie
*

Group: Members
Posts: 7
Joined: 13-January 12
Member No.: 46,588



QUOTE (shay_b @ Jan 23 2012, 12:05 AM) *
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.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 3rd September 2014 - 01:59 AM