Bind variables in SELECT statement

Question

I have tables in my Oracle database

PROJECTINFO
NAME       SCHEMA
--------------------------
Test       W_TEST_000

SAMPLESET 
NAME          SS_ID
--------------------------
Test_SSet     1049

in my SQL*Plus script a have a substitution variable (set directly or from user input)

DEFINE Project_Name = 'Test'
DEFINE SampleSet_Name = 'Test_SSet'

Now I need another two bind variables

VAR Project_Schema varchar2(50)
VAR SampleSet_ID number

Then I need to place a result of a SELECT statement into two bind vars

SELECT SCHEMA INTO :Project_Schema FROM PROJECTINFO WHERE NAME = '&Project_Name';
SELECT SS_ID INTO :SampleSet_ID from SAMPLESET WHERE NAME = '&SampleSet_Name';

Now I need to use both :Project_Schema and :SampleSet_ID in SELECT statement from W_TEST_000.MY_TABLE subtable like this:

SELECT NAME FROM :Project_Schema.MY_TABLE WHERE SS_ID = :SampleSet_ID

But this does not work.. (ORA-00903: invalid table name)

How to use bind variables in future SQL requests within the same SQL*Plus script?

You can use Dynamic SQL for this. Try EXECUTE STATEMENT

Sameer Mirji
– Sameer Mirji

2016-03-04 02:54:32 +00:00
Commented Mar 4, 2016 at 2:54 — Sameer Mirji
– Sameer Mirji, Commented Mar 4, 2016 at 2:54

Ricardo Arnold · Accepted Answer · 2016-03-04 06:48:32Z

2

it will work if you encapsulate the selects in PL/SQL blocks e.g.

DEFINE Project_Name = 'Test'
DEFINE SampleSet_Name = 'Test_SSet'

VAR Project_Schema varchar2(50)
VAR SampleSet_ID number


begin
  SELECT SCHEMA 
    INTO :Project_Schema 
    FROM PROJECTINFO 
   WHERE NAME = '&Project_Name';
end;
/
begin
  SELECT SS_ID 
    INTO :SampleSet_ID 
    from SAMPLESET 
   WHERE NAME = '&SampleSet_Name';
end;
/        

 --test the contents of the variable
Select :SampleSet_ID, :Project_Schema from dual;

answered Mar 4, 2016 at 6:48

Ricardo Arnold

9331 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Alexander Over a year ago

However, if SAMPLESET is inside table which name is in :Project_Schema variable, how can I access it? I mean W_TEST_000.SAMPLESET, then if I type Select :Project_Schema.SAMPLESET.. it does not work.

Ricardo Arnold Over a year ago

Hi @Alexander, did this answer solve your problem? can you please mark the question as answered and mark the answer as accepted? it will help both reputations by adding us points =)

Ricardo Arnold Over a year ago

can you be more specific with what you want? SAMPLESET is a table in your example, what do you mean with it being inside a table in project_schema?

Alexander Over a year ago

Sorry for this confusion.. Please look at the last part of my top post - now I have to select from table W_TEST_000.MY_TABLE but having W_TEST_000 in the variable Project_Schema. So I need to execute something like this: SELECT NAME FROM :Project_Schema.MY_TABLE WHERE SS_ID = :SampleSet_ID. My question is - how to correctly write this request (part after FROM) ?

Ricardo Arnold Over a year ago

you can either use a ref cursor or just dynamic SQL

EXECUTE IMMEDIATE 'SELECT NAME FROM '||:PROJECT_SCHEMA'||'.MY_TABLE WHERE SS_ID = :SampleSet_ID' INTO <my_name_var> using :SampleSet_ID

|

Collectives™ on Stack Overflow

Bind variables in SELECT statement

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related