Solved

Select - Dynamic Schema

Posted on 2004-08-10
5
629 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

Title # Comments Views Activity
How can I rollback insert statements after commit in oracle? 7 119
Oracle Pivot Question 8 58
Can't Access My Database 57 63
having some issue on pl sql procedure 1 16
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now