Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
  execute system command(query, cols);
  examine sysout from command;
  return tabular data from syscmd as table;
share|improve this question
Comments are not for extended discussion; this conversation has been moved to chat. – Paul White Apr 27 at 12:35

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.

  FROM TABLE( your_pipelined_function( p_1, p_2 ));

A couple more references that discuss the same sample implementation

share|improve this answer

I think your best approach is to abandon the attempt to send back a dynamic table (although I suppose you might be able to create temporary table and pass back a refcursor to it, but I am not sure here).

My preferred approach here would be to generate results in a more flexible format, something like an XML document or the like and return it. This gives you the flexibility that you need without having to have the columns determined after function scan.

share|improve this answer
Hi Chris, thanks for the answer. I have already abandoned the dynamic table because it is simply not possible. XML is too verbose but a good idea. My main problem is that I would need to call a static Java function to query a lucene index. This would mean that I need to open and close the index every time. This is very unefficient. I have resorted to a REST service with JSON and XML output. – Michael-O Feb 28 '13 at 9:29

One solution would be to create an extrnal table based on the Lucene output. You can easily alter the external table definition (and to point it to multiple files).

So you will have:

function l_query(clob query, list of varchars cols) returns table_name
execute system command(query, cols); 
#hopefully we know the output filename
create a new external table mapping the output;
share|improve this answer
It will work! The definition of the file is known and we can dinamically create an external table. – Ohadi Jun 6 '12 at 11:58

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.