SQL Query - Out Parameter

I have read all the posts in these forums related to using the SQL Query activity with parameters and I'm still having an issue trying to pass a comma-separated string of ID's into a SQL statement successfully.  The colon-argument-name bind variable works fine when passing a numeric value.  The bind variable inserts into the SQL command statement as a number and is parsed at the database as expected and the "results" of the SQL are returned throught the Result component of the activity.

 

When I use the same syntax to pass in a set of comma-separated numbers, the subsitution of the bind variable seems to be adding a set of single-quotes around the "string" even though it is really a list of numeric values. In any case, the value in the bind variable should not be sending anything but the actual content of the variable.

 

There are a few posts where people, using what appears to be SQL Server  TransAct/SQL are able to use a block of SQL with declared variables to execute a dynamically built SQL string and the "results" are sent back throught activity's Result component.  When I try to perform the same type of SQL block using Oracle PL/SQL, the activity doesn't seem to understand that the EXECUTE IMMEDIATE sql command is returning anything and the "results" are empty.

 

Another post suggested using a stored procedure, but the normal Oracle syntax would use an "OUT" parameter to return the SQL results, usually as a REF CURSOR. There is no way to set an OUT direction for a parameter in the SQL Query activity.  Even though the parameter Direction is displayed, it cannot be set - only IN parameters are allowed.

 

Can someone please explain how I can use an Oracle client to execute a SQL command taking IN a string of comma-separated numbers that the database SQL parser will see as a comma-separated set of numbers - not a big, long string?