| PL/SQL Error [message #38991] |
Tue, 04 June 2002 10:50  |
Matt
Messages: 43
Registered: September 1999
|
Member |
|
|
I've recently started Oracle programming. I'm trying to create a simple procedure to creat a user using a stored variable. However, because I'm fairly new at SQL statement's I've run into an error. Please have a look and tell me what I've done wrong.
Thanks
DECLARE
v_userid VARCHAR2 (9) DEFAULT 'User1';
v_passwrd VARCHAR2 (9) DEFAULT 'User1';
BEGIN
CREATE USER v_userid
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
IDENTIFIED BY v_passwrd ;
GRANT viewA viewB viewC TO v_userid;
ALTER USER v_userid DEFAULT ROLE roleA roleB roleC FROM DBA_USERS
WHERE USERNAME <> 'SYS' AND USERNAME <> 'SYSTEM';
END;
/
|
|
|
|
| Re: PL/SQL Error [message #38992 is a reply to message #38991] |
Tue, 04 June 2002 11:53  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You cannot directly execute DDL in PL/SQL. However, you can do this using dynamic SQL. Here is an 8i example:
execute immediate
'create user ' || v_userid ||
' identified by ' || v_passwrd ||
' default tablespace users' ||
' temporary tablespace temp';
execute immediate 'grant ... to ' || v_userid; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
