Skip to Main Content
  • Questions
  • Returning Arrays From JAVA Stored Function

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Phillip.

Asked: September 18, 2000 - 6:57 pm UTC

Last updated: September 27, 2019 - 3:50 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

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?

and Tom said...

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. 

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Permission Problem

Vinnie, October 26, 2004 - 5:19 pm UTC

Tom,

I am getting a ORA-04043 error when trying to use a TYPE.

CREATE TYPE stringtable AS TABLE OF VARCHAR2(256);
/

Grant execute on strintable to test;

when I try to run the JAVA app logged in as test I get the 04043 error when:
ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor( "STRINGTABLE", conn );

AM I missing something here?



Tom Kyte
October 26, 2004 - 6:24 pm UTC

are you logged into the same schema that created it? if not, you might want to add the schema name in there.

Permissions?

Vinnie, October 27, 2004 - 8:47 am UTC

I do a:

Alter session set current_schema =
after I get the connection and still get the error.
I have also append the schema_name in front of STRINGTABLE & still did not work.

Is there something special with TYPES & permissions I missed?


Tom Kyte
October 27, 2004 - 9:15 am UTC

need full, tiny, yet complete, example to work with.

like this:

ops$tkyte@ORA9IR2> drop user a cascade;
 
User dropped.
 
ops$tkyte@ORA9IR2> drop user b cascade;
 
User dropped.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create user a identified by a;
 
User created.
 
ops$tkyte@ORA9IR2> create user b identified by b;
 
User created.
 
ops$tkyte@ORA9IR2> grant create session, create type, create procedure to a;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2> grant create session to b;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2> exec dbms_java.grant_permission( 'A', 'SYS:java.io.FilePermission', '/tmp', 'read')
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect a/a
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2>
a@ORA9IR2> create or replace type fileList as table of
  2                         varchar2(255)
  3  /
 
Type created.
 
a@ORA9IR2>
a@ORA9IR2> 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( "A.FILELIST", conn );
 20      return new ARRAY( descriptor, conn, path.list() );
 21  }
 22
 23  }
 24  /
 
Java created.
 
a@ORA9IR2>
a@ORA9IR2>
a@ORA9IR2> 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.
 
a@ORA9IR2>
a@ORA9IR2> grant execute on get_dir_list to b;
 
Grant succeeded.
 
a@ORA9IR2> grant execute on fileList to b;
 
Grant succeeded.
 
a@ORA9IR2>
a@ORA9IR2> @connect b/b
a@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2>
b@ORA9IR2> select *
  2    from TABLE ( cast (a.get_dir_list( '/tmp' ) as a.fileList) );
 
COLUMN_VALUE
-------------------------------------------------------------------------------
.wine-tkyte
ssh-ijO10225
...... 

Java does not like my oracle.sql.ARRAY initialization

reader, July 29, 2005 - 10:02 am UTC

I have the following code that gives me one error
drop java source "ZipUtils";
create or replace and compile java source named "ZipUtils"
as
import java.io.*;
import java.util.zip.*;
import java.util.Iterator;
import oracle.sql.*;
import java.sql.*;
import oracle.jdbc.*;

public class ZipUtils {
public static int createZip (String directory)
                   throws IOException {
     File path = new File( directory );
     // These are the files to include in the ZIP file
     String[] fileList = path.list();

    // Create a buffer for reading the files
    byte[] buf = new byte[1024];
    
    try {
        // Create the ZIP file
        String outFilename = "sams9dbExport.zip";
        ZipOutputStream out = new ZipOutputStream( new FileOutputStream( outFilename ) );
    
        // Compress the files
        for ( int i=0; i<fileList.length; i++ ) {
            FileInputStream in = new FileInputStream( fileList[i] );
    
            // Add ZIP entry to output stream.
            out.putNextEntry( new ZipEntry( fileList[i] ) );
    
            // Transfer bytes from the file to the ZIP file
            int len;
            while ( ( len = in.read( buf ) ) > 0 ) {
                out.write( buf, 0, len );
            }
    
            // Complete the entry
            out.closeEntry();
            in.close();
        }
    
        // Complete the ZIP file
        out.close();
    } catch (IOException e) {
        return 0;
    }
    return 1;
}
public static oracle.sql.ARRAY[] getZipContents( String pZipFile ) throws IOException {
     int i = 0;
     String[] zipEntryNames = new String[1000];
     ArrayDescriptor descriptor;
     try {
    oracle.sql.ARRAY[] oraArray;
        // Open the ZIP file
        ZipFile zf = new ZipFile( pZipFile );
    
        // Enumerate each entry
        for ( Iterator entries = ( Iterator ) zf.entries(); entries.hasNext(); ) {
            // Get the entry name
             zipEntryNames[i] = ( (ZipEntry)entries.next() ).getName();
         i = i + 1;
        }
        Connection conn = new OracleDriver().defaultConnection();
        descriptor = ArrayDescriptor.createDescriptor( "SAMS.VARCHARARRAY", conn );
        oraArray[0] = new ARRAY( descriptor, conn, zipEntryNames );

    } catch (IOException e) {
        e.printStackTrace();
    }
    catch (SQLException e) {
        e.printStackTrace();
    }
    return oraArray;
}
}
/
show errors java source "ZipUtils"

This is my error
SQL> @createZip.java

Java dropped.


Warning: Java created with compilation errors.

Errors for JAVA SOURCE ZipUtils:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      ZipUtils:65: Variable oraArray may not have been initialized.
0/0      ZipUtils:73: Undefined variable: oraArray
0/0      Info: 2 errors

Function created.

SQL>

Any ideas? Do I have to implement a procedure with an OUT parameter to pass ARRAY's or can I use a PL/SQL function wrapper with a java function call? 

Tom Kyte
July 29, 2005 - 12:33 pm UTC

seems that oraArray is out of scope there isn't it. you return it OUTSIDE of the try block


CAST Error - Collection inside package

A reader, September 23, 2005 - 2:04 pm UTC

Hi Tom,
I tried to modify the code above and i want to centralise and store all my user defined collections in a package as shown in the code below. I got an error "ORA-00902: invalid datatype" when I run the select statement. Also if I tried to add an object type(commented line in myconstants package)I got an error "PLS-00540: object not supported in this context". We're using using oracle 9i.

create or replace package myconstants as
type myTableType is table of varchar2 (64);
-- type myScalarType as object ( x int, y int );
end myconstants;
/

create or replace function demo_proc2( p_rows_to_make_up in number ) return myconstants.myTableType
as
l_data myconstants.myTableType := myconstants.myTableType();
begin
for i in 1 .. p_rows_to_make_up
loop
l_data.extend;
l_data(i) := 'Made up row ' || i;
end loop;
return l_data;
end;
/
select * from the ( select cast( demo_proc2(5) as myconstants.mytableType )from dual );


Tom Kyte
September 23, 2005 - 8:42 pm UTC

you'll have to use SQL types or local package types (which will create two mangled types in the dictionary anyway) to do that.

Java Procedure does not accept Oracle Directory object

BravoFox, September 20, 2007 - 10:45 am UTC

Hello,

I'm trying to use the get_dir_list procedure with an Oracle directory as parameter and it doesn't work properly.

What I'm trying to do :

CREATE OR REPLACE DIRECTORY
DBDIR AS
'D:\Appl_Dirs\IO_DIRS\IN\';

--> Somewhere in an integratio procedure I call get_dir_list :

Get_dir_list('DBDIR');

I obtain a java error on the directory DBDIR (that doesn't exist of course.

So I put before the call to get_dir_list a :

Select DIRECTORY_PATH||'/'
INTO l_SourceDir
FROM ALL_DIRECTORIES
WHERE DIRECTORY_NAME = 'DBDIR'; -- in procedure there's a cursor instead of the name...

get_dir_list(l_SourceDir);

And it works quite fine if I apply also a :
Execute dbms_java.grant_permission( 'MYUSER', 'SYS:java.io.FilePermission', 'D:\Appl_Dirs\IO_DIRS\IN\', 'read' );

My question is : How to use directly the Oracle Directory Object when calling get_dir_list ?
(Assuming that MYUSER is granted READ/WRITE on DBDIR object).

Best Regards,
Bruno


Tom Kyte
September 24, 2007 - 7:22 am UTC

you don't - that is the only answer.

the java stuff is JAVA, java works as java works. The directory object - totally "Oracle", not java.

How does this code will change if we have multiple columns?

NP, September 06, 2019 - 6:13 am UTC

I am trying to return table of 3 columns, what change I would need on oracle side and java side? can you please provide example?

I am doing it in 12C, is there any better way to do it like returning restulSet? or table function or pipeline?
Connor McDonald
September 27, 2019 - 3:50 am UTC

Perhaps an easy way in 12c is implicit result sets. You just open your cursors in PLSQL and JDBC can be told about them.

A nice article on that here

https://dzone.com/articles/fetching-oracle-12c-implicit-cursors-with-jdbc-and

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library