CODEWITHSUNDEEP
sqloracle-database
Negashion
Negashion

Reputation: 139

Storing a select into a variable for future use in procedure

Im coding a procedure where i have to do an update with a "where field IN (SET)" condition. I am trying to retrieve this "SET" from a table with the following query :

   SELECT WM_CONCAT(fieldX) FROM TableX WHERE .... ;

I would like to save this select into a variable and use it after for the "where field IN (SET)" condition. Something like :

   UPDATE TABLEY SET ... WHERE fieldX IN (Variable);

Right now im get this error when i try to do a select with a where condition like the one i want for the update :

  ORA-00932: inconsistent datatypes: expected - got CLOB
  00932. 00000 -  "inconsistent datatypes: expected %s got %s"

Thx in advance.

Upvotes: 0

Views: 218

Answers (2)

Dba
Dba

Reputation: 6639

Try with xmltable,

UPDATE TABLEY y
SET ... 
WHERE y.fieldX IN(SELECT (COLUMN_VALUE).getNumberVal() 
                  FROM xmltable(Variable));

Upvotes: 1

collapsar
collapsar

Reputation: 17238

try

UPDATE TABLEY y
   SET ... 
 WHERE y.fieldX IN (
          SELECT x.fieldX FROM TableX x WHERE .... 
       )
     ;

Upvotes: 0

Related Questions