DBMS_SQL and variables

From: Programming <sysdev_at_mb.sympatico.ca>
Date: 1997/06/16
Message-ID: <33A55FC3.6EC5@mb.sympatico.ca>#1/1

DBMS_SQL I am using DBMS_SQL to create tables from within procedures. I have CREATE ANY TABLE rights.
If I don't specify a username, the table is created as my own (myself.tablename), in my own schema.

I want more than one person to use this procedure, and I want to ensure that the table is always created in the user's own schema.

How can I get the username into a variable, and prefix the tablename with the username:

ie.
...
Create table username.tablename as
(select ...)
...

???

I will include what I have tried so far. It is really frustrating trying deal with variables in PL/SQL and SQLPLUS!!!

Rodger Lepinsky
ADP Systems Partnership
Winnipeg, Manitoba, Canada

Works, but, no username specified:

create or replace procedure testing2 as

cursor_name 		integer;
rows_processed 	integer;
agt_count		integer;
user_name 		varchar(15);
BEGIN
             select user into user_name from dual; 
   	cursor_name := dbms_sql.open_cursor;
	dbms_output.put_line('Cursor name is:  ' || cursor_name);
	dbms_sql.parse(cursor_name, 
	' create table air8 as


	(select * from air) ', 

	dbms_sql.v7);
	rows_processed := dbms_sql.execute(cursor_name);
	dbms_output.put_line('Rows processed is:  ' || rows_processed);
	dbms_sql.close_cursor(cursor_name);	

END; Procedure created. Elapsed: 00:00:00.67

begin
testing2;
end;

Cursor name is: 1
Rows processed is: 0

PL/SQL procedure successfully completed. Elapsed: 00:00:01.13

This code gives the table: RLEPINSKYAIR8 (I wanted RLEPINSKY.AIR8 )

	dbms_sql.parse(cursor_name, 
	' create table %%user_name.air8 as


	(select * from air) ', 

	dbms_sql.v7);

When creating the procedure,
this code prompts the user to enter a username:

	dbms_sql.parse(cursor_name, 
	' create table &user_name.air8 as


	(select * from air) ', 

	dbms_sql.v7);

Enter value for user_name: rlepinsky
Procedure created.		Elapsed: 00:00:00.24

The following cause ERRORS:

This will create the procedure, but causes errors when running the procedure:

	' create table user.air8 as


	(select * from air) ', 

	dbms_sql.v7);

ERROR at line 1:

ORA-00903: invalid table name
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 25
ORA-06512: at "RLEPINSKY.TESTING2", line 8
ORA-06512: at line 2					Elapsed: 00:00:00.02


This causes errors: 
	' create table user_name.air8 as


	(select * from air) ', 

	dbms_sql.v7);

Cursor name is: 4
User name is: RLEPINSKY
begin
 *
ERROR at line 1:

ORA-01918: user 'USER_NAME' does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 25
ORA-06512: at "RLEPINSKY.TESTING2", line 11
ORA-06512: at line 2					Elapsed: 00:00:00.05

This causes errors:

	' create table %user_name.air8 as


	(select * from air) ', 

	dbms_sql.v7);

Cursor name is: 5
User name is: RLEPINSKY
begin
 *
ERROR at line 1:

ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 25
ORA-06512: at "RLEPINSKY.TESTING2", line 11
ORA-06512: at line 2

Any ideas???

Rodger Lepinsky
ADP Systems Partnership
Winnipeg, Manitoba, Canada Received on Mon Jun 16 1997 - 00:00:00 CDT