-
I have recently started Oracle script's and have run into an error with my PL/SQL. Can anyone tell me what I'm doing 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;
/
-
DECLARE
/*
|| Procedure for dynamically executing SQL statements,
|| needed to execute DDL commands.
*/
PROCEDURE execute_sql_statement (sql_statement IN VARCHAR2)
IS
cursor_handle INTEGER;
BEGIN
cursor_handle := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE
(cursor_handle, sql_statement, DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR(cursor_handle);
END execute_sql_statement;
BEGIN
execute_sql_statement
('CREATE USER user1 identified by user1
default tablespace users
temporary tablespace temp');
and so on for other statements...
END;
-
Thank you, I'll try this out
-
if you are using 8i it will be much easier to do this:
EXECUTE IMMEDIATE 'CREATE USER '||v_userid||'
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
IDENTIFIED BY '||v_passwrd||' ;
GRANT viewA viewB viewC TO '||v_userid;
-
This is true - actually, we're switching over to execute immediate for new coding, but I thought the execute sql statement would be clearer because of the comments I snipped out of one of our scripts.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|