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?
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
March 26, 2003 - 4:02 pm UTC
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
March 26, 2003 - 6:46 pm UTC
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] );
}
}
/
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)
April 04, 2003 - 11:28 am UTC
what version?
So, April 04, 2003 - 3:00 pm UTC
Sorry - it's 9.2EE
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
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.
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 !!
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>
April 11, 2003 - 8:09 am UTC
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 ...
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
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 .....
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();
}
}
}
}
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.
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
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...???
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?
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.
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
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
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
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();
}
}
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)
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
October 19, 2005 - 12:41 pm UTC
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
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
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
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
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
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.
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?
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
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
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.
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;
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.
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.
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.
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
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
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
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
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;
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;
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"
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?
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
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
June 17, 2011 - 1:12 pm UTC
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();
}
}
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
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
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