Re: Passing PL/SQL values to REF

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 15 Jul 1998 00:43:52 GMT
Message-ID: <35acfb14.9339870@192.86.155.100>

A copy of this was sent to klausk_at_my-dejanews.com (if that email address didn't require changing) On Tue, 14 Jul 1998 18:08:55 GMT, you wrote:

>Hi all,
>
>due to the implementation of Oracle RDBMS passing records/tables over JDBC
>Thin Driver to an Java applet, I'm looking for a nice trick, how I can pass
>the values of a (multicolumn) PL/SQL table to a REF Cursor (who can be
>accessed via JDBC).
>
>I do not want to create physical tables at runtime,, to get the performance
>up.
>
>Any ideas ?
>
>cu
>
>Klaus
>
>BTW: Although the JDBC docs say, that you can't pass records from a function
>to JDBC , it DOES work !
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

Actually... there is a way to use SQL on PL/SQL tables (sort of). In order to do this, you need to create a table as such (we only ever need one of these tables per database, consider it similar to DUAL)

create table dummy ( x int primary );
insert into dummy select rownum from all_objects;

so dummy is a table with rows (approx) 1 .. 1,000 or so. You can add more if you want. The important thing is that is has records 1, 2, 3, 4, .... N (all integers, no gaps). the max number of records you can get back from pl/sql tables will be mandated by the number of rows in this table.

Once you have that, you need to make your pl/sql table callable from SQL. We need a function "getcol" for example that if you say "getcol(5)" it would return the 5'th element from your pl/sql table. We also need a function, say getmax, that tells us how many elements are in the table currently. So a package spec might look like:

create or replace package demo
as

pragma restrict_references(demo, wnds, rnds, wnps, rnps);

type array is table of varchar2(255) index by binary_integer;

procedure set_up_some_data;

function getcol( x in number ) return varchar2; pragma restrict_references(getcol,wnds,rnds,wnps);

function getmax return number;
pragma restrict_references(getmax,wnds,rnds,wnps); end;
/

the procedure set_up_some_data is just an example, your own code would go there.

Now, we can create a view like this:

create or replace view
demo_view
as
select demo.getcol(x) theColumn
from dummy
where x <= ( select demo.getmax from dual ) /

So, this view will select our function on column X for all X's <= the number of elements in our table. that is, we will get rows 1, 2, 3 ... N-Elements in the Table.

Then we implement our package body as such:

create or replace package body demo
as

g_theArray array;
g_theCnt number;

function getcol( x in number ) return varchar2 is
begin

return g_theArray(x);
end;

function getmax return number
is
begin

return g_theCnt;
end;

procedure set_up_some_data
as
begin

for x in ( select username, rownum rnum from all_users where rownum < 11) loop

        g_theArray(x.rnum) := x.username;
        g_theCnt := x.rnum;

end loop;
end;

end demo;
/

Now we can:

SQL> select * from demo_view;

THECOLUMN

SYS
SYSTEM
DBSNMP
TRACESVR
SCOTT

WEB$RPPRASAD
WEB$GBRADSHA
WEB$JBROTHER
WEB$KKISER
WEB$GDEYOUNG

10 rows selected.

SQL> select * from demo_view order by thecolumn;

THECOLUMN

DBSNMP
SCOTT
SYS
SYSTEM
TRACESVR

WEB$GBRADSHA
WEB$GDEYOUNG
WEB$JBROTHER
WEB$KKISER
WEB$RPPRASAD

10 rows selected.

Yes, you can even apply a where clause to it, join it, use it in an IN statement (eg:

SQL> select * from all_users where username in ( select * from demo_view );

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
DBSNMP                                 17 01-SEP-97
SCOTT                                  20 01-SEP-97
SYS                                     0 01-SEP-97
SYSTEM                                  5 01-SEP-97
TRACESVR                               19 01-SEP-97
WEB$GBRADSHA                         1792 01-SEP-97
WEB$GDEYOUNG                         1794 01-SEP-97
WEB$JBROTHER                         1813 01-SEP-97
WEB$KKISER                           1793 01-SEP-97
WEB$RPPRASAD                         1791 01-SEP-97

and so on...

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jul 14 1998 - 19:43:52 CDT

Re: Passing PL/SQL values to REF_Cusors