Solved

Select - Dynamic Schema

Posted on 2004-08-10
5
632 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

691 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