1 Reply Latest reply: Nov 20, 2012 9:05 PM by vansul RSS

    How to Run a Select Query  stored in a Variable

    TrojanSpirit
      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
          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;