In response to a question in comp.databases.oracle.server
(which I would post to if my ISP's news server wasn't busted)
you responded (in part)
Another way is to use a result set and "select * from
plsql_function". It could look like this:
ops$tkyte@8i> create or replace type myTableType as table of
varchar2 (64);
2 /
Type created.
ops$tkyte@8i>
ops$tkyte@8i>
ops$tkyte@8i> create or replace
2 function demo_proc2( p_rows_to_make_up in number )
3 return myTableType
4 as
5 l_data myTableType := myTableType();
6 begin
7 for i in 1 .. p_rows_to_make_up
8 loop
9 l_data.extend;
10 l_data(i) := 'Made up row ' || i;
11 end loop;
12 return l_data;
13 end;
14 /
Function created.
ops$tkyte@8i>
ops$tkyte@8i> select *
2 from the ( select cast( demo_proc2(5) as mytableType )
3 from dual );
COLUMN_VALUE
----------------------------------------------------------------
Made up row 1
Made up row 2
Made up row 3
Made up row 4 [Image]
Made up row 5
So, your JDBC program would just run the query to get the data.
If the function "demo_proc2" cannot be called from SQL for
whatever reason (eg: it calls an impure function in another piece
of code or it itself tries to modify the database via an insert
or whatever), you'll just make a package like:
ops$tkyte@8i> create or replace package my_pkg
2 as
3
4 procedure Make_up_the_data( p_rows_to_make_up in
number ); 5 function Get_The_Data return myTableType;
6 end;
7 /
Package created.
ops$tkyte@8i>
ops$tkyte@8i> create or replace package body my_pkg
2 as
3
4 g_data myTableType;
5
6 procedure Make_up_the_data( p_rows_to_make_up in number )
7 as
8 begin
9 g_data := myTableType();
10 for i in 1 .. p_rows_to_make_up
11 loop
12 g_data.extend;
13 g_data(i) := 'Made up row ' || i;
14 end loop;
15 end;
16
17
18 function get_the_data return myTableType
19 is
20 begin
21 return g_data;
22 end;
23
24 end;
25 /
Package body created.
ops$tkyte@8i>
ops$tkyte@8i> exec my_pkg.make_up_the_data( 3 );
PL/SQL procedure successfully completed.
ops$tkyte@8i>
ops$tkyte@8i> select *
2 from the ( select cast( my_pkg.get_the_data as mytableType
) 3 from dual );
COLUMN_VALUE
----------------------------------------------------------------
Made up row 1
Made up row 2
Made up row 3
And you'll call the procedure followed by a query to get the
data...
I have tried this, and it all works beautifully. I would
like to make this a standard of sorts. _However_, I would
also want the choice to write the stored function in
java. When I try that, the call from SQL*Plus fails
with a variety of errors. I think I am either using
the wrong wrapper for the function, or defining the
wrong return type in the java class. So, my question
is, how would the above look if the stored function was
written in java?
Here is a quick and dirty example for you. A java function to return a list of files in a directory as an array suitable for SELECT'ing. Notice I use an easier (8.1) syntax for casting the table as well. Note that the owner of this java procedure must have javasyspriv granted to them and
that is extremely powerful so be careful with that one. It is needed to read the file system as we are:
ops$tkyte@DEV816> create or replace type fileList as table of
varchar2(255)
2 /
Type created.
ops$tkyte@DEV816> create or replace
2 and compile java source named "DirList"
3 as
4 import java.io.*;
5 import java.sql.*;
6 import oracle.sql.*;
7 import oracle.jdbc.driver.*;
8
9 public class DirList
10 {
11 public static ARRAY getList(String directory)
12 throws SQLException
13 {
14 File path = new File( directory );
15
16 Connection conn =
17 new OracleDriver().defaultConnection();
18 ArrayDescriptor descriptor =
19 ArrayDescriptor.createDescriptor( "FILELIST", conn );
20 return new ARRAY( descriptor, conn, path.list() );
21 }
22
23 }
24 /
Java created.
ops$tkyte@DEV816> create or replace
2 function get_dir_list( p_directory in varchar2 )
3 return fileList
4 as language java
5 name 'DirList.getList( java.lang.String )
6 return oracle.sql.ARRAY.ARRAY';
7 /
Function created.
ops$tkyte@DEV816> select *
2 from TABLE ( cast (get_dir_list( '/tmp' ) as fileList) );
COLUMN_VALUE
----------------------------------
lost+found
.rpc_door
ps_data
.pcmcia
.X11-unix
.X11-pipe
page.html
owinst.log
wwvcb.log
xxx.dat
mpAwaWAs
mp3saOFs
wwvcbus.log
sbddl.log
help.log
helpctxs.log
helprela.log
ups_data
helpindx.log
wwvsbus.ctl
tkyte.dbg
1.txt
exec29047
mpljaa3p
2.txt
3.txt
mpwUaGCo
4.txt
5.txt
q_invalid.sql
xxtmpxx.sql
mm.sql
xxx
xxx.sql
mpUQaq6l
test_tbs.dbf
data.dat
afiedt.buf
a.sql
mpYxaizE
mpNcaGOW
mpzqayTW
mp1ra4wY
mp77aqXl
.removable
45 rows selected.