Tuesday, January 3, 2012

Converting list of values to result of a query


When there is list of values that have to be converted to a result of a query, here is the way.

SQL> select column_value from table(dbmsoutput_linesarray('AAA','BBB'));

COLUMN_VALUE
-------------------
AAA
BBB

Now the above query can be used as a subquery as if the values 'AAA', 'BBB' are result of a query (though we have given them like a list).

Note: 
 dbmsoutput_linesarray is a varray(2147483647) of varchar2(32767) that comes with database by default in SYS schema. And it can be used by any database user.