Skip to Main Content
  • Questions
  • passing arrays into pl/sql stored procedures

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Abhijit.

Asked: September 07, 2000 - 9:55 am UTC

Last updated: July 05, 2012 - 7:35 am UTC

Version: 8.1.5

Viewed 100K+ times! This question is

You Asked

Dear Sir,

I need to pass a Java String(or any other) array datatype into a PL/SQL stored procedure. I am able to pass String (received as VARCHAR2) and int (recd. as NUMBER) datatypes.

How do I have an array IN parameter in such stored procedure? Since no OAS is being used, I cannot declare a variable as "owa_util.ident_arr". Is there a similar array structure?

rgds, Abhijit.

and Tom said...

For all of the info -- see
</code> http://docs.oracle.com/cd/A81042_01/DOC/java.816/a81354/oraarr3.htm#1056648 <code>

Here is a quick and dirty example showing PLSQL calling Java calling PLSQL and passing an array from Java to PLSQL. The trick is to use a SQL table type -- not a PLSQL type (eg: create the type OUTSIDE of plsql -- that way Java can in fact bind to it). Java cannot bind to PLSQL table types (eg: like owa_util.ident_arr was -- that was a plsql table type -- not a SQL type).


ops$tkyte@DEV816> create or replace
2 and compile java source named "ArrayDemo"
3 as
4 import java.io.*;
5 import java.sql.*;
6 import oracle.sql.*;
7 import oracle.jdbc.driver.*;
8
9 public class ArrayDemo
10 {
11 public static void passArray() throws SQLException
12 {
13 Connection conn =
14 new OracleDriver().defaultConnection();
15
16 int intArray[] = { 1,2,3,4,5,6 };
17
18 ArrayDescriptor descriptor =
19 ArrayDescriptor.createDescriptor( "NUM_ARRAY", conn );
20

21 ARRAY array_to_pass =
22 new ARRAY( descriptor, conn, intArray );
23
24 OraclePreparedStatement ps =
25 (OraclePreparedStatement)conn.prepareStatement
26 ( "begin give_me_an_array(:x); end;" );
27
28 ps.setARRAY( 1, array_to_pass );
29
30 ps.execute();
31
32 }
33
34 }
35 /

Java created.

ops$tkyte@DEV816>
ops$tkyte@DEV816>
ops$tkyte@DEV816> create or replace type NUM_ARRAY as table of number;
2 /

Type created.

ops$tkyte@DEV816>
ops$tkyte@DEV816> create or replace
2 procedure give_me_an_array( p_array in num_array )
3 as
4 begin
5 for i in 1 .. p_array.count
6 loop
7 dbms_output.put_line( p_array(i) );
8 end loop;
9 end;
10 /

Procedure created.

ops$tkyte@DEV816>
ops$tkyte@DEV816> create or replace
2 procedure show_java_calling_plsql
3 as language java
4 name 'ArrayDemo.passArray()';
5 /

Procedure created.

ops$tkyte@DEV816>
ops$tkyte@DEV816> set serveroutput on
ops$tkyte@DEV816> exec show_java_calling_plsql
1
2
3
4
5
6

PL/SQL procedure successfully completed.



Rating

  (84 ratings)

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

Comments

A reader, March 18, 2001 - 7:32 pm UTC


Karol Brejna, July 06, 2001 - 2:36 am UTC


Passing SQL arrays to stored procedures in JSP

Dane Laverty, August 14, 2001 - 1:32 pm UTC

This is the only useful information I found anywhere regarding passing SQL arrays as parameters in stored procedures.

good resource

Rachel, November 13, 2001 - 8:02 pm UTC

Finally a straightforward solution. Thanks!

Right on the money!

Vadim Dolt, August 22, 2002 - 8:20 am UTC


Great but is it possible to pass a Vector to a procedure?

Paul, January 20, 2003 - 11:43 am UTC

Tom, Is it possible to pass a Vector to a stored procedure in the same manner? Or, do you have to read the Vector into an array first?

Tom Kyte
January 20, 2003 - 11:52 am UTC

if by "vector" you mean the java class - you'll need to put it into a SQL datatype first.

How to pass Array from SQL to java stored procedure?

Steve, March 26, 2003 - 11:07 am UTC

Hi Tom,

How about a reverse ?
i.e., how to pass Array from SQL or PL/SQL to java stored procedure?

Thanks!

Steve

How to pass Array from SQL to java stored procedure?

Steve, March 26, 2003 - 4:23 pm UTC

Hi Tom,

I am sorry I did not give you enough info
the following code is what I am trying to do. 

create or replace package myTestPackage1 
as 
     Type STRING_ARRAY IS Table of varchar2(32);
     procedure test_1;

     procedure  testArray(description varchar2, a1 STRING_ARRAY, a2 STRING_ARRAY) as language java 
     name ' testjava1.testArray(java.lang.String, java.lang.String[], java.lang.String[])'; 

end;
/

create or replace package body myTestPackage1 
as 
  procedure test_1 
  is
      testdata STRING_ARRAY := STRING_ARRAY();
      realdata STRING_ARRAY := STRING_ARRAY();
  begin 
      testdata.extend(3);  
      realdata.extend(3);
      
      select 'a', 'b', 'c' 
      into testdata(1), testdata(2), testdata(3)
      from dual;

      select 'a', 'b', 'c1' 
      into realdata(1), realdata(2), realdata(3)
      from dual;
      
      testArray('arrray', realdata, testdata);
      
  end test_1; 
end;
/ 

CREATE OR REPLACE AND COMPILE java  source named "testjava1" 
AS 
import java.sql.*; 
import oracle.jdbc.driver.*; 
public class testjava1
{ 

   public static void testArray(String description, String[] expectedData, String[] realData)
       throws SQLException 
    {
        System.out.println("assertArray" );
        for(int i=0; i<expectedData.length; i++ )
           System.out.println("assertArray" + expectedData[i] + ", " + realData[i]); 
    }
}
/


and when I run
SQL> exec myTestPackage1.test_1
BEGIN myTestPackage1.test_1; END;

*
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
ORA-06512: at "SRS.MYTESTPACKAGE1", line 0
ORA-06512: at "SRS.MYTESTPACKAGE1", line 19
ORA-06512: at line 1



Thanks!

Steve 

Thank you!

Steve, March 26, 2003 - 10:11 pm UTC


How to pass Array from SQL to java stored procedure?

Steve, March 27, 2003 - 10:36 am UTC

Hi Tom,

After runing the example on your book, I figured out
Why my code doesn't work. The difference is that your
code Defines the STRARRAY outside the package, Mine just
define it inside package. My question is Why I can't define String_array inside a package, even though I reference it with Package prefix. Does Oracle design that way?


Thanks!

Steve

create or replace package myTestPackage1
as
Type STRING_ARRAY IS Table of varchar2(32); -- Have to define outside package
procedure test_1;

procedure testArray(description varchar2, a1 STRING_ARRAY ) as language java
name ' testjava1.testArray1(java.lang.String, oracle.sql.ARRAY)';

end;
/

create or replace package body myTestPackage1
as
procedure test_1
is
testdata STRING_ARRAY := STRING_ARRAY();

begin

realdata.extend(3);


select 'a', 'b', 'c1'
into realdata(1), realdata(2), realdata(3)
from dual;

myTestPackage1.testArray('arrray', realdata);

end test_1;
end;
/
show err;

CREATE OR REPLACE AND COMPILE java source named "testjava1"
AS
import java.io.*;
import java.sql.*;
import java.math.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class testjava1
{

public static void testArray1(String description, oracle.sql.ARRAY expectedData) //,oracle.sql.ARRAY realData)
throws SQLException
{
System.out.println("assertArray" );
String[] ss = (String[])expectedData.getArray();
for(int i=0; i<ss.length; i++ )
System.out.println("assertArray" + ss[i] );
}
}
/



Tom Kyte
March 27, 2003 - 11:00 am UTC

PLSQL types are known to exactly PLSQL and PLSQL alone.

SQL types -- stored in the data dictionary -- are visible to all, usable by all.

you must use a SQL type via create type.

Very Helpful

Rusty Bullerman, April 02, 2003 - 10:43 am UTC

This answered the questions no one else is asking on this subject. I perused Oracle documentation as well as the highly-recommended PL/SQL books and didn't find this info.

Thanks,
Rusty Bullerman

Excellent...

So, April 04, 2003 - 7:33 am UTC

I can't begin to describe how useful this website has been to our project. This solution alone is worth its word count in grams of gold!

Our Java developers now want to pass all parameters in name-value arrays. Is there a downside (or two) to this ?

e.g. instead of

myPkg.myProc(resultSet,10,1,'Abc',1,10000,45......etc.)

now use:

pNameArr = ['P1','P2','P3','P4','P4','P6'....etc]
pValueArr= ['10','1','Abc','1','10000','45'....etc]

myPkg.myProc(resultSet,pNameArr,pValueArr)



Tom Kyte
April 04, 2003 - 11:28 am UTC

what version?

So, April 04, 2003 - 3:00 pm UTC

Sorry - it's 9.2EE

Tom Kyte
April 04, 2003 - 6:37 pm UTC

sure -- it'll work fine -- i would suggest the the plsql routine that gets the two collections would then put them into a plsql index by array


create procedure foo( names in array, values in array )
as
type associativeArray is table of varchar2(255) index by varchar2(30);

data associativeArray;
begin
for i in 1 .. names.count
loop
data(names(i)) := values(i);
end loop;


then, plsql can quickly lookup data('fieldX'). this is 9iR2 and up only... before that -- plsql will have to do a linear search for each field

WRT associativearray posting

A reader, April 04, 2003 - 8:21 pm UTC

How about handling an Array of Records in the similar fashion thru associative arrays ? Any Example will help. Is it possible ? the example u gave was like pivoting rows of parameters into columns which is feed to a input array.
If we are dealing with an array of records how do we handle it best if Associative array may not be the right way . version 9.2

Tom Kyte
April 05, 2003 - 11:45 am UTC

ops$tkyte@ORA920> declare
  2          type array is table of emp%rowtype index by varchar2(25);
  3
  4          l_data array;
  5  begin
  6          for x in ( select * from emp )
  7          loop
  8                  l_data(x.ename) := x;
  9          end loop;
 10
 11          dbms_output.put_line( l_data('BLAKE').empno );
 12  end;
 13  /
7698

PL/SQL procedure successfully completed.
 

One more Carification

A reader, April 05, 2003 - 12:14 pm UTC

wrt associative example given above
Followup:
ops$tkyte@ORA920> declare
2 type array is table of emp%rowtype index by varchar2(25);
3
4 l_data array;
5 begin
6 for x in ( select * from emp )
7 loop
8 l_data(x.ename) := x;
9 end loop;
10
11 dbms_output.put_line( l_data('BLAKE').empno );
12 end;
13 /

This question arises because I have to do something similar to name value pairs to accept values from java.
But how to handle an array of records ? name value pair of associative arrays wont work here.
The option i see from your site is each time define a new sql object type and create a table based on that.
Is there any generic approach which will aviod me from having to create lots of objects types for different procedure parameters.

example : emp%rowtype I have to get values from java so obviously it will be sqltype.
But this means for every procedure which gets array from java I will have to create that object type as well that table type.
Can u suggest a better solution.



Tom Kyte
April 05, 2003 - 2:57 pm UTC

there is a necessary and unavoidable "impendance mismatch" between Java and PLSQL...

I don't know what you are trying to do -- however, getting an associative array from plsql to java just isn't "going to happen", nor would you want it to really. In java the natural way to do this will be via a hash table or something similar. In plsql -- a plsql table type.

You cannot have a SQL collection that is indexed by a varchar2 -- there is no corresponding SQL type here.

You cannot bind any 3gl to a PLSQL record type (unless that 3gl just happens to be plsql).

Java will interface to PLSQL via collections and if you want to be obscure, collections of objects. It'll be much more natural and easy for you to interface via collections of simple scalars and in fact, I would propose you actually interface via global temporary tables if you want the least amount of code on both ends. Java can insert easily -- PLSQL can select. Batch the insert and bulk collect and it'll be rather efficient as well.

One more Carification

A reader, April 05, 2003 - 12:16 pm UTC

wrt associative example given above
Followup:
ops$tkyte@ORA920> declare
2 type array is table of emp%rowtype index by varchar2(25);
3
4 l_data array;
5 begin
6 for x in ( select * from emp )
7 loop
8 l_data(x.ename) := x;
9 end loop;
10
11 dbms_output.put_line( l_data('BLAKE').empno );
12 end;
13 /

This question arises because I have to do something similar to name value pairs to accept values from java.
But how to handle an array of records ? name value pair of associative arrays wont work here.
The option i see from your site is each time define a new sql object type and create a table based on that.
Is there any generic approach which will aviod me from having to create lots of objects types for different procedure parameters.

example : emp%rowtype I have to get values from java so obviously it will be sqltype.
But this means for every procedure which gets array from java I will have to create that object type as well that table type.
Can u suggest a better solution.
"SORRY I MISSPELLED MY EMAIL, VERY VERY VERY SORRY"..JUST CORRECTED IT



Pls add these to the ratings ************************************************

A reader, April 05, 2003 - 6:08 pm UTC

Thanx much for your brilliant solns.
Will that also mean I user global temp table even for non array values to ne passed to plsql from java .. in this case it will be just a single row in global temp table for the name value pair solution.So we totally avoid using associative arrays.

right on !!

So, April 07, 2003 - 5:28 am UTC

Tom

Thanks for validating the name-value approach in our java/plsql environment and pointing out associative arrays.

When passing a ref cursor back to java should it be declared IN OUT or OUT (i.e by value or by reference). Is it possible to summarize how all this works in n-tier with regards to memory pointers and structures & table access?

p.s. What I've done and recommend is to buy 2 copies of your book. One for home and one for work. Saves lugging it around! This is the only Oracle book that I consider valuable enough to use a 'duplicate' approach on !!

Tom Kyte
April 07, 2003 - 8:11 am UTC

it'll work but it will be almost certainly slower then using formal named parameters will be.

use OUT for self defined type

Steve, April 10, 2003 - 2:30 pm UTC

Hi Tom,

I have a quick question.
In your example, you can use OUT for self defined type array, but Why I can't? Thanks!


imm@IMMP> CREATE OR REPLACE TYPE STRING_ARRAY AS TABLE OF VARCHAR2 (64);
2 /

Type created.

imm@IMMP> show err;
No errors.
imm@IMMP>
imm@IMMP> create or replace package sqlunit
2 as
3 procedure assertQuery(description varchar2, a1 STRING_ARRAY, a2 STRING_ARRAY) as language java
4 name 'Assert.diffQuery(java.lang.String, java.lang.String, java.lang.String)';
5
6 procedure fetchRefCursorInto(pname in varchar2, parameters in STRING_ARRAY, fetchfields out STRING_ARRAY )
7 as language java name 'Until.fetchRefCursorInto(java.lang.String, oracle.sql.ARRAY , oracle.sql.ARRAY)';
8
9 end sqlunit;
10 /

Warning: Package created with compilation errors.

imm@IMMP> show err;
Errors for PACKAGE SQLUNIT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/6 PL/SQL: Declaration ignored
7/23 PLS-00235: the external type is not appropriate for the parameter
imm@IMMP>




Tom Kyte
April 11, 2003 - 8:09 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8908169959941 <code>

shows how to pass an array as IN and an array as OUT

Very Helpful info. - How about passing 2 D array

Baldeep, May 19, 2003 - 4:26 pm UTC

Hi Tom,

I am able to pass single Dimensional array to Oracle. I am trying to pass a 2-Dimensional Object array to Oracle from java using StructDescriptor and ArrayDescriptor.

I get error "java.sql.SQLException: Inconsistent java and sql object types" while creating the STRUCT.

Could you please suggest me a solution.

Here is the code I am using:

Java code:
/******************************************/
public void addUsers(List users) throws SQLException {
OracleCallableStatement ocs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection dbConn = DriverManager.getConnection( URL, username, password);

//*******Input parameters for the stored proc
StructDescriptor desc1 = StructDescriptor.createDescriptor("EFUSERARRVALUES", dbConn);

ArrayDescriptor desc2 = ArrayDescriptor.createDescriptor("USERARRAYVALUES", dbConn);
//User data
List lstUsers = users;
int size = lstUsers.size();

Object[] userArrObj = new Object[size];
Object[][] userRecObj = new Object[size][4];

//Struct obj and arrays
for (int j = 0; j < size; j++) {

User user = (User) lstUsers.get(j);
userRecObj[j][0] = user.getLdapUserId();
userRecObj[j][1] = user.getFirstName();
userRecObj[j][2] = user.getLastName();

userArrObj[j] = new STRUCT(desc1, dbConn, userRecObj[j]);

}

ARRAY userArr = new ARRAY(desc2, dbConn, userArrObj);

//*****************Run the stored proc ****/
ocs = (OracleCallableStatement) dbConn.prepareCall("{call eforms.ADD_USERS(?)}");

ocs.setArray(1, userArr);
ocs.registerOutParameter(1, OracleTypes.ARRAY, "USERARRAY");

//Execute the proc
ocs.execute();
dbConn.commit();

} catch (Exception e) {

System.out.println("e>"+e);

} finally {
if (ocs != null)
ocs.close();
}
}
/****************************************************/

Procs:
/****************************************************/
CREATE TYPE USERARRAY AS OBJECT(user_id NUMBER,ldap_user_id varchar2(100), first_name varchar2(100),last_name varchar2(100))
/


CREATE TYPE USERARRAYVALUES AS TABLE OF USERARRAY
/


PROCEDURE ADD_USERS(usrarr IN OUT USERARRAYVALUES) IS

l_user_id NUMBER;

BEGIN
FOR i IN 1..usrarr.COUNT
LOOP
BEGIN
SAVEPOINT start_transaction;

SELECT DECODE(MAX(user_id)+1,NULL,1,MAX(user_id)+1)
INTO l_user_id FROM EF_USER_ADDR;

INSERT INTO EF_USER_ADDR (user_id,ldap_user_id,first_name,last_name)VALUES (l_user_id,usrarr(i).ldap_user_id,usrarr(i).first_name, usrarr(i).last_name)

END;
COMMIT;
END LOOP;

EXCEPTION

WHEN OTHERS THEN
RAISE;

END Add_Users;

/****************************************************/



Thanks
Baldeep


A reader, August 18, 2003 - 8:40 am UTC


Running as non-owner

Carol, August 19, 2003 - 6:59 pm UTC

I created grants and synonyms so that I should be able to run the package from an account other than the owner. I haven't been able to get it to work. Is it me or is it possible?

My latest error is:

ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: invalid name pattern: TESTUSER.TESTARRAY

Any help is greatly appreciated.

Thanks.

internal error for non owner of type .

phil, April 22, 2004 - 5:02 am UTC

I am trying to passing an array back and I am getting the following error ..

java.sql.SQLException: invalid name pattern: N_WEB.TY_HIGH_VALUE

my java code and stored procedure are below ... it works fine if the java logs in as the owner of the type. What can I change in order for the n_web non owner to use the type ...

package com.gresham.rtn.access.toplink;

import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleCallableStatement;

import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import java.sql.SQLException;

public class DemoPK {
public static void main(String[] args){

Connection conn = null;
OracleCallableStatement stmt = null;
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:nostrodb", "n_web","pn_web");


stmt =(OracleCallableStatement)conn.prepareCall
( "begin ? := p_utils.GetPartitionMonth('LT'); end;" );

// The name we use below, TY_HIGH_VALUE , has to match the name of the
// type defined in SQL
stmt.registerOutParameter( 1, OracleTypes.ARRAY,"TY_HIGH_VALUE" );
stmt.executeUpdate();


//Get the ARRAY object and print some meta data about it
ARRAY simpleArray = stmt.getARRAY(1);

System.out.println("Array is of type " + simpleArray.getSQLTypeName());

System.out.println("Array element is of type code " +simpleArray.getBaseType());

System.out.println("Array is of length " + simpleArray.length());


// Print the contents of the array
String[] values = (String[])simpleArray.getArray();

for( int i = 0; i < values.length; i++ )
System.out.println( "row " + i + " = '" + values[i] +"'" );

} catch (SQLException se) {
System.out.println(se.toString());
} catch (Exception e) {

System.out.println(e.toString());
} finally {
try {
stmt.close();
conn.close();
} catch (SQLException se) {
System.out.println(se.toString());
}
}
}
}


CREATE OR REPLACE TYPE n_sys.TY_HIGH_VALUE is VARRAY(100) OF VARCHAR2(100);
/


CREATE OR REPLACE package n_sys.p_utils
as


function getpartitionmonth (l_i_table_name varchar2)
return ty_high_value;


end;
/

create or replace package body n_sys.p_utils
as



function getpartitionmonth (l_i_table_name varchar2)
return ty_high_value
is

l_data TY_HIGH_VALUE := TY_HIGH_VALUE ();
cursor c_value is
select high_value
from user_tab_partitions where table_name = l_i_table_name;

l_dateval varchar2(500);
ssql varchar2(500);

begin

for part_rec in c_value
loop
l_dateval := part_rec.high_value;
ssql := 'select to_char( ' || l_dateval || ' ,''YYYYMM'') from dual';
execute immediate ssql into l_dateval;
l_data.extend;
l_data(l_data.count) := l_dateval;
end loop;

return l_data;

end getpartitionmonth;


end p_utils;
/

as always many thanks ...

Tom Kyte
April 22, 2004 - 7:45 am UTC

i don't see any grants? does the other schema have the ability to execute that type.

phil, April 22, 2004 - 8:56 am UTC

when I do the following ...

grant execute on n_sys.ty_high_value to n_web ;
create synonym n_web.ty_high_value for n_sys.ty_high_value;

i get the following ...

java.sql.SQLException: ORA-21700: object does not exist or is marked for delete
ORA-06512: at "SYS.DBMS_PICKLER", line 15
ORA-06512: at "SYS.DBMS_PICKLER", line 67
ORA-06512: at line 1

thanks

Tom Kyte
April 23, 2004 - 8:02 am UTC

9ir2 -- I ran your script "as is", added:

drop user n_sys cascade;
drop user n_web cascade;

create user n_sys identified by n_sys;
grant create session, create type, create procedure to n_sys;

create user n_web identified by n_web;
grant create session to n_web;


to the top of it (to have the users), connected as n_sys, ran your creates, added:

grant execute on ty_high_value to n_web;
grant execute on p_utils to n_web;


to the bottom of it, fixed the code to have n_sys in it (unqualified references):

stmt =(OracleCallableStatement)conn.prepareCall
( "begin ? := n_sys.p_utils.GetPartitionMonth('LT'); end;" );

...

stmt.registerOutParameter( 1, OracleTypes.ARRAY,"N_SYS.TY_HIGH_VALUE" );
stmt.executeUpdate();



and it ran straight off.

Case sensitive!!

Peter, June 02, 2004 - 11:40 am UTC

Very useful, but i still got
ORA-29532: Java call terminated by uncaught Java exception:
java.sql.SQLException: invalid name pattern: blah blah

Then it suddenly dawned on me that the type declarations in the examples where all upper case.
So I created my type in uppercase and made sure that all references were in uppercase.

Then it worked!






Exception with ArrayDescriptor

Gavin, September 17, 2004 - 6:33 am UTC

Hi

I am trying to run this code (line for line) but at the ArrayDescriptor.createDescriptor line I get the following exception. Is this something you are familiar with off the top of your head or do I need to find some more details.
The DB version is 9.2.0.4

Thanks for any help.

java.lang.LinkageError: oracle/sql/TypeDescriptor
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1025)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:137)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:102)
at .....


Tom Kyte
September 17, 2004 - 9:20 am UTC

present it in the fashion I present it - so I can see what you see

create or replace ..........

the entire thing.

Exception with ArrayDescriptor

Gavin, September 20, 2004 - 7:10 am UTC

Hi - as requested.
This code is run from within jdeveloper.

Error occurs at line ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor(
"GAVIN.NUM_ARRAY", connection );

Thanks very much.


SQLPLUS:

CREATE OR REPLACE TYPE NUM_ARRAY AS TABLE OF NUMBER;


PL/SQL:

CREATE OR REPLACE
PROCEDURE two( p_return_code OUT NUMBER, p_array IN NUM_ARRAY )
AS
counter NUMBER := 0;
BEGIN
FOR i IN 1 .. p_array.COUNT LOOP
counter := counter + 1;
END LOOP;
p_return_code := counter;
END;


JDEVELOPER:

package packagename;

import javax.sql.*;
import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class LookupDataAccess
{
public LookupDataAccess()
{
}

public void execute ()
{


OracleCallableStatement cs = null;
Connection connection = null;
int count = 0;
int intArray[] = { 1,2,3,4,5,6 };

try {

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

connection = DriverManager.getConnection("jdbc:oracle:thin:@ipaddress:port:sid", "user", "password");

ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "GAVIN.NUM_ARRAY", connection );
ARRAY array_to_pass = new ARRAY(descriptor, connection, intArray );

cs = (OracleCallableStatement)connection.prepareCall( "{call two(?,?)}" );
cs.registerOutParameter(1, count);
cs.setARRAY( 2, array_to_pass );
cs.execute();

System.out.println("## Count retrieved " + count);

} catch (Exception e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}

}

}

Tom Kyte
September 20, 2004 - 8:53 am UTC

soo, not really line for line -- cause jdev would not have "create or replace"

java.lang.LinkageError: oracle/sql/TypeDescriptor

looks like a classpath issue or something like that. I know not quite enough about java programming to fix something outside the database like that.

java.sql.SQLException: invalid name pattern: SCOTT.EMP_REC

pooja, October 28, 2004 - 3:31 am UTC

Hi tom,
I need a help from regarding the java.sql.SQLException: invalid name pattern: SCOTT.EMP_REC error in SOAP message in PL/SQL WebServices.

i will explain u from the first step how i created and deployed the WebService.

1. i created the package in the scott schema as follows:

CREATE or REPLACE type EMP_REC as object
(
employee_id number(6),
last_name varchar2(25),
job_id varchar2(10),
manager_id number(6),
hire_date date,
salary number(8,2),
commission_pct number(2,2),
department_id number(4)
);
/

CREATE or REPLACE package EMP_FETCHER as
FUNCTION get_emp(emp_no IN NUMBER) RETURN emp_rec;
END;
/
CREATE or REPLACE package body EMP_FETCHER as
FUNCTION get_emp(emp_no IN NUMBER) RETURN emp_rec IS
emp_found employees%rowtype;
emp_rtn emp_rec;
BEGIN
SELECT * INTO emp_found
FROM employees
WHERE employees.employee_id=emp_no;
emp_rtn := emp_rec
(
emp_found.employee_id,
emp_found.last_name,
emp_found.job_id,
emp_found.manager_id,
emp_found.hire_date,
emp_found.salary,
emp_found.commission_pct,
emp_found.department_id
);

RETURN emp_rtn;
END;

END;
/

2. By using the jdeveloper, i converted that package to .sqlj and .java programs.

3. By using the jdeveloper only, i published it as a webservice.

4. In OC4J, i run the following commands:
C:\>java -jar %oc4jhome%\webservices\lib\WebServicesAssembler.jar -config C:\rec_types\config.xml

C:\>java -jar %oc4jhome%\j2ee\home\admin.jar ormi://localhost admin admin -deploy -file /EMP_FETCHER.ear -deploymentName EMP_FETCHER

C:\>java -jar %oc4jhome%\j2ee\home\admin.jar ormi://localhost admin admin -bindWebApp EMP_FETCHER EMP_FETCHER_web http-web-site /WS

these are all executed properly. and when i executed from the localhost, after giving the empno, it is giving the SOAP message as follows:
<?xml version="1.0" encoding="UTF-8" ?>
- <SOAP-ENV:Envelope xmlns:SOAP-ENV="</code> http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="
http://www.w3.org/2001/XMLSchema" > <code>
- <SOAP-ENV:Body>
- <SOAP-ENV:Fault>
<faultcode>SOAP-ENV:Server.Exception:</faultcode>
<faultstring>java.sql.SQLException: invalid name pattern: SCOTT.EMP_REC</faultstring>
<faultactor>/WS/WS</faultactor>
</SOAP-ENV:Fault>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Please tell me what is the mistake i have done.


Passing Array of Objects to Stored Procedure.

Harry, November 24, 2004 - 1:56 am UTC

Hey Tom ,
That was a great source of info. My question is an extenstion of your inputs. Can I pass an array of objects (java object) to a procedure which accepts VARRAY of OBJECT.(SQL object).
I am getting the error of "Fail to convert to internal representation" when I try to pass the array of objects to the procedure using OracleCallableStatement.setARRAY() and ArrayDescriptor. Do I need to do something extra here.




Tom Kyte
November 24, 2004 - 7:18 am UTC

you can pass sql types to sql

java can map to sql types (you can take an object defined in sql and using jpublisher create a mapping class that is a java structure)

you use those types, not just "any old java type" you create.

Passing an array of Objects to Stored Procedure

Harry, November 24, 2004 - 6:45 am UTC

Find the code below.

At Schema Level -

CREATE OR REPLACE TYPE test_object
AS OBJECT
( lookup_type VARCHAR2(30)
,lookup_code varchar2(30)
,meaning varchar2(80));

CREATE OR REPLACE TYPE TEST_OBJECT_ARR is VARRAY(8) of test_object ;

// Java Class to represent oracle object.

public class NomReq
{
public String lookupCode;
public String lookup ;
public String meaning ;
}

// JSP Code to call the procedure

<%
objNomReq = new NomReq();
arrNomReq = new NomReq[2];

objNomReq.lookupCode = "LookUpCode1" ;
objNomReq.lookup = "lookup1" ;
objNomReq.meaning = "Meaning1 " ;

arrNomReq[0] = objNomReq ;

objNomReq.lookupCode = "LookUpCode2" ;
objNomReq.lookup = "lookup2" ;
objNomReq.meaning = "Meaning2 " ;

arrNomReq[1] = objNomReq ;

DBConnection objDB = new DBConnection();
conn = objDB.getConnection();

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("TEST_OBJECT_ARR",conn);
ARRAY arrObj = new ARRAY(desc,conn,arrNomReq);
oCstmt = (OracleCallableStatement) conn.prepareCall("{call TEST_ARRAY1.dump_data(?)}");
oCstmt.setARRAY(1,arrObj);

oCstmt.execute();

Error comes - Failed to convert to internal representation

Tom Kyte
November 24, 2004 - 7:52 am UTC

please read about jpub. otn.oracle.com has the documentation freely available.

Passing Array to oracle stored procedure

Manish, January 04, 2005 - 1:19 am UTC

how can i pass the Array from java to oracle stored procedure...???

Tom Kyte
January 04, 2005 - 8:28 am UTC

hmm, the answer above starts with

....
Here is a quick and dirty example showing PLSQL calling Java calling PLSQL and
passing an array from Java to PLSQL.
............

Thanks

Satish Mehta, January 15, 2005 - 2:26 am UTC

Hi Tom,
Suppose I have to choose between a PL/SQL table or a temp table (local) where these will be created and destroyed in each session over an online web site - normally having 5000 user session, which one would you recommend - performance wise?


Tom Kyte
January 15, 2005 - 9:41 am UTC

there is no such thing as a local temporary table in Oracle.

What about XA?

Paedagogus, January 20, 2005 - 11:22 am UTC

Are there any known issues surrounding the use of XA with arrays? We have a distributed transaction in which we need to pass an array to a stored procedure. The call succeeds, but it fails to commit. If we perform this outside of the transaction it works.

Tom Kyte
January 20, 2005 - 7:13 pm UTC

not that I'm aware of -- but then again, the description of the problem you give is, well -- vague.

Performance Considerations?

Michael, May 05, 2005 - 12:31 pm UTC

Having used a similar approach to the one described above (i.e. top of the page) in one area of our system, I was wondering if there are any Oracle performance issues associated with using Types extensively through a system.

The reason I ask is that we are planning on simplifying our pl/sql API so that Java can pass objects and collections of objects to the pl/sql layer so we can hide some of our database logic and implementation from the Java layer.

For example if the Java layer wished to add an employee all it needs to do is pass its employee object down and pl/sql will add the data to the relevant tables.

The same with if java needed to add multiple employee phone numbers, an array of employee phone number objects can be passed to pl/sql.

Now while it worked very well for the section of our system that I used it in and cut down the number of times the Java layer had to execute a store procedure, I am aware that nothing in computing is ever as simple as it at first seems and there are always downsides to every approach. I was wondering if there are any issues that need to be taken into account when using this approach(i.e. passing arrays or arrays of objects to pl/sql).

Thank you in advance


Tom Kyte
May 05, 2005 - 1:09 pm UTC

I have not run into any -- but like anything, this can be overused and abused. I have seen a system where all interactions was done via pipelined functions -- that didn't work too well (sometimes a ref cursor is what you want to return, you don't want to fetch it in plsql to pipe it out again)

Non oracle drive

Anil, May 17, 2005 - 12:29 pm UTC

Hi

Inorder to use the technique that you have mentioned we need to use oracle supplied classes. Is this possible to send an arary from JAVA to sored procedure with out using oracle supplied drivers.

Rgds
Anil

Tom Kyte
May 17, 2005 - 2:16 pm UTC

you would have to ask the maker of the non-oracle supplied drivers I guess?

Ashutosh Sharma, May 20, 2005 - 9:04 am UTC

I keep on getting the error:
java.sql.SQLException: invalid name pattern: SPIRAL.NUM_ARRAY
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:495)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:405)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1020)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:136)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:101)
at com.aol.talkback.tools.exportdata.StackTraceWithBBID.execute(StackTraceWithBBID.java:62)
at com.aol.talkback.tools.exportdata.StackTraceWithBBID.callerMethod(StackTraceWithBBID.java:181)
at com.aol.talkback.tools.exportdata.StackTraceWithBBID.main(StackTraceWithBBID.java:200)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:78)

Even after following all the tips discussed here


Tom Kyte
May 20, 2005 - 10:31 am UTC

hmm, no example from you..... I gave you mine, you give me yours (as small as possible).

then we can find the mistake.

My code

Ashutosh Sharma, May 20, 2005 - 9:07 am UTC

This is my function:
create or replace type NUM_ARRAY as table of number;



create or replace function getStack(bbid_array in num_array) return types.ref_cursor
as
l_cursor types.ref_cursor;
begin
for i in 1 .. bbid_array.count
loop
open l_cursor for select fc__keytable_1077.keyorder, fc__keytable_1077.keyvalue from fc__keytable_1077 where

fc__keytable_1077.bbid = bbid_array(i);
end loop;
return l_cursor;
end;
/

And this is my java code.
package com.aol.talkback.tools.exportdata;

import com.aol.talkback.tools.exportdata.db.DBUtils;

import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Iterator;
import java.sql.*;

import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.driver.OracleStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.sqlj.runtime.Oracle;
import oracle.sql.ArrayDescriptor;
import oracle.sql.ARRAY;

/**
* User: ashutoshsh
* Date: May 11, 2005
* Time: 10:58:57 AM
*/
public class StackTraceWithBBID
{

private String stackSQL = "select fc__Keytable_1033.keyorder keyorder, fc__keytable_1033.keyvalue stack_trace from blackboxes_by_deployment, fc__keytable_1033 where fc__keytable_1033.bbid = blackboxes_by_deployment.bbid and blackboxes_by_deployment.bbid = ? ";

private Connection conn = null;
private PreparedStatement pstmt = null;
private Statement stmt = null;
private ResultSet rs = null;
private PrintWriter pwOut = null;
private FileOutputStream fOut = null;
String name = null;
String dateString=null;
String hourString = null;
String version = null;
long bbid[] =null;
File fNewFile = null;
String prepareCallString = "begin :1 := getStack(:2); end;";
CallableStatement cstmt = null;
ArrayDescriptor descriptor = null;



private void execute()
{
//read thru the bbid dir all the non zero length files-these are the bbids.
//grab the date, hour, Version from the files
//read all the BBIDs on that date and particular hour
//execute the stackTrace query for those BBIDs and
//save the result into output directory.
//

final File file = new File("bbid"+File.separator);
File filesInThisDir[] = file.listFiles();
try
{
cstmt = conn.prepareCall(prepareCallString);
cstmt.registerOutParameter(1,OracleTypes.CURSOR) ;
//descriptor = ArrayDescriptor.createDescriptor( "NUM_ARRAY", conn );
descriptor = ArrayDescriptor.createDescriptor( "SPIRAL.NUM_ARRAY", conn );

//pstmt = conn.prepareStatement(stackSQL);
//stmt = conn.createStatement();
//((OracleStatement)pstmt).setRowPrefetch(50);
//((OracleStatement)stmt).setRowPrefetch(50);
}
catch (SQLException e)
{
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
for(int i = 0; i < filesInThisDir.length; i++)
{
//System.out.println(filesInThisDir[i].getName());
name = filesInThisDir[i].getName();
dateString = name.substring(0,10);
hourString = name.substring(11,13);
version = name.substring(14);
version = version.substring(0, version.indexOf('-'));
//System.out.println("dateString-->"+dateString);
//System.out.println("hourString-->"+hourString);
//System.out.println("version-->"+version);
StringBuffer stringBuffer = new StringBuffer().append("output");
fNewFile =new File(stringBuffer.append(File.separator).append(dateString).append("-").append(hourString).append('-').append(version).append("-").append("stackTrace output.txt").toString());
long t1 = System.currentTimeMillis();
bbid = readTheBBIDFile(filesInThisDir[i]);
long t2 = System.currentTimeMillis();
System.out.println("Time taken for reading one file-->"+(t2-t1));
try
{
if(fNewFile.exists())
{
fOut = new FileOutputStream(fNewFile, true);
}
else
{
fOut = new FileOutputStream(fNewFile);
}
}
catch (FileNotFoundException e)
{
e.printStackTrace();
}
pwOut = new PrintWriter(fOut);
//System.out.println(filesInThisDir[i].getName());
//System.out.println("BBIDs in the file are:");
//rather than using hte

try
{
ARRAY array_to_pass = new ARRAY( descriptor, conn, bbid );

cstmt.setArray(2, array_to_pass);

rs = (ResultSet)cstmt.executeQuery();;

while(rs.next())
{
int keyorder = rs.getInt(1);
String stack_trace = rs.getString(2);
System.out.println(keyorder+","+stack_trace);
}
}
catch (SQLException e)
{
e.printStackTrace();
}











}


}
private long[] readTheBBIDFile(File file)
{

List bbidList = new ArrayList();

String tempBBID = null;
try
{
BufferedReader bbidFile = new BufferedReader(new FileReader(file));

while((tempBBID = bbidFile.readLine()) != null )
{
tempBBID = tempBBID.trim();
//if(!(tempBBID.equalsIgnoreCase("\n") && tempBBID.equalsIgnoreCase("\r\n")))
bbidList.add(tempBBID);

}
}
catch (IOException e)
{
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
Iterator iter = bbidList.iterator();
long bbidArray[] = new long[bbidList.size()];
for(int i = 0; iter.hasNext() ; i++)
{
Object o = iter.next();
bbidArray[i] = Long.parseLong(o.toString());
}
return bbidArray;

}
private void callerMethod()
{
long start = System.currentTimeMillis();
conn = DBUtils.getConnection();

execute();
try
{
rs.close();
pstmt.close();
//stmt.close();
}
catch (SQLException e)
{
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
DBUtils.closeConnection(conn);
long end = System.currentTimeMillis();
System.out.println("Total time taken with writing to BBID files"+(end-start));
}

public static void main(String args[])
{
StackTraceWithBBID stBBID = new StackTraceWithBBID();
stBBID.callerMethod();
}
}



Tom Kyte
May 20, 2005 - 10:32 am UTC

can you make it self contained, so I can run it too -- small small small. Just enough to get the error.

ok this is somewhat smaller version of this

Ashutosh Sharma, May 23, 2005 - 2:51 am UTC

This is my function:
create or replace type NUM_ARRAY as table of number;



create or replace function getStack(bbid_array in num_array) return
types.ref_cursor
as
l_cursor types.ref_cursor;
begin
for i in 1 .. bbid_array.count
loop
open l_cursor for select fc__keytable_1077.keyorder, fc__keytable_1077.keyvalue
from fc__keytable_1077 where

fc__keytable_1077.bbid = bbid_array(i);
end loop;
return l_cursor;
end;
/


And this is my java code-only the part which is causing the problem:
public class StackTraceWithBBID
{
////
String prepareCallString = "begin :1 := getStack(:2); end;";
CallableStatement cstmt = null;
ArrayDescriptor descriptor = null;
try
{
cstmt = conn.prepareCall(prepareCallString);
cstmt.registerOutParameter(1,OracleTypes.CURSOR) ;
//descriptor = ArrayDescriptor.createDescriptor( "NUM_ARRAY", conn
);
descriptor = ArrayDescriptor.createDescriptor( "SPIRAL.NUM_ARRAY",
conn );

try
{
ARRAY array_to_pass = new ARRAY( descriptor, conn, bbid );

cstmt.setArray(2, array_to_pass);

rs = (ResultSet)cstmt.executeQuery();;

while(rs.next())
{
int keyorder = rs.getInt(1);
String stack_trace = rs.getString(2);
System.out.println(keyorder+","+stack_trace);
}


The errors i m getting are:
java.sql.SQLException: invalid name pattern: SPIRAL.NUM_ARRAY
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:495)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:405)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1020)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:136)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:101)
at com.aol.talkback.tools.exportdata.StackTraceWithBBID.execute(StackTraceWithBBID.java:62)
at com.aol.talkback.tools.exportdata.StackTraceWithBBID.callerMethod(StackTraceWithBBID.java:181)
at com.aol.talkback.tools.exportdata.StackTraceWithBBID.main(StackTraceWithBBID.java:200)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)


and

java.sql.SQLException: Missing descriptor: ARRAY
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:916)
at oracle.sql.ARRAY.<init>(ARRAY.java:107)
at com.aol.talkback.tools.exportdata.StackTraceWithBBID.execute(StackTraceWithBBID.java:112)
at com.aol.talkback.tools.exportdata.StackTraceWithBBID.callerMethod(StackTraceWithBBID.java:181)
at com.aol.talkback.tools.exportdata.StackTraceWithBBID.main(StackTraceWithBBID.java:200)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:78)





Tom Kyte
May 23, 2005 - 10:50 am UTC

Ok, this is what a small test case would look like....

I assume you meant to send an "in list" to the procedure and not open up N queries -- returning ONLY the last one -- so wrote the code to match:

spiral@ORA9IR2> create or replace type NUM_ARRAY as table of number
2 /

Type created.

spiral@ORA9IR2>
spiral@ORA9IR2> create or replace function getStack(bbid_array in num_array) return sys_refcursor
2 as
3 l_cursor sys_refcursor;
4 begin
5 open l_cursor
6 for
7 select *
8 from all_users
9 where user_id in (select *
10 from TABLE(cast( bbid_array as num_Array) )
11 );
12
13 return l_cursor;
14 end;
15 /

Function created.


import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class nesttest
{
private static Connection conn;

private static void process() throws Exception
{
String sql = "begin ? := getStack(?); end;";

long[] elements = { 1,2,3,4,5 };

ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("SPIRAL.NUM_ARRAY", conn);
ARRAY array = new oracle.sql.ARRAY(arrayDesc, conn, elements);

CallableStatement cstmt = conn.prepareCall(sql);
cstmt.registerOutParameter(1,OracleTypes.CURSOR) ;
cstmt.setArray( 2, array );
cstmt.execute();

ResultSet rs = (ResultSet)cstmt.getObject(1);
while(rs.next())
System.out.println( rs.getString(1) );
}
static public void main(String args[])
{
try
{
DriverManager.registerDriver(new OracleDriver());
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ora9ir2","spiral","spiral");
conn.setAutoCommit(false);
process();
conn.close();
} catch (Exception e)
{
e.printStackTrace();
}
}

}

[tkyte@xtkyte-pc j]$ java nesttest
SYSTEM
[tkyte@xtkyte-pc j]$


Passing Objects from Java to Oracle

Jayesh Kakkad, July 04, 2005 - 3:40 am UTC

It indeed helped me to focus on one thing apart from different combinations to solve the problem.

Cheers,
Jayesh

Passing an Array from Java to Pl/sql

Anoop Sagar Pradhan, October 19, 2005 - 11:33 am UTC

I am using the following code to get the array into pl/sql

CREATE OR REPLACE TYPE STRARRAY AS TABLE OF VARCHAR2 (255)

DECLARE
L_OUT STRARRAY := STRARRAY ();
BEGIN
L_OUT := pass_f ('68381602013');
FOR I IN 1 .. L_OUT.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE ('l_out('|| I || ') = ' || L_OUT (I) );
END LOOP;
END;

CREATE OR REPLACE FUNCTION pass_f (
collectorid IN VARCHAR2
)
RETURN STRARRAY
AS
LANGUAGE JAVA
NAME 'com.mountain.molehill.crm.client.CRMTransferClient.transactionDetails(java.lang.String)return java.lang.Array';
/

I am getting ORA:00932 Inconsistent Datatype Conversion

Thanks!

Anoop Sagar Pradhan, October 20, 2005 - 2:40 am UTC

Hi Tom,
Thanks for the help.
My requirement is that I have to collect a custom DTO returned from an EJB into an Oracle collections type. Then use this output to populate fields in Oracle Form(6i).
I am able to retrieve the array into pl/sql procedure but that array contains concatenated values
(eg. var_tab(7) = 0003||5433542.34||Sponsor Bonus||20040923003412)
which I need to segregate and put them in a record type. This record type will be inside a pl/sql procedure and this procedure will update the fields in the Form.
what is the best way to get the individual values from the array element? I am trying to return list of arrays from my java stored proc. Can u give me some sample code for that? How do i collect this list of arrays in pl/sql and read each individual array for its elements?

Regards,
Anoop


Tom Kyte
October 20, 2005 - 8:10 am UTC

"DTO"? The Dublin Transportation Office perhaps ;)


why would you encode data in a string like that, why not just select columns in the first place.


why are you using a java stored procedure for something that should be done in plsql?

Requirement

AnoopSagar Pradhan, October 21, 2005 - 7:18 am UTC

My requirement is like:
I have to populate a datablock in a form with records returned by an EJB (residing in weblogic server). I am using the Java stored proc in Oracle to convert the custom datatype object(custom java view object)into oracle type.
I am getting an array as a return from the java stored procedure. I tried retireving the return as an array of arrays into plsql but I got the error ORA-00932.
So I am retrieving it as a simple array.
Now I hav to create a record type and populate that with the elements of this array. How do I do this?
On this record type I will run ref cursor and populate the FORM.
I am facing problems at :
1. When I try to insert values into a temporary table it says "Not enough values".
2. I donot know how to populate a record type.
3. I cannot use any permanent storage of the records(to minimise replication).

regards,
Anoop

Tom Kyte
October 21, 2005 - 8:33 am UTC

sorry, no example, no comment - not sure why you are using a java stored procedure or what a "custom java view object" is or how it got all of the way down to a java stored procedure (since you presumably are passing it via a plsql layer and plsql just isn't going to accept "java")

java.lang.ClassCastException

Michel Thibodeau, January 04, 2006 - 1:36 pm UTC

Using :
Tomcat 5
JDK 1.4
I've got the following error with the following code.
Oracle
CREATE TYPE type1 AS VARRAY(10) OF VARCHAR2(10)

JSP
<%@ page import="java.sql.*" %>


<% //get a connection
Class.forName("com.codestudio.sql.PoolMan").newInstance();
Connection connection =DriverManager.getConnection("jdbc:poolman://oraclePI");

oracle.sql.ArrayDescriptor numberArray = oracle.sql.ArrayDescriptor.createDescriptor("TYPE1", connection);

ERROR
java.lang.ClassCastException
oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:108)
org.apache.jsp.secur.plVarray_jsp._jspService(plVarray_jsp.java:225)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)



any idees


Tom Kyte
January 04, 2006 - 2:28 pm UTC

I'd say get rid of the jsp, try to reproduce on command line, include entire script (creat type, users involved). eg: maybe the type is owned by someone else and needs to be qualified.

You have to love java exception stacks - thats a short one actually.

Doing everything you say...but still....no dice.

David, January 10, 2006 - 4:40 pm UTC

Snippets (note, we have a couple layers between the top-level Java code and the actual final call to createDescriptor...)

create type StudentIdArrayType as varray(1000) of integer;

create or replace package body pkg_custom_group is

procedure save_custom_group
(
i_name varchar2,
i_admin varchar2,
i_login varchar2,
i_student_ids in StudentIdArrayType,
i_update_id integer,
o_group_id out integer
) is
begin
if i_update_id is null or i_update_id = 0 then
-- doing an insert; get a new sequence number first
select custom_group_seq.nextval into o_group_id from dual;

insert into custom_group (id, name, administration, creator_id)
values (o_group_id, i_name, i_admin, i_login);
else
o_group_id := i_update_id;

update custom_group
set name = i_name
where id = i_update_id;

-- update: remove all students, so we can reinsert them next.
delete from custom_group_student
where group_id = i_update_id;
end if;

if i_student_ids is not null then
for i in 1..i_student_ids.count loop
insert into custom_group_student (group_id, student_id, seq)
values (o_group_id, i_student_ids(i), i);
end loop;
end if;
end save_custom_group;

-- delete the custom group (and all the students in the group) from the database
procedure delete_custom_group
(
i_group_id integer
) is
begin
-- will cascade delete to custom_group_student
delete from custom_group where id = i_group_id;
end delete_custom_group;

end pkg_custom_group;

SNIPPET FROM Java:

ArrayDescriptor dscrpt = ArrayDescriptor
.createDescriptor(
"StudentIdArrayType",
statement.getConnection());
statement.setArray(masterIndex, new ARRAY(dscrpt, statement.getConnection(),
param.getArray()));

Yet I still get java.sql.SQLException: invalid name pattern: DPLASS.StudentIdArrayType


Tom Kyte
January 10, 2006 - 7:28 pm UTC

I cannot work with snippets - remove all of the code in the plsql unit (not relevant to the example) and make the java code as small as possible

hence resulting in a

o concise
o yet 100% complete
o but small
o however in its entirety

test case.

(although I'd look at the CASE - like where I used NUM_ARRAY and not Num_Array since SQL uppercases everything unless you use quoted identifiers...)

MG, January 23, 2006 - 9:22 am UTC

Hi Tom,

I have a user defined Type as follows. When I try to drop that, it alwas says error:

ORA-21700: object does not exist or is marked for delete

Object Type :
------------
CREATE OR REPLACE TYPE FONDSRPT.t_sum_lt_zero_number AS OBJECT (
total NUMBER, /* your final result aggregate type, see function */
STATIC FUNCTION odciaggregateinitialize (
start_context IN OUT t_sum_lt_zero_number
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT t_sum_lt_zero_number,
VALUE IN NUMBER /* your input type */
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate (
SELF IN t_sum_lt_zero_number,
returnvalue OUT NUMBER /* result type */,
flags IN NUMBER
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT t_sum_lt_zero_number,
second_context IN t_sum_lt_zero_number
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatedelete (
SELF IN OUT t_sum_lt_zero_number,
VALUE NUMBER
)
RETURN NUMBER
)
/

CREATE OR REPLACE TYPE BODY FONDSRPT.t_sum_lt_zero_number
IS
STATIC FUNCTION odciaggregateinitialize (
start_context IN OUT t_sum_lt_zero_number
)
RETURN NUMBER
/* This function initializes the context of aggregation,
that is creates a new my_aggregate_type and initializes its attributes */
IS
BEGIN
start_context := t_sum_lt_zero_number (0);
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT t_sum_lt_zero_number,
VALUE IN NUMBER /* input type */
)
RETURN NUMBER
/* This function implements the aggregation algorithm */
IS
BEGIN
/* Follows the code to aggregate the preceding result with the current data */
/* In our example, this is just a product */
IF VALUE < 0
THEN
SELF.total := SELF.total + VALUE;
END IF;

RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateterminate (
SELF IN t_sum_lt_zero_number,
returnvalue OUT NUMBER,
flags IN NUMBER
)
RETURN NUMBER
/* This function does the final stuff */
/* In our example, we make the square root */
IS
BEGIN
returnvalue := SELF.total;
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT t_sum_lt_zero_number,
second_context IN t_sum_lt_zero_number
)
RETURN NUMBER
/* This function merge two contexts */
IS
BEGIN
-- SELF.total := second_context.total+SELF.total;
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregatedelete (
SELF IN OUT t_sum_lt_zero_number,
VALUE NUMBER
)
RETURN NUMBER
IS
BEGIN
IF VALUE < 0
THEN
SELF.total := SELF.total - VALUE;
END IF;

RETURN odciconst.success;
END;
END;
/


Could you please tell , how could I drop this type ?

Thank you



Tom Kyte
January 23, 2006 - 10:39 am UTC

ops$tkyte@ORA9IR2> drop type t_sum_lt_zero_number;

Type dropped.


I cannot reproduce, suggest you follow this with support. 

A reader, January 23, 2006 - 10:46 am UTC

Hi Tom,

I have tried with

drop type

and

drop type .. force

both command. But same message comes. I read the articles in meta link too. But couln't found the correct solution.

Do you have any idea?

Thank you for your time.

Tom Kyte
January 23, 2006 - 10:53 am UTC

i gave you my idea above?!?

file a tar.

Using user data type in clients

Jairo Ojeda, March 13, 2006 - 1:00 pm UTC

Tom, you said that
" ...
SQL types -- stored in the data dictionary -- are visible to all, usable by all.
you must use a SQL type via create type. "
So, I create a user data type like
CREATE OR REPLACE TYPE BNFPUBLIC.ARGTYPE AS TABLE OF VARCHAR2(4000);
but do you know how can I use this type by C#.NET to call a procedure like pkg_prog.pp_proc1(pkey, pcolumns, pvalues), where pcolumns and pvalues are argtype data types?

What about JPub?

A reader, June 24, 2006 - 10:33 am UTC

Can you some shed some light on JPub? How it fares compared to the approach you have used?

If I were to use JPub I would just have to create a class for the object type and the proc and call the proc from my Java class?

Tom Kyte
June 24, 2006 - 11:45 am UTC

JPub can be used to create mapping classes for arbitrary object types and collections thereof.

If the question is "how to pass an array", jpub would be "overkill".

If the question is "how to pass arbitrary sql object types and collections thereof", jpub would be the right answer.

want to delete multiple records passed from java.

Vijay, August 23, 2006 - 8:07 am UTC

Hi Tom,
good day to you as always, wanted some help from you, in a scenario where a user can select multiple records from GUI and press delete button, what should be the best way to call pl/sql stored procedure from java, should the unique id be passed as comma seperated values or should array be passed from java to pl/sql or is there any other way to handle this in the best way.

Your help in this regard is much appreciated.

Thanks in advance.

Regards,
Vijay

Tom Kyte
August 27, 2006 - 3:33 pm UTC

if they could pass a collection, it would be nice. As demonstrated above.


then you can:


create procedure your_proc( plsql_variable in num_array )
as
begin

delete
from t
where id in (select * from TABLE(cast plsql_variable as num_array)));

end;


i want the following

RAJESH.SUGURU, November 06, 2006 - 5:19 pm UTC

i want to generate graph in pl/sql by taking year on x_axis and sal on y_axis where year and sal are columns of emp table.i am getting problem while displaying salaries like 122.5 and 1667.75 how to generate graph to show exact value of salary for each employee up to five years.i think that i given sufficient information to generate a graph.
i am gladful to u if u give me the solution for this query.
note :we have to use pl/sql code as well precised scale for y_axis(salary) and have to generate exact value of salary.
thanking you,
rajesh.suguru

Tom Kyte
November 07, 2006 - 4:25 pm UTC

go for it?

You have given zero information relevant to generating a graph, nothing, nada, nunca - ZERO, ZIPPO.

And besides, "U" doesn't work here, never has. If you know them, let me know, I'd like to give them a long list of requests.


Maybe you want to look at apex:

</code> http://apex.oracle.com/ <code>

it is capable of - IN A BROWSER environment, using SVG, generating graphs.

What about WL8 SP6

Arun, March 06, 2007 - 8:07 am UTC

Tom the java code given by you will not work whne using weblogic 8 SP6. Because when you pass conn object from a connection pool to ArrayDescriptor it doesn't work.
Here is the code snippet on a WL8 SP6 server:
DataSource dataSource = (DataSource)envContext.lookup("jdbc/OMSDataSource");
Connection con=dataSource.getConnection();
ArrayDescriptor descriptor =ArrayDescriptor.createDescriptor("STR_ARRAY",con);


Can u please help me in solving this problem.

Thanks.
Tom Kyte
March 06, 2007 - 11:09 am UTC

"U" is not here. sorry.


"it doesn't work"
"how very descriptive"


WL8 Problem

Sean, March 30, 2007 - 5:25 am UTC

Hi,

In response to Arun (posted March 06, 2007) I believe that your problem stems from the fact weblogic returns a wrapped connection and ArrayDescriptor actually requires an oracle.jdbc.OracleConnection (despite the fact that the signature says java.sql.Connection is fine).

What you need to do is get hold of the direct vendor connection (I believe there is a method in weblogic that will give you this) and then create the ArrayDescriptor with that.

This is a guess though, given the lack of the actual error.

Hope it helps!

Exception in thread "main" java.sql.SQLException: invalid name pattern: OSS_TDS.n_idx_tnb_varray

Srikanth Reddy, June 14, 2007 - 4:48 pm UTC

Hi Tom,
I am trying to pass an array to a oracle stored procedure and getting "java.sql.SQLException: invalid name pattern: OSS_TDS.n_idx_tnb_varray"

Can you please tell me what is wrong here? Thanks a lot for the help.

//java code

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@claymores.mcilink.com:1535:DOSS3","oss_tds","abc123");

ArrayDescriptor desc1 = ArrayDescriptor.createDescriptor( "n_idx_tnb_varray", conn );
ArrayDescriptor desc2 = ArrayDescriptor.createDescriptor( "n_tnb_varray", conn );
ArrayDescriptor desc3 = ArrayDescriptor.createDescriptor( "n_pri_varray", conn );

String[] idx_tnb_sp = new String[]{"aaa","bbb","ccc"};
String[] tnb_sp = new String[]{"0000000000","1111111111","2222222222"};
String[] tnb_pri_sp = new String[]{"zzz","yyy","xxx"};

oracle.sql.ARRAY array_to_pass_1 = new oracle.sql.ARRAY(desc1, conn, idx_tnb_sp);
oracle.sql.ARRAY array_to_pass_2 = new oracle.sql.ARRAY(desc2, conn, tnb_sp);
oracle.sql.ARRAY array_to_pass_3 = new oracle.sql.ARRAY(desc3, conn, tnb_pri_sp);

CallableStatement proc = null;
proc = conn.prepareCall("{ call tds_upd_pkg.UPD_tab_tnb_blk(?, ?, ?) }");
proc.setArray(1, array_to_pass_1);
proc.setArray(2, array_to_pass_2);
proc.setArray(3, array_to_pass_3);
proc.execute();
//
//stp info

create or replace type n_tnb_varray IS TABLE of CHAR(10) / create or replace type n_idx_tnb_varray IS TABLE of VARCHAR2(12) / create or replace type n_pri_varray IS TABLE of VARCHAR2(10) / CREATE OR REPLACE PACKAGE tds_upd_pkg AS

PROCEDURE UPD_tab_tnb_blk(
v_idx_tnb IN n_idx_tnb_varray,
v_tnb IN n_TNB_varray,
v_tnb_pri IN n_pri_varray); END tds_upd_pkg;
//

PROCEDURE UPD_tab_tnb_blk(
v_idx_tnb IN n_idx_tnb_varray,
v_tnb IN n_TNB_varray,
v_tnb_pri IN n_pri_varray)
AS
x NUMBER;
BEGIN
FOR x IN v_idx_tnb.FIRST .. v_idx_tnb.LAST
LOOP
UPDATE ossR_tnb
SET tnb_pri= v_tnb_pri(x)
WHERE idx_tnb= v_idx_tnb(x) and
tnb = v_tnb(x);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;


Tom Kyte
June 14, 2007 - 8:29 pm UTC

case - you created it in upper case (non-quoted identifiers in SQL are rolled to uppercase), you referenced it in lower case...

A reader, July 27, 2007 - 8:07 am UTC

If front end people can not pass collection(array) then what should be best way to insert multiple records.
Sending records in a string by concatenating with string
OR
Calling procedure in Loop from front end.

Is calling procedure in loop is slower then sending records in a string. In sending strings there will be extra cost involved in making string and then parsing in procedure.

Tom Kyte
July 27, 2007 - 3:07 pm UTC

by teaching front end people how to pass arrays?



A reader, July 30, 2007 - 1:03 am UTC

"If front end people can not pass collection(array) then what should be best way to insert multiple
records.
Sending records in a string by concatenating with string
OR
Calling procedure in Loop from front end.

Is calling procedure in loop is slower then sending records in a string. In sending strings there
will be extra cost involved in making string and then parsing in procedure."

"
by teaching front end people how to pass arrays? "

We cannot follow any of

Sending records in a string by concatenating with string
OR
Calling procedure in Loop from front end.

If we have to choose from above what you recommand.
Tom Kyte
July 30, 2007 - 5:27 pm UTC

I'll keep going with option 3, by teaching front end people how to pass data.


Or, have them array insert their inputs into a global temporary table in one call- and the call the stored procedure which will get its inputs from the global temporary table.

Or, calling procedure in loop slow by slow from front end as a last resort.

A reader, August 02, 2007 - 8:14 am UTC

If we have multiple columns on front end
Should we need to send each column as a different array.
Send all the columns as a arrays of the array.
As a object.
Tom Kyte
August 05, 2007 - 10:31 am UTC

what is a front end - like a front end loader?

the choice of language would be relevant.

In most cases, a set of scalar array inputs might be easiest, but in java - using jpublisher - mapping to a collection of objects is pretty easy.

After migration from 9i --> 10g starts complaining

Bipin, October 12, 2007 - 7:18 am UTC

Hi Tom,

We were use to call the below function Func, however it fails when we migrate to 10g.

ORA-29532: Java call terminated by uncaught Java exception: java.lang.NoSuchMethodError
ORA-06512: at <<package_name.procedure_name>>, line 1
ORA-06512: at line 8

CREATE OR REPLACE FUNCTION Func(xml IN VARCHAR2,xsd IN VARCHAR2)
return varchar2
IS LANGUAGE JAVA NAME
'SchemaUtil.validation(oracle.sql.CHAR,oracle.sql.CHAR) returns java.lang.String';
/

Do let me know if I need to follow certain steps to avoid the error.

Regards,
Bipin Ganar

Divyesh, October 13, 2007 - 7:47 am UTC

Hi Tom,
Could you please let me know what is limitation of number of arguments that can be passed from a Stored Procedure.

Thanks
Tom Kyte
October 14, 2007 - 2:30 pm UTC

it is only limited by your ability to type....

practically, you are limited by your patience, would you bind 10,000 inputs?

More than enough would be the answer - there are no documented limits, only practical ones.

Test Case Doubt

Narendra, November 23, 2007 - 8:45 am UTC

Hello Tom,

Can you please help me in resolving this ?
I am trying to use your (following) test case as demonstrated on 23rd february 2005.
I am trying it on Oracle Database 8.1.7.4 with JDBC driver 9.2.0.4.0.
I have made some changes (marked in bold) to the code to return number instead of ref cursor.
However, on compiling the JAVA class, I am getting error while compiling the JAVA class "setArray(int, java.sql.Array) in java.sql.PreparedStatement can not be applied to (int, oracle.sql.ARRAY)" for the statement "cs.setArray(2, array);"
============================================================
create or replace type NUM_ARRAY as table of number
 
Type created.
 
create or replace function getStack(bbid_array in num_array) return <b>NUMBER</b>
 as
     <b>l_cnt NUMBER(4);</b>
   begin
           open l_cursor
           for
           select <b>count(*)
             into l_cnt</b>
             from all_users
            where user_id in (select *
                                from TABLE(cast( bbid_array as num_Array) ));
 
           return </b>l_cnt</b>;
  end;
/ 
Function created.

import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
                                                                                                    
                      
public class nesttest
{
   private static Connection conn;
                                                                                                    
                      
   private static void process() throws Exception
   {
      String sql = "begin ? := getStack(?); end;";
                                                                                                    
                      
      long[] elements = { 1,2,3,4,5 };
                                                                                                    
                      
      ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("<b>SCOTT</b>.NUM_ARRAY", conn);
      ARRAY array = new oracle.sql.ARRAY(arrayDesc, conn, elements);
                                                                                                          CallableStatement cstmt = conn.prepareCall(sql);
  cstmt.registerOutParameter(1,<b>OracleTypes.NUMERIC</b>) ;
      cstmt.setArray( 2, array );
      cstmt.execute();
                                                                                                    
      <b>String ret = cstmt.getObject(1).toString();</b>
      System.out.println( <b>ret</b> );
   }
   static public void main(String args[])
   {
      try
      {
         DriverManager.registerDriver(new OracleDriver());
         conn = 
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:<b>orcl","scott","tiger"</b>);
         conn.setAutoCommit(false);
         process();
         conn.close();
      } catch (Exception e)
      {
         e.printStackTrace();
      }
   }
                                                                                                    
                      
}

============================================================
p.s. Well, it seems code tag ignored other tags embedded within.

STORED PROCEDURE

vineela, February 07, 2008 - 2:36 am UTC

1 create or replace procedure p1 (eno number,
2 deptno1 out number,
3 ename1 in out varchar2)
4 as
5 begin
6 select deptno,ename into deptno1,ename1 from emp where empno=eno;
7* end;
QL> /

rocedure created.

QL>
QL>
QL>
QL> exec p1 (123,:a,:b);
EGIN p1 (123,:a,:b); END;


RROR at line 1:
RA-06502: PL/SQL: numeric or value error
RA-06512: at "SCOTT.P1", line 6
RA-06512: at line 1


I am getting the above err.Plz suggest a appropriate solution
Tom Kyte
February 07, 2008 - 8:32 am UTC

appropriate solution:

do not run: exec p1 (123,:a,:b); and you will not get the error.

I mean - come on - what do you expect here?

Sounds like the binds you defined are not appropriate for what you are doing, but you give us NOTHING TO GO ON.

I would have expected no-data-found using emp - since there is no empno 123.

so read through this and see what everyone would have expected to have happen (no_data_found), then watch as we create a row that will match and get your numeric or value error (because you probably defined b as "varchar2" which is "varchar2(1)" and not big enough) and then see the final solution which would be to define the binds correctly (which you didn't share with us, so we are totally GUESSING)


ops$tkyte%ORA10GR2> create table emp as select * from scott.emp;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p1 (eno number,
  2                                     deptno1 out number,
  3                                     ename1 in out varchar2)
  4  as
  5  begin
  6  select deptno,ename into deptno1,ename1 from emp where empno=eno;
  7  end;
  8  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable a number
ops$tkyte%ORA10GR2> variable b varchar2
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec p1( 123, :a, :b )
BEGIN p1( 123, :a, :b ); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "OPS$TKYTE.P1", line 6
ORA-06512: at line 1


ops$tkyte%ORA10GR2> insert into emp (empno,deptno,ename) values ( 123, 10, 'HelloWorld' );

1 row created.

ops$tkyte%ORA10GR2> exec p1( 123, :a, :b )
BEGIN p1( 123, :a, :b ); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "OPS$TKYTE.P1", line 6
ORA-06512: at line 1


ops$tkyte%ORA10GR2> variable b varchar2(30)
ops$tkyte%ORA10GR2> exec p1( 123, :a, :b )

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print a

         A
----------
        10

ops$tkyte%ORA10GR2> print b

B
--------------------------------
HelloWorld

That´s Great Tom

Oscar Hernández, March 19, 2008 - 5:13 pm UTC


parsing array to java with no result set returned

dbssf, June 04, 2008 - 12:52 pm UTC

Hi Tom.
I parse an array of string type to pl/sql.
This is the piece of code that I use:

oracle.jdbc.OracleConnection conn = (oracle.jdbc.OracleConnection)connection;

ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor( "TY_VARCHAR32_ARRAY", conn );

ARRAY array_to_pass = new ARRAY( descriptor, conn, intArray );

oracle.jdbc.OracleCallableStatement statement = (oracle.jdbc.OracleCallableStatement)conn.prepareCall(TEST);

CHAR[] keyVals1= new oracle.sql.CHAR[pOrderIds.size()];
for(int i = 0; i < pOrderIds.size(); i++) {
keyVals1[i] = new oracle.sql.CHAR((String) pOrderIds.get(i),oracle.sql.CHAR.DEFAULT_CHARSET);
}
oracle.sql.ARRAY keyArr1 = new oracle.sql.ARRAY(descriptor, conn, keyVals1);

statement.setARRAY(1, keyArr1);
statement.registerOutParameter(2,OracleTypes.CURSOR);

statement.execute();

ResultSet rs = statement.getCursor(2);

pl/sql code is this :
PROCEDURE TEST
(
IDS IN VARCHAR32_ARRAY,
CURSOR OUT CURSOR
)
IS
BEGIN
OPEN CURSOR FOR
SELECT *
FROM table
WHERE ROWID IN (select column_value from TABLE(cast( IDS as VARCHAR32_ARRAY)));

END TEST;
At the end the result set is null and the statement.execute return always false.

Could you please take a look at this code and help me.

Thanks
Gicu
Tom Kyte
June 04, 2008 - 1:10 pm UTC

do you know how to debug simple things - like writing a message to a table or something - or just maybe returning

is
   l_data long;
begin
  for i in 1 .. ids.count
  loop
      l_data := l_data || ',' || ids(i);
  end loop;
  open l_cursor /* because using cursor as a variable name is not smart */
   for select l_data from dual; -- let's see what we get here!
end;



incrementally build up the code - testing at each stage, to see where "things might, just might be going "wrong".


@dbssf re: no result set returned

Stew Ashton, June 07, 2008 - 3:52 am UTC


In addition to Tom's advice, please make sure you have all the necessary .jar files in your CLASSPATH. For objects and collections, outside of the US we need orai18n.jar (or for older versions nls_charset12.zip).
http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/global.htm#insertedID1
Without this .jar, you are probably passing garbage in your array. Tom's test would confirm this.

CHR(13) TRIM() from ARRAY?

A reader, July 17, 2008 - 9:32 am UTC

Tom,
I am outputting an array to a flat file that will be picked up as a comma delimited string of values. The array is inserting a CHR(13) to the records. I *tried* wrapping the array in a TRIM() function but get PLS-00306: wrong number or types of arguments in call to 'TRIM'. Here's a snippet of my effort. Do you have a method for doing this?

FOR i IN 1 .. p_group_id.COUNT
LOOP
UTL_FILE.put_line (dot_lst, TRIM (p_group_id (i), CHR (13)) || ',');
END LOOP;
Tom Kyte
July 17, 2008 - 11:51 am UTC


ops$tkyte%ORA10GR2> declare
  2          type array is table of varchar2(30);
  3          l_data array := array( 'hello' || chr(13), 'world'||chr(13) );
  4  begin
  5        FOR i IN 1 .. l_data.COUNT
  6        LOOP
  7           dbms_output.put_line ( TRIM (trailing chr(13) from l_data (i) ) || ',');
  8        END LOOP;
  9  end;
 10  /
hello,
world,

PL/SQL procedure successfully completed.


Thanks!

A reader, July 17, 2008 - 2:01 pm UTC

Thanks, that's perfect.

java

A reader, July 19, 2008 - 12:40 am UTC

excellent

SQL Collection Slow

A reader, July 21, 2008 - 11:22 am UTC

I have a situation that I can't figure out and think may be a bug. I have a SQL Collection Type that I am using to populate from .net. From there I Cast the array as a table and attempt to pull from a remote DB. This method works for a large table with several joins. This specific query works when I hard code (not bound) the data to the query. But when I try to bind the array using Table(cast(. . . it takes over 1 minute just to do an insert to the table). I would provide scripts to build the structure but it won't help since it WORKS most of the time, just not with this specific scenario.

Here is teh query with the hard code value (this runs in milliseconds):
EXECUTE IMMEDIATE ' INSERT INTO TA_EDI_PROVIDER_TEMP'
|| ' SELECT prpr_name "ProviderName",'
|| ' prad_addr1 "Address", prad_addr2 "Address2", prad_city "City",'
|| ' prad_state "State", prad_zip "Zipcode", prad_ctry_cd "Country",'
|| ' mctn_id "EINNumber"'
|| ' FROM facetsdb.cmc_prpr_prov@'
|| v_dlink
|| ' pr,'
|| ' facetsdb.cmc_prad_address@'
|| v_dlink
|| ' prad'
|| ' WHERE pr.prpr_id IN ''200003080865'' ' --(select * from TABLE( CAST (:x AS edi_prv_type)))'
|| ' AND prad.prad_id = pr.prad_id'
|| ' AND prad.prad_type = pr.prad_type_check';
--USING edi_prv;

Here is the same query with the bound array. This is the one that takes several minutes to run, with only one value. Again, I use this method all over the place with much larger data. So, I don't get it and no resource here has any clue why this is happening.

EXECUTE IMMEDIATE ' INSERT INTO TA_EDI_PROVIDER_TEMP'
|| ' SELECT prpr_name "ProviderName",'
|| ' prad_addr1 "Address", prad_addr2 "Address2", prad_city "City",'
|| ' prad_state "State", prad_zip "Zipcode", prad_ctry_cd "Country",'
|| ' mctn_id "EINNumber"'
|| ' FROM facetsdb.cmc_prpr_prov@'
|| v_dlink
|| ' pr,'
|| ' facetsdb.cmc_prad_address@'
|| v_dlink
|| ' prad'
|| ' WHERE pr.prpr_id IN (select * from TABLE( CAST (:x AS edi_prv_type)))'
|| ' AND prad.prad_id = pr.prad_id'
|| ' AND prad.prad_type = pr.prad_type_check';
USING edi_prv;
Tom Kyte
July 22, 2008 - 10:49 am UTC

it will all depend on the plan, 100%

it could be that it decided to full scan the collection over and over - having to go back to the originating site for each occurrence.

or that it decided "bring back every row to compare to this set" (eg: the collection is not getting pushed over the link - highly likely - no index on remote site being used)

got plan?

passing arrays into pl/sql stored procedures

Reader, July 22, 2008 - 10:52 am UTC

Plan for SQL

A reader, July 22, 2008 - 5:21 pm UTC

Because I had to get some results, I changed the method I was using for the original query. However, this query is taking equally as long. To the reviewer who posted the link ,thank you for doing so, however I was "firewalled". I notice on the plan that it is doing the "REMOTE" twice. Not having had to look at the plan for a link previously, I'm fairly uncertain as to what the PICKLER FETCH and REMOTE's are telling me. I see a rather high cost, but am unsure what I can do to change that.

INSERT INTO ta_edi_subscriber_temp
SELECT gr.grgr_name "GroupName", sb.sbsb_id "sbr_ID",
sb.sbsb_first_name "sbr_FirstName",
sb.sbsb_last_name "sbr_LastName", sb.sbsb_mid_init "sbr_MiddleName",
sbad.sbad_addr1 "Address", sbad.sbad_addr2 "Address2",
sbad.sbad_city "City", sbad.sbad_state "State",
sbad.sbad_zip "Zipcode", sbad.sbad_ctry_cd "Country",
me.meme_ssn "SSN", me.meme_birth_dt "Birthdate",
gr.grgr_id "GroupID", me.meme_first_name "FirstName",
me.meme_last_name "LastName", me.meme_mid_init "MiddleName",
me.meme_sex "Gender", me.meme_rel "Relationship",
sb.mcbr_ck "AccountNumber"
FROM facetsdb.cmc_sbsb_subsc@testauto_devh_ftest2_link sb,
facetsdb.cmc_sbad_addr@testauto_devh_ftest2_link sbad,
facetsdb.cmc_meme_member@testauto_devh_ftest2_link me,
facetsdb.cmc_grgr_group@testauto_devh_ftest2_link gr
WHERE sbad.sbsb_ck = sb.sbsb_ck
AND me.sbsb_ck = sb.sbsb_ck
AND me.grgr_ck = gr.grgr_ck
AND sb.grgr_ck = gr.grgr_ck
AND sbad.sbad_type = :a
AND EXISTS (SELECT NULL
FROM TABLE (CAST (:x AS edi_sb_type))
WHERE sbsb_id = sb.sbsb_id AND meme_sfx = me.meme_sfx)

Plan
INSERT STATEMENT ALL_ROWSCost: 20,406
6 FILTER
3 HASH JOIN Cost: 3,558 Bytes: 21,415,940 Cardinality: 104,468
1 REMOTE REMOTE Cost: 5 Bytes: 13,662 Cardinality: 297
2 REMOTE REMOTE Cost: 306 Bytes: 8,775,312 Cardinality: 104,468
5 FILTER
4 COLLECTION ITERATOR PICKLER FETCH



Plan for SQL (cont'd)

A reader, July 22, 2008 - 5:33 pm UTC

Here are the creation scripts for the objects.

CREATE OR REPLACE
TYPE TESTAUTO_O.SBSB_SFX_OBJ AS OBJECT (SBSB_ID VARCHAR2(9), SFX VARCHAR2(2))
/

CREATE OR REPLACE
TYPE TESTAUTO_O.EDI_SB_TYPE AS TABLE OF SBSB_SFX_OBJ;
/

Plan for SQL (cont'd)

A reader, July 23, 2008 - 11:59 am UTC

Pardon me for the add on's. but, the more I investigate, the more it seems I'm doomed to waiting and this is a network issue as opposed to a SQL issue (the query is very basic). Even though everything is bound, the remote DB doesn't seem to want to play nice. Everything I've read through your website about the subject points to looking at stats, but I couldn't find a resolution. This query is waiting and waiting and waiting. Each row is having network issues. I'm not very well educated on how Oracle executes queries over db links, but it seems to me I dont' have a way out. Is that true?

ROWNUM EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO
1 log file sync 6 0 3 0.43 1 25681
2 db file sequential read 1 0 1 0.74 1 7413
3 direct path read 2574 0 272 0.11 12 2722112
4 direct path write 2672 0 244 0.09 16 2439356
5 TCP Socket (KGAS) 74 0 1 0.02 0 13410
6 single-task message 1 0 24 24.04 24 240432
7 SQL*Net message to client 22 0 0 0 0 27
8 SQL*Net message to dblink 10343 0 3 0 0 25403
9 SQL*Net more data to dblink 1 0 0 0 0 31
10 SQL*Net message from client 22 0 17214 782.46 16711 172141617
11 SQL*Net message from dblink 10342 0 55098 5.33 116 550979341
12 SQL*Net more data from dblink 24038 0 141 0.01 3 1405321

Plan for SQL (cont'd ) - SOLVED

A reader, July 24, 2008 - 11:58 am UTC

Going on the assumption that it may be environmental, I decided to run this against multiple equivalent databases. Turns out, the exact same query will run in milliseconds in one environment and several minutes in another. Therefore, the hypothesis seems to be proven that the issue is with the remote database and the network rather than the query itself.

thanks for looking!

Remote Array Insert Performance

A reader, October 06, 2008 - 5:55 pm UTC

I posted a similar question on another thread, tried to find it, and could not. I am having a performance issue that I don't understand. I run nearly identical queries calling remote db's, one uses an array for processing, the other a simple string. One returns in milliseconds the other in 10 minutes. The queries and plans below.

----Slow Query-----
INSERT INTO ta_edi_provider_temp
SELECT prpr_name "ProviderName",
prad_addr1 "Address",
prad_addr2 "Address2",
prad_city "City",
prad_state "State",
prad_zip "Zipcode",
prad_ctry_cd "Country",
mctn_id "EINNumber",
prpr_id "ProviderID"
FROM facetsdb.cmc_prpr_prov@testauto_devh_ftest7_link pr,
facetsdb.cmc_prad_address@testauto_devh_ftest7_link prad
WHERE EXISTS (SELECT NULL
FROM TABLE (CAST(:b1 AS edi_prv_type))
WHERE provider_id = pr.prpr_id)
AND prad.prad_id = pr.prad_id
AND prad.prad_type = pr.prad_type_check;

---------Plan------------
INSERT STATEMENT ALL_ROWSCost : 58,649,344 Bytes : 134 Cardinality : 1
6 FILTER EXISTS (SELECT 0 FROM TABLE() "KOKBF$" WHERE :V_PROVIDER_ID=:B1)
3 HASH JOIN "PRAD"."PRAD_ID"="PR"."PRAD_ID" AND "PRAD"."PRAD_TYPE"="PR"."PRAD_TYPE_CHECK"Cost : 93,090 Bytes : 325,900,596 Cardinality : 2,432,094
1 REMOTE SERIAL_FROM_REMOTE CMC_PRPR_PROV TESTAUTO_DEVH_FTEST7_LINKSELECT /*+ OPAQUE_TRANSFORM */ "PRPR_ID","PRPR_NAME","PRAD_ID","PRAD_TYPE_CHECK","MCTN_ID" FROM "FACETSDB"."CMC_PRPR_PROV" "SYS_ALIAS_1"Cost : 15,638 Bytes : 131,333,076 Cardinality : 2,432,094
2 REMOTE SERIAL_FROM_REMOTE CMC_PRAD_ADDRESS TESTAUTO_DEVH_FTEST7_LINKSELECT /*+ OPAQUE_TRANSFORM */ "PRAD_ID","PRAD_TYPE","PRAD_ADDR1","PRAD_ADDR2","PRAD_CITY","PRAD_STATE","PRAD_ZIP","PRAD_CTRY_CD" FROM "FACETSDB"."CMC_PRAD_ADDRESS" "PRAD"Cost : 22,312 Bytes : 382,931,840 Cardinality : 4,786,648
5 FILTER :V_PROVIDER_ID=:B1
4 COLLECTION ITERATOR PICKLER FETCH

-----Fast Query------
INSERT INTO TA_EDI_PROVIDER_TEMP
SELECT prpr_name "ProviderName",
prad_addr1 "Address",
prad_addr2 "Address2",
prad_city "City",
prad_state "State",
prad_zip "Zipcode",
prad_ctry_cd "Country",
mctn_id "EINNumber",
prpr_id "ProviderID"
FROM facetsdb.cmc_prpr_prov@testauto_devh_ftest7_link pr,
facetsdb.cmc_prad_address@testauto_devh_ftest7_link prad
WHERE prpr_id IN ('200003422449', '200003422450', '200003422451', '200003422452', '200003422453', '200003422454')
AND prad.prad_id = pr.prad_id
AND prad.prad_type = pr.prad_type_check


---------Plan------------
INSERT STATEMENT ALL_ROWSCost : 0
1 REMOTE SERIAL_FROM_REMOTE TESTAUTO_DEVH_FTEST7_LINKEXPLAIN PLAN SET STATEMENT_ID='Execution Plan' INTO "TESTAUTO_P"."QUEST_SL_EXPLAIN1"@! FOR SELECT "A2"."PRPR_NAME","A1"."PRAD_ADDR1","A1"."PRAD_ADDR2","A1"."PRAD_CITY","A1"."PRAD_STATE","A1"."PRAD_ZIP","A1"."PRAD_CTRY_CD","A2"."MCTN_ID","A2"."PRPR_ID" FROM "FACETSDB"."CMC_PRPR_PROV" "A2","FACETSDB"."CMC_PRAD_ADDRESS" "A1" WHERE ("A2"."PRPR_ID"='200003422449' OR "A2"."PRPR_ID"='200003422450' OR "A2"."PRPR_ID"='200003422451' OR "A2"."PRPR_ID"='200003422452' OR "A2"."PRPR_ID"='200003422453' OR "A2"."PRPR_ID"='200003422454') AND "A1"."PRAD_ID"="A2"."PRAD_ID" AND "A1"."PRAD_TYPE"="A2"."PRAD_TYPE_CHECK"

Tom Kyte
October 07, 2008 - 1:11 pm UTC

I do not see any 'array' processing here. Array processing is when you bulk fetch or bulk bind (eg: insert 100 rows from a client into a table at a time, instead of calling insert 100 times)

what you have is the difference between two entirely different queries.

where exists
where in

why are you even comparing them? at least compare similar syntax.

you will then be comparing:

where column in ( some UNKNOWN SET )

versus

where column in ( 1, 2, 3, 4, 5 ) -- we know what the values are.


If this is a batch statement executed infrequently (once a week, once a day, once a month) - using literals can make sense, gives the optimizer all of the details.

Undering the underlying working of Oracle.sql.Array

Farhan, April 03, 2009 - 7:42 pm UTC

Hi Tom,

My question is rather very trivial/basic, and that is with regards to internal working of Oracle.sql.Array when being used e.g. to return an array of Strings from java-stored-procedure to pl-sql stored-procedure (i.e the caller).

All i want to know is why the construction of Oracle.sql.Array class requires a connection-object as one of the parameters ?. I understand ArrayDescriptor requiring a connection object and that is to fetch the meta-data of the type of array being constructed from the database (and hence the database-hit cannot be avoided) but why for Oracle.sql.Array, isn't the information available via ArrayDescriptor enough to construct the Array Object ? or is their a missing link/concept.

Thanks in advance...

Can assosiative arrays be passed to stored procedure now

srp, May 21, 2009 - 6:18 pm UTC

Tom,
Thanks for this solution. This is very useful. I have asked this in relate to some other question but I just want to ask again. You have mentioned in one of the follow-ups about passing associative arrays to stored procedure

"In java the natural way to do this
will be via a hash table or something similar. In plsql -- a plsql table type.

You cannot have a SQL collection that is indexed by a varchar2 -- there is no corresponding SQL
type here."
You can create the collection indexed by varchar2 now. So can we pass associative arrays now?
Tom Kyte
May 23, 2009 - 10:16 pm UTC

from plsql to plsql - yes.

from anything else to plsql - no, that would have to be done manually - in a wrapping layer of your construction.

java.lang.ClassCastException: org.jboss.resource.adapter.jdbc.WrappedCallableStatement

shashi wagh, January 15, 2010 - 4:49 am UTC

Dear Tom,


I have tried the same with Hibernate and I got the following exception

java.lang.ClassCastException: org.jboss.resource.adapter.jdbc.WrappedCallableStatement;

Here is my code
Connection conn = getSession().connection();

int intArray[] =
{
1, 2, 3, 4, 5, 6
};

OracleCallableStatement ocs = (OracleCallableStatement)conn
.prepareCall( "begin sh_give_me_an_array(:x); end;" );




Please suggest something.

Thanks and Regards,
Shashi


Tom Kyte
January 18, 2010 - 5:20 pm UTC

I know nothing about linking things together in hibernate.

java.lang.ClassCastException: org.jboss.resource.adapter.jdbc.WrappedConnection

shashi wagh, January 19, 2010 - 5:38 am UTC

I have also tried connection from Data Source then also It gives following error

java.lang.ClassCastException: org.jboss.resource.adapter.jdbc.WrappedConnection

Following is my code

Connection con = getDataSource().getConnection();// getting the data source and connection

OracleCallableStatement ocs = (OracleCallableStatement)con.prepareCall( "begin sh_give_me_an_array(:x); end;" );



Please Help.............

How do I convert the con object to what I will get from the JDBC Connection

I have tried the following then also it is not worked

WrappedConnection connection = (WrappedConnection)getDataSource().getConnection();

Connection con = connection.getUnderlyingConnection();

OracleCallableStatement ocs = (OracleCallableStatement)con.prepareCall( "begin sh_give_me_an_array(:x); end;" );


But not worked.........

Passing a string to function for IN

Mah, June 15, 2011 - 2:46 pm UTC

Hi,
I was reading this topic to get the solution so I can pass a string as parameter to a function like,

select myfun('A','B') from dual

in function I will use this string as,

create function myfun (mystr in varchar2)
select * from mytable where grade IN (mystr);

is this possible if so then how as this is was I am getting error. thanks

Thanks... this article has literally saved me!!!

Aditya Ghosh, June 30, 2011 - 8:07 am UTC

I had to create a couple of procedures which accepted and sent out string arrays in PL/SQL; then I had to create PL/SQL stored procedure based Web Services on these procedures; it was a nightmare understanding why JDeveloper was unable to bind to PL/SQL Index By Tables(Associative Arrays), which was the collection type I was using. I could never have guessed that creating the same table type as an SQL type would so elegantly solve the problem; the Web Service generation wizard automatically accepted it as a new type, and the rest was cakewalk. Thanks a zillion times!!! This has really made my day after hours of desperation and frustrated effort!!!

Naive Question

A reader, September 14, 2011 - 5:42 am UTC

Newbie in JDBC programming.
Following code works but inserts NULLS into DB when "string" array is passed.
What is my mistake?
Please Help.

create type str_array as table of varchar2(10);
/

create table emp2(name varchar2(10));


create or replace procedure give_me_an_array(p_array in str_array) as
begin
for i in 1.. p_array.count
loop
insert into emp2 values(p_array(i));
end loop;
end;
/

stringarray.java
================
import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class stringarray
{
public static void main(String[] args) throws SQLException
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
String url = "jdbc:oracle:thin:@//x.x.x.x:1521/mydb";
Connection conn = DriverManager.getConnection(url,"USER1","PASS1");
//bad practice, just for testing
conn.setAutoCommit(true);


String strArray[] = {"1","2","3","4","5","6"};

ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("STR_ARRAY", conn);

ARRAY array_to_pass = new ARRAY(descriptor, conn, strArray);

OraclePreparedStatement ps=(OraclePreparedStatement)conn.prepareStatement("begin give_me_an_array(:x); end;");

ps.setARRAY(1, array_to_pass);

ps.execute();

}
}



Tom Kyte
September 15, 2011 - 6:02 am UTC

[tkyte@localhost ~]$ cat stringarray.java
import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class stringarray
{
public static void main(String[] args) throws SQLException
{
    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
    String url = "jdbc:oracle:thin:@//localhost.localdomain:1521/ora11gr2.localdomain";
    Connection conn = DriverManager.getConnection(url,"ops$tkyte","foobar");
//bad practice, just for testing
    conn.setAutoCommit(true); 


 String strArray[] = {"1","2","3","4","5","6"};

 ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("STR_ARRAY", conn);

 ARRAY array_to_pass = new ARRAY(descriptor, conn, strArray);

 PreparedStatement ps=(PreparedStatement)conn.prepareStatement("begin give_me_an_array(:x); end;");

 ps.setArray(1, array_to_pass);

 ps.execute();

}
}



that code worked for me - you have some typos in yours.

Naive Question: Followup

A reader, September 14, 2011 - 11:32 am UTC

Never Mind.
I had to include nls_charset12.jar in CLASSPATH.
Stew Ashton from Paris' note tipped me.


Calling an Oracle Procedure from Java

Aleahim D, November 29, 2011 - 4:26 am UTC

I need to call a procedure with IN parameters from Java. The problem is here...the parameters are taken from a csv file.

Please help me,
Aleahim
Tom Kyte
November 29, 2011 - 11:40 am UTC

not a problem.

read the csv in your program, parse the csv in your program.

now the csv data is in java variables.

This has nothing to do with the database - this is all about "processing a file in java"

Passing VARCHAR ARRAY to procedure

Rui Rocha, July 04, 2012 - 12:56 pm UTC

Hi Tom,
I've with this for a couple of days but I havent achived nothig yet.

I have these two types:

CREATE OR REPLACE TYPE REPORT_ELEMENTS as TABLE OF VARCHAR2(100);
/

CREATE OR REPLACE TYPE INT_ELEMENTS as TABLE OF NUMBER;
/


And these procedures:



FUNCTION test(vals REPORT_ELEMENTS) RETURN Reporting.VarcharArray PIPELINED IS

BEGIN
FOR I IN 1..vals.Count() LOOP
PIPE ROW(i || ' >'||vals(i) ||'<');

END LOOP;

END;


FUNCTION testInt(vals INT_ELEMENTS) RETURN Reporting.VarcharArray PIPELINED IS

BEGIN

FOR I IN 1..vals.Count() LOOP
PIPE ROW(i || ' >'||vals(i) ||'<');

END LOOP;

END;


I am using jasperreports. I want to pass from a report an array to a oracle pipelined function. So I have created a helper method that creates a oracle.sql.Array and sends it as a parameter to a pipelined function. I'm succeeding when I use the Number array, but unfortunately not when I pass the Varchar array, and this is the one I want. The function pipes the exact number of lines of the array but, when it is a varchar array, each index has a null value. Why doesn't it work with a varchar array? There's another thing that has intrigued me ARRAY class has methods such as getIntArray(), getShortArray(), getDoubletArray().... but hasn't a getStringArray()....???
Please give me a light.....



This is My method:

public static Object doSomething() throws Exception{

String url = "jdbc:oracle:thin:scatex/scatex@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = EFADB)))";
java.sql.Connection conn = DriverManager.getConnection(url, "scatex", "scatex");

//The function is the same, either has the numeric instruction set or the varchar instruction set
//The function is the same, either has the numeric instruction set or the varchar instruction set
//The function is the same, either has the numeric instruction set or the varchar instruction set

// numeric instruction set
int[] intElems = new int[]{1,2,3,4,5};
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("INT_ELEMENTS",conn);
ARRAY vals = new ARRAY(desc, conn, intElems);
BigDecimal[] elms = (BigDecimal[])vals.getArray();
System.out.println(vals.dump());


// varchar instruction set
String[] strElems = new String[]{"1","2","3","4","5"};
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("REPORT_ELEMENTS",conn);
ARRAY vals = new ARRAY(desc, conn, strElems);
String[] strElms = (String[])vals.getArray();
System.out.println(strElms.dump());;


return vals;

}


this is the dump of the varchar array:
name = SCATEX.REPORT_ELEMENTS
max length = 0
length = 5
element[0] = ???
element[1] = ???
element[2] = ???
element[3] = ???
element[4] = ???



Thanks in advance,
Rui Rocha
Tom Kyte
July 05, 2012 - 7:35 am UTC

please provide the SMALLEST possible 100% complete java routine - skip the numbers bit altogether (it is working, we don't need to confuse ourselves with it).

I don't see your java calling plsql or sql at all here.

Passing VARCHAR ARRAY to procedure

Rui Rocha, July 05, 2012 - 8:51 am UTC

This is the smallest routine:

public static Object doSomething() throws Exception{

String[] elements = {"elemento 1 ","elemento 2 " };
String url = "jdbc:oracle:thin:scatex/scatex@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = EFADB)))";

String username = "scatex";
String password = "scatex";
java.sql.Connection connection = DriverManager.getConnection(url, username, password);
OracleConnection oraConn = connection.unwrap(OracleConnection.class);

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("REPORT_ELEMENTS",oraConn);
ARRAY vals = new ARRAY(desc, oraConn, elements);

System.out.println(vals.dump());

return vals;

}


I don't want to call a procedure, just want to return a valid oracle.sql.ARRAY, that is going to be used by another framework as a function parameter. In the previous example the ARRAY with the strings isn't valid. I really don't understand why. If I migrate all the code as, I have stated in the previous post, no a number ARRAY everything works fine. Another thing that puzzled me was that ARRAY class onlçy has implementations to get numeric array values: getIntArray(), getShortArray() - and hasn't a getStringArray(). Is there somekind of missed feature????
Anyway, after some days digged into this, I have created a work arround, that I'd like to post for anyone who goes throught the same problem. Insted of creating an array of a varchar i have created an array of a plsql object. HEre are some snipets (sorry about the names used:



CREATE OR REPLACE TYPE XPTO as OBJECT (
NAME NUMBER,
ATTRIBUTE1 NUMBER,
ATTRIBUTE2 NUMBER
);
/

CREATE OR REPLACE TYPE TB_T_TYPE IS TABLE OF XPTO;
/



FUNCTION testObj(vals TB_T_TYPE) RETURN Reporting.VarcharArray PIPELINED IS
BEGIN

FOR I IN 1..vals.Count() LOOP
PIPE ROW(i || ' >'||vals(i).name ||'<');

END LOOP;

END;

public static Object doSomething() throws Exception{

String url = "jdbc:oracle:thin:scatex/scatex@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = EFADB)))";
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
java.sql.Connection conn = DriverManager.getConnection(url, "scatex", "scatex");

ArrayList<TableTest> records = new ArrayList<TableTest>();
records.add(new TableTest("1","2","3"));
records.add(new TableTest("4","5","6"));
records.add(new TableTest("7","8","9"));

int iSize = records.size();
Object[] arrObj =null;
Object[][] recObj =null;

StructDescriptor structDescriptor = StructDescriptor.createDescriptor("XPTO", conn);
ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("TB_T_TYPE", conn);

arrObj = new Object[iSize];
recObj = new Object[iSize][3];
//Structuring obj and arrays
for (int j = 0; j < iSize ;j++){
TableTest ob= records.get(j);
recObj[j][0]=ob.getName();
recObj[j][1]=ob.getAttribute1();
recObj[j][2]=ob.getAttribute2();

arrObj[j] = new STRUCT(structDescriptor,conn, recObj[j]);
}
ARRAY vals = new ARRAY(arrayDescriptor, conn, arrObj);

System.out.println( vals.dump().toString().getBytes());

return vals;

}


public class TableTest {

private String name;
private String attribute1;
private String attribute2;

public TableTest(String name, String attribute1, String attribute2) {
super();
this.name = name;
this.attribute1 = attribute1;
this.attribute2 = attribute2;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getAttribute1() {
return attribute1;
}

public void setAttribute1(String attribute1) {
this.attribute1 = attribute1;
}

public String getAttribute2() {
return attribute2;
}

public void setAttribute2(String attribute2) {
this.attribute2 = attribute2;
}




}



I still would like to know what was wrong in my first approach wich I still think is the right one

Thanks,
Rui Rocha


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