Re: Obtaining a ref cursor from a table object

"bung ho" <bung_ho_at_hotmail.com> wrote in message news:567a1b1.0306171246.7b35cdd7_at_posting.google.com...
> "Teppicamon" <quicog_at_hotmail.com> wrote in message
news:<bcn8t4$l1l3u$1_at_ID-162849.news.dfncis.de>...
> > Hi everybody
> >
> > I'm relatively new to PL/SQL, and now I face a problem that I don't know
> > 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.
>
> unfortunately, i don't think this is directly possible, because the
> exp_tab (and exp_rec) type is defined in the ctx_thes package--meaning
> that it is a PL/SQL type rather than a SQL type (someone correct me if
> i'm wrong, because the documentation says these types are defined in
> the CTXSYS schema, but that doesn't appear to be the case). java will
> need the 'plain' SQL type.

Uhm... Thanx, that's more or less what I suspected after many lost hours of trying, but at least wanted some expert to confirm it to me...

>
> having said that, you can always create your own SQL type (e.g.,
> "create or replace type my_xphrase_tab_type as table of
> varchar2(256)"). then write a wrapper stored procedure that takes the
> results from ctx_thes.bt, uses them to populate your own table type,
> and then that can be returned to a java program as a resultset (using
> the "select column_value from table(cast(... as my_xphrase_tab_type))"
> form). as usual, check out asktom for lots of code examples of this
> kind of thing (for example:
>
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:246014735 810
>
> ).

Thank you very much for the advice. Sounds pretty complicated (at least for me ;-) ) but I think I should give it a try even for the sake of learning...

>
> one final thing is: can't you use the alternate ctx_thes.bt call that
> returns the result as a big concatenated string? this might be the
> easiest way to go. just parse it out on the java side. it's kind of
> hack-y but would take less work than the above.

Yes, we could, but it has the problem that then we would lose the xlevel information, and it's really important because if a word has broader terms in more than one branch we can't see the difference... E.g., if we have a branch b-c and other d-e-c, if we do a bt('c',2,'my_thes'), we would get a string like c | b | e | d , and that is completely useless for our purposes, because we can't separate the branches... If I can't manage to succeed with a SQL type of my own I'll have to encode somehow the table in a string with all the info I need...

Again, thanks a lot for your help

--
Francesc X. Gil
SPOC, SL