Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Obtaining a ref cursor from a table object

Re: Obtaining a ref cursor from a table object

From: Teppicamon <quicog_at_hotmail.com>
Date: Wed, 18 Jun 2003 13:41:17 +0200
Message-ID: <bcpj55$l2p9o$1@ID-162849.news.dfncis.de>

"bung ho" <bung_ho_at_hotmail.com> wrote in message news:567a1b1.0306171315.48d7940_at_posting.google.com... > "Noons" <wizofoz2k_at_yahoo.com.au.nospam> wrote in message news:<3eef3dbf$0$24424$afc38c87_at_news.optusnet.com.au>...
> > "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.
>
> unless i'm mistaken, i think that the OP knows how to return ref
> cursors--what he's essentially asking here is, can you return the
> contents of a PL/SQL table (namely, one of type CTX_THES.EXP_TAB) as a
> ref cursor.  and afaik, the answer is, not really.

Yep, that's actually the point... Anyway, thank you Nuno for your help...

--
Francesc X. Gil
SPOC, SL
Received on Wed Jun 18 2003 - 06:41:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US