create command in stored procedure

[mixedbag]

Is it possable to use the create command in a stored procedure as when I try this I get an error

Create or Replace Procedure Add_User
(p_name all_users.username%TYPE) AS
BEGIN
SELECT * from ALL_USERS;
CREATE TABLESPACE james
DATAFILE '/home/oracle/joy/oradata/joy/james01.dbf' size 10M
DEFAULT STORAGE ( INITIAL 50K NEXT 50K
MINEXTENTS 1
MAXEXTENTS 100
PCTINCREASE 20);

CREATE USER james IDENTIFIED BY passwd
DEFAULT TABLESPACE james;

grant connect to james;
grant resource to james;
Commit;
END Add_User;

the following error is recived:
Errors for PROCEDURE ADD_USER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/2 PLS-00103: Encountered the symbol "CREATE" when expecting one of
the following:
begin declare end exception exit for goto if loop mod null
pragma raise return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>

Of course I want to replace usernames with the contents of p_name

 

[LokiDba]

No you cannot you have to use dynamic sql and the account which the procedure is in needs to have the Create User, resource, connect priv's with the ability to grant them to other users. What you have to do is build the Create statement as a string and then pass it to the relevant functions in the DBMS_SQL package. This may be different in 8 and 8i has I think that they have dynamic SQL as part of PL/SQL.

The procedures you need to call are as follows:

DBMS_SQL.open_cursor;
DBMS_SQL.parse(....);
DBMS_SQL.execute(...);
DBMS_SQL.close_cursor(...);

This will execute a DDL statement (ie Create, grant ).

Hope this is of some use

LokiDBA

 

[LokiDba]

Also I usually produce a package that access as a wrapper into any of the build in packages so that if oracle change it you only need to perform a change in one location.

LokiDBA.