Help with Oracle Functions for a SQL Server dummy.

[kaymc]

Hi, I'm fairly new to Oracle, although I've used SQL Server for years.

I'm currently producing a horrible little Crystal report where I'll need to pass parameters to a sql function and have it return the data I need -- Not just a value or a row but 10 or 12 rows.

Now with SQL server it was pretty easy to write a function, without having to come to grips with any nasty cursors, and have it return a table: Just pop in your query, add a header for the parameters and get it to return the results of your query. Not much more than a fancy view.

If only the same were true of Oracle.

By playing with a few examples I've worked out how to write simple cursors which return rowtypes, but that's where I've hit a brick wall.

So my question is this, how can I write a function to pass a few parameters and which will then return multiple rows, without having to become a PL/SQL cursor wizard overnight? Is there an easy and obvious solution that I am missing?

 

[sem]

I'm not familiar with Crystal, but your procedure may be like

create function getTableData(pTable in varchar2)
return sys_refcursor
is
result sys_refcursor;
begin
  open result for 'select * from '||pTable;
  return result;
end;

Remember that this function is quite dangerous as it alows anybody granted to execute it to query any of your tables.


Regards, Dima

http://seminihin.narod.ru

 

[sem]

One more note: in pre-9i sys_refcursor type is not available so you should define your own ref cursor type in some package specification.

Regards, Dima

http://seminihin.narod.ru

 

[kaymc]

Thanks so much Dima!

You got me looking in the right direction and after a bit of fiddling I got my refcursor working with crystal.

You, sir, are a lifesaver.