Re: Obtaining a ref cursor from a table object

"Teppicamon" <quicog_at_hotmail.com> wrote in message news:bcn8t4$l1l3u$1_at_ID-162849.news.dfncis.de...
> even how to start solving... We're using interMedia Text on an Oracle 8.1.7
> server and we want to use some procedures in the CTX_THES package to let
> users browse the Thesaurus from a web application. For example, we use the
> bt() function to get the broader terms of a word like this (that's the
> sample in Oracle docs):
>
> declare
> xtab ctx_thes.exp_tab;
> begin
> ctx_thes.bt(xtab, 'wolf', 2, 'my_thes');
> for i in 1..xtab.count loop
> dbms_output.put_line(xtab(i).phrase);
> end loop;
> end;
>
> And OK, that works fine, but we would want to get a ref cursor to that xtab
> table to be able to return it and use it as a ResultSet in a J2EE web
> application. Maybe I just don't know how to open a cursor from a table
> object (if I try a select from that table, Oracle raises an error saying it
> has to be declared (?)), or maybe it isn't possible at all, but if that's
> the case I would just want to know it to avoid wasting more time...
>
> Any help will be greatly appreciated...

Put your function inside a package.
In the package description source, include a line to define an untyped refcursor.
Something like this:

curser TYPE REF CURSOR;

Then in the function code, define a return (or a OUT par) of that type. Like:

my_cursor curser;

Then just:

OPEN my_cursor FOR
'SELECT blah from blah_tab';
RETURN my_cursor;

and that's it.

Go to asktom, there are some detailed examples there. Search for REF CURSOR.

--
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam