Pass/Send a DataTable as part of a SQL Statement (Not a Procedure)

Question

Question: I would like to pass the contents of a DataTable as the from parameter in an Oracle SQL Statement. Is this possible?

The Scenario

1. Create the DataTable and Fill.

2. Pass Contents as the from Table in the SQL query.

   var rankDataTable = new System.Data.DataTable(); // Assume Table is populated and field member of RN exists.

   cmd.Execute ("SELECT Y.* FROM :rankDataTable Y WHERE Y.RN = 1", rankDataTable ) // Execute Query

3. Expected : Contents of rankDataTable are used in Oracle as part of a new Query

Thanks

Answer 1

Though your question looks like you want nested table or varray, but I would suggest to use more flexible approach with JSON or XML: you can pass your data as JSON or XML and use it with JSON_TABLE() or XMLTABLE(). In this case you wouldn't need to create own types.

For example: DBFiddle

SQL> select * 
     from json_table(
         '[{ID:1,X:10,Y:"abc"},{ID:2,X:20,Y:"def"}]'
         ,'$[*]' 
         columns ID int, X int, Y);

  ID          X Y
---- ---------- --------------------
   1         10 abc
   2         20 def

or xmltable(): DBFiddle 2

select * 
from xmltable(
        '/ROWSET/ROW' 
        passing xmltype(
        '<ROWSET>
            <ROW>
                <ID>1</ID><X>10</X><Y>ABC</Y>
            </ROW>
            <ROW>
                <ID>2</ID><X>20</X><Y>DEF</Y>
            </ROW>
        </ROWSET>'
        ) 
        columns ID int,X int,Y);

  ID          X Y
---- ---------- --------------------
   1         10 ABC
   2         20 DEF