ORACLE SQL: Use a SELECT statement to pass a value to a function parameter

Question

I have a function in Oracle that will convert a comma-delimited string into a table of values. It is usually called like this:

SELECT DISTINCT COLUMN_VALUE FROM TABLE(Comma_String_To_Table('abc,123,def,456'));

This call would return a table with four rows:

COLUMN_VALUE
abc
123
def
456

I use this many times to accept multi-value parameters from a front end web page, convert the parameter to a table, then join the table to filter my results, something like this:

SELECT *
FROM MYTABLE m
INNER JOIN (
    SELECT DISTINCT COLUMN_VALUE FROM TABLE(Comma_String_To_Table('abc,123'))
) f ON M.STATUS = F.COLUMN_VALUE

Now, to the question... I am writing a piece of standalone SQL. I want to use this Comma_String_To_Table function to filter my results just like in the example above, except that I want the value passed to the function to be the result of a SELECT statement. i.e:

SELECT *
FROM MYTABEL m
INNER JOIN (
    SELECT DISTINCT COLUMN_VALUE FROM TABLE(Comma_String_To_Table(SELECT VALUE FROM APPSETTING WHERE APPSETTINGID = 76))
) f ON M.STATUS = F.COLUMN_VALUE

The SQL SELECT VALUE FROM APPSETTING WHERE APPSETTINGID = 76 results in a single comma-delimited string value.

The error I am getting is:

ORA-00936: missing expression

Any help to get this to work would be much appreciated.

AppSetting contains the expression I wish to filter with as a comma-delimited string. If I were writing out the SQL without using the AppSetting value, it would be something like: — laughsloudly
– laughsloudly, Commented Mar 19, 2015 at 16:17

Tony Andrews · Accepted Answer · 2015-03-19 16:16:33Z

4

Try this:

SELECT *
FROM MYTABEL m
INNER JOIN (
    SELECT DISTINCT COLUMN_VALUE 
    FROM  TABLE (SELECT Comma_String_To_Table(VALUE)
                 FROM APPSETTING WHERE APPSETTINGID = 76)
) f ON M.STATUS = F.COLUMN_VALUE

answered Mar 19, 2015 at 16:16

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nicola Feb 5 at 14:38

simple and brilliant

Collectives™ on Stack Overflow

ORACLE SQL: Use a SELECT statement to pass a value to a function parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related