Return a fully dynamic table from an Oracle function

oracleoracle-11g-r2plsql

I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of type varchar.

As far as I have seen, I have to always to create an object/record and a table type of it. This means that my idea won't work? The underlying goal is to pass a system command output back to a callee as a table.

Edit: more on the task. I want to issue an OS command, consume the output and return it back as a table. The output from the OS command is going to be CSV-formatted data. At the time of execution I do not know the amount of rows to be returned but only the amount of columns which is passed as the second arg. I was thinking about using Java with a dynamic STRUCT and an ARRAY containing them. Though I would prefer the former approach.

It should look like this:

create function(clob query, list of varchars cols) returns table
begin
  execute system command(query, cols);
  examine sysout from command;
  return tabular data from syscmd as table;
end

Best Answer

It is possible, though quite complicated, to write a pipelined table function that returns a variable structure. Your pipeline table function could take the two arguments and use the Oracle Data Cartridge interface and the magic of the AnyDataSet type to return a dynamic structure at runtime. You can then use that in subsequent SQL statements as if it was a table, i.e.

SELECT *
  FROM TABLE( your_pipelined_function( p_1, p_2 ));

A couple more references that discuss the same sample implementation

Related Question