Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
share|improve this question
    
So from within your SQL function you want to call an operating system command that will query the database? That does not make sense. You can query the database right from within your SQL function. –  a_horse_with_no_name Mar 24 '12 at 12:49
    
No no, it won't query a database. I will query a Lucene index and return the matching records. I want to make that result available in an transparent way to other DB users. –  Michael-O Mar 24 '12 at 12:58
    
I see, then indeed a ref cursor will not help. Do you really need a "table", or can it be rows of tab/comma separated values (i.e. a table with a single varchar or clob column) –  a_horse_with_no_name Mar 24 '12 at 13:02
1  
I don't think this is possible in Oracle. –  a_horse_with_no_name Mar 24 '12 at 13:09
1  
Oooh, Lucene supports ODBC. You can do it with Oracle Gateways! –  FreshPhilOfSO Mar 26 '12 at 20:52
show 17 more comments

3 Answers

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
share|improve this answer
    
Won't work, as he wants to return the data as a table. You can't do this if the number of columns is dynamic. Read the comments above. –  FreshPhilOfSO May 16 '12 at 14:13
    
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
add comment

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
    
In PostgreSQL I would use JSON ;-) –  Chris Travers Feb 28 '13 at 9:49
    
Me too but I am on Oracle :-( –  Michael-O Feb 28 '13 at 10:17
    
would this help? sourceforge.net/projects/pljson –  Chris Travers Feb 28 '13 at 10:18
    
I have to check but it has virtually no documentation thus making it unusable. –  Michael-O Feb 28 '13 at 10:52
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

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.