asked on

Select - Dynamic Schema

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.

catchmeifuwant

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.

johnsone

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;

root_start

ASKER

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

ASKER CERTIFIED SOLUTION

modulo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial