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>...
> 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.

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:246014735810

).

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. Received on Tue Jun 17 2003 - 15:46:06 CDT