Solved

Select - Dynamic Schema

Posted on 2004-08-10
5
630 Views
Last Modified: 2010-05-18
Hi All,

I want to execute the following select:
Select * From schema.table;

I have this "schema" or username in a table, but how I can do this select ?

I have this username in a variable called "vUser":
Select userId into vUser from oracle_user;

And if I use:
Select * From vUser.table;

Oracle returns an error.

Do I have how to do this ?

Thanks,
root.
0
Comment
Question by:root_start
5 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 11761856
You need to use a Dynamic sql to execute in such cases..

Here's a sample. I'm connected as User2 and trying to query "dept" table owned by "demo" user.

------------
declare
v_user varchar2(10) := 'demo';
v_count number(3);
v_sql varchar2(100);

begin
v_sql := 'select count(*) from '||v_user||'.dept';  ------------- Dynamic SQL

execute immediate v_sql into v_count; ----------- Execute the SQL and store the results in v_count

dbms_output.put_line(v_count); -------- Print the output

end;
/

---------------

Output is :

4

PL/SQL procedure successfully completed.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 11763343
If you are trying to write a SQL*Plus script, you can do something like this:

create table s (sname varchar2(30));
insert into table s values ('USER');
column sname new_value schema
select sname from s;

create table t (tname varchar2(30));
insert into table s values ('TABLE');
column tname new_value table
select tname from t;

Select * From &&schema..&&table;

0
 
LVL 3

Author Comment

by:root_start
ID: 12025376
I did the following to correct my problem:

In the begging of my procedure I put:

set verify off
Accept oracle_user Prompt 'Oracle User: '

And where I need to use the user I put:
&oracle_user..control_seq_func()

Thanks for all your help.

root
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12591489
PAQed with points refunded (30)

modulo
Community Support Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question