CODEWITHSUNDEEP
oracle-databasesqlplus
Alexander
Alexander

Reputation: 73

Bind variables in SELECT statement

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?

Upvotes: 2

Views: 9979

Answers (1)

Ricardo Arnold
Ricardo Arnold

Reputation: 913

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;

Upvotes: 2

Related Questions