PL/SQL Scripting Error.

[pjindc]

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;
/

[stecal]

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;

[pjindc]

Thank you, I'll try this out

[Highlander]

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;

[stecal]

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.