Return a fully dynamic table from an Oracle function

Question

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

• Paul White: Comments are not for extended discussion; this conversation has been moved to chat.

Answer 1

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

• Dynamic SQL Pivoting
• The Implementing the Interface Approach section of the Oracle Data Cartridge Developer's Guide

Answer 2

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.

• Michael-O: 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.

Answer 3

you can create a tmp view, and replace the view dynamically.

create or replace view tmp_view as select 1 x from dual;
/
create or replace package pkg_input_sql is
 cursor my_cursor is select * from tmp_view;
 my_rec_type my_cursor%rowtype;
 type my_tab_type is table of my_cursor%rowtype;
 function get_cursor(p_sqlstr varchar2) return sys_refcursor;
 function get_table return my_tab_type
 pipelined;
end pkg_input_sql;
/
create or replace package body pkg_input_sql is
 function get_cursor(p_sqlstr varchar2) return sys_refcursor as
 my_cursor sys_refcursor;
 begin
 open my_cursor for p_sqlstr;
 return my_cursor;
 end get_cursor;
 function get_table return my_tab_type
 pipelined is
 my_tab my_tab_type;
 i pls_integer;
 my_cursor sys_refcursor;
 begin
 my_cursor := get_cursor('select * from tmp_view');
 fetch my_cursor bulk collect
 into my_tab;
 for i in 1 .. my_tab.count loop
 pipe row(my_tab(i));
 end loop;
 end;
begin
 null;
end pkg_input_sql;
/
create or replace procedure create_tmp_view(p_sqlstr varchar2) is
begin
 execute immediate 'create or replace view tmp_view as ' || p_sqlstr;
 dbms_utility.exec_ddl_statement('alter package pkg_get_sql compile package');
 dbms_session.reset_package; -- to avoid ora-04068
end create_tmp_view;

execution results:

enter image description here

Answer 4

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
begin 
execute system command(query, cols); 
#hopefully we know the output filename
create a new external table mapping the output;
end

• Ohadi: It will work! The definition of the file is known and we can dinamically create an external table.