This discussion is archived
1 Reply Latest reply: Nov 20, 2012 7:05 PM by vansul RSS

How to Run a Select Query  stored in a Variable

TrojanSpirit Newbie
Currently Being Moderated
Hello,

I have a following requirement:

Result of one select query on Var1 , result of other select query in Var2 ,
if Va2 = 'value11' OR Var2 = 'Value2' then Var1 = 'select query'. Now how can I run this SQL query at the end of the Pl/SQL?

so I'm writing following query for the same:

DECLARE
qry nvarchar2(500);
result nvarchar2(500);

BEGIN

select 'select TEXTVAL as "CHARG" FROM TABLE1 WHERE LOC =''[ParameterValue]'' and KEYNAME =''<<REPLACE>>''' INTO qry from dual;

SELECT CASE WHEN count(RW."CountofBATCH") > 1 then 'Mixing'
WHEN count(RW."CountofMAT") = 0 then 'None'
ELSE 'Other'
END
INTO result
FROM TABLENAME2 TT, XMLTable('/Rowsets/Rowset/Row' PASSING TT.XMLCOL
COLUMNS
"CountofBATCH" PATH '/Row[CLABS > 0]/CHARG',
"CountofMAT" PATH '/Row[MATNR = "[Parameter Value]"]/MAT'
) AS RW

where
TT.PL = '[Parameter Value]' and
TT.TANK = '[Parametr Value]' ;

IF result = 'Mixing' OR result = 'None' THEN
qry := replace( qry , '<<REPLACE>> ' , result);
else
qry := 'Nothing';
END IF;

This way the variable qry will have select statement. Now How can I run this qry variable to get the output of that select statement in the same query?
  • 1. Re: How to Run a Select Query  stored in a Variable
    vansul Pro
    Currently Being Moderated
    you can use execute immediate if the output of the query is in the single query.

    that is very simple.
    have the query in the signle string and then pass like this

    declare
    qry varchar2(255);
    result varcharf2(2500);
    vempid number :=1;

    begin
    qry:='select empname from emp where empid=:empid';

    execute immediate qry into result using vempid;
    -----now the data result is in result
    end;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points