[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select - Dynamic Schema

Posted on 2004-08-10
5
Medium Priority
?
637 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
4 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 35

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month19 days, 14 hours left to enroll

872 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