How to pass java objects to PL/SQL procedures?

Hi,
Could anyone please explain me with an example of how to pass java objects to a PL/SQL stored procedure?
Thanks in Advance for the help!
Nithya

Nitya,
This is a smal example.
Connection conn;
CallableStatement cstmt=conn.prepareCall("call customer_info.update_password(?,?); end;");
cstmt.setString(1,password);
cstmt.setString(2,user);
cstmt.execute();
customer_info is the package name and update_password is the procedure name.
Regards
Beksy

Thanks Beksy!
But can you also tell me from PL/SQL side of what the datatype to be used for an Object that's passed?
Thanks
Nithya

procedure update_password(v_pass IN VARCHAR2,v_user IN varchar2) IS
BEGIN
update user_ref
set password=v_pass,
password_change_date =sysdate
where userid=v_user;
commit;
END;
Hope it helps
Beksy

I think I'm not explaning properly. Sorry about that.
If a PL/SQL procedure is called with the parameter as a Java object itself, for example,
update_password(UserObj),
where UserObj is the User object, then how should a PL/SQL procedure look like. Does PL/SQL have supporting datatype for a Java Object?
Thanks Again!
Nithya

I overlooked the object part.
I do not have a tested example. The following is a sample code I saw in one of the Oracle sites.
in PL/SQL------
create or replace type rectype as object(col1 varchar2(10),col2 varchar2(10));
/
create or replace package ioStruct as
procedure testproc(iorec in out rectype,orec out rectype);
end ioStruct;
/

create or replace package body ioStruct as

procedure testproc(iorec in out rectype,orec out rectype) is
begin
orec := iorec;
iorec.col1 := orec.col2;
iorec.col2 := orec.col1;
end testproc;
end ioStruct;
/

and in java----
{
// First declare the object arrays that will store the data.
Object [] p1obj = {"First","Second"};
Object [] p2obj = {};
// Now Declare a descriptor to associate the host object type with the
// record type in the database.
StructDescriptor desc1=StructDescriptor.createDescriptor("RECTYPE",conn);
// Now create the STRUCT objects to associate the host objects
// with the database records.
STRUCT p1struct = new STRUCT(desc1,conn,p1obj);
STRUCT p2struct;
// Declare the callable statement.
// This has to be of type OracleCallableStatement to use:
// setOracleObject(
// and
// registerOutParameter(position,type,oracletype)
OracleCallableStatement ocs =
(OracleCallableStatement)conn.prepareCall("{call iostruct.testproc(?,?)}");
// The first parameter is in out so we have to use setOracleObject to
// pass it to the statement.
ocs.setOracleObject(1,p1struct);
// The first parameter is in out so we have to Register the parameter as well.
// Note the reuse of the TYPE.
ocs.registerOutParameter(1,OracleTypes.STRUCT,"RECTYPE");
// The second parameter is out so that has to be registered too.
// Note the re use of the TYPE.
ocs.registerOutParameter(2,OracleTypes.STRUCT,"RECTYPE");
// Execute the procedure.
ocs.execute();
// Associate the returned arrays with the ARRAY objects.
p1struct = ocs.getSTRUCT(1);
p2struct = ocs.getSTRUCT(2);
// Get the data back into the data arrays
p1obj = p1struct.getAttributes();
p2obj = p2struct.getAttributes();
// Show the results:
System.out.println("First Object is now "+p1obj[0]+" and "+p1obj[1]);
System.out.println("Second Object is now "+p2obj[0]+" and "+p2obj[1]);
}
Regards
Beksy

Thanks so much Beksy!
That helped a lot.

Hi I want to send an object array to my pl/sql and return an object array to my java program. Is it possible. Thanks.

Originally posted by Beksy Kurian:
I overlooked the object part.
I do not have a tested example. The following is a sample code I saw in one of the Oracle sites.
in PL/SQL------
create or replace type rectype as object(col1 varchar2(10),col2 varchar2(10));
/
create or replace package ioStruct as
procedure testproc(iorec in out rectype,orec out rectype);
end ioStruct;
/

create or replace package body ioStruct as

procedure testproc(iorec in out rectype,orec out rectype) is
begin
orec := iorec;
iorec.col1 := orec.col2;
iorec.col2 := orec.col1;
end testproc;
end ioStruct;
/

and in java----
{
// First declare the object arrays that will store the data.
Object [] p1obj = {"First","Second"};
Object [] p2obj = {};
// Now Declare a descriptor to associate the host object type with the
// record type in the database.
StructDescriptor desc1=StructDescriptor.createDescriptor("RECTYPE",conn);
// Now create the STRUCT objects to associate the host objects
// with the database records.
STRUCT p1struct = new STRUCT(desc1,conn,p1obj);
STRUCT p2struct;
// Declare the callable statement.
// This has to be of type OracleCallableStatement to use:
// setOracleObject(
// and
// registerOutParameter(position,type,oracletype)
OracleCallableStatement ocs =
(OracleCallableStatement)conn.prepareCall("{call iostruct.testproc(?,?)}");
// The first parameter is in out so we have to use setOracleObject to
// pass it to the statement.
ocs.setOracleObject(1,p1struct);
// The first parameter is in out so we have to Register the parameter as well.
// Note the reuse of the TYPE.
ocs.registerOutParameter(1,OracleTypes.STRUCT,"RECTYPE");
// The second parameter is out so that has to be registered too.
// Note the re use of the TYPE.
ocs.registerOutParameter(2,OracleTypes.STRUCT,"RECTYPE");
// Execute the procedure.
ocs.execute();
// Associate the returned arrays with the ARRAY objects.
p1struct = ocs.getSTRUCT(1);
p2struct = ocs.getSTRUCT(2);
// Get the data back into the data arrays
p1obj = p1struct.getAttributes();
p2obj = p2struct.getAttributes();
// Show the results:
System.out.println("First Object is now "+p1obj[0]+" and "+p1obj[1]);
System.out.println("Second Object is now "+p2obj[0]+" and "+p2obj[1]);
}
Regards
Beksy

I have answered to your 'own' thread, Bobby
Beksy

Hi Beksy,
Is it possible to insert the whole object into a table using an insert statement? for example,
INSERT INTO USER_TABLE VALUES userObj;

We can store objects in a table.
See this example:
CREATE TYPE external_person AS OBJECT (
name VARCHAR2(30),
phone VARCHAR2(20) );

CREATE TABLE external_person_table OF external_person;

INSERT INTO external_person_table VALUES (
'John Smith',
'1-800-012-0834' );
SELECT VALUE(p) FROM external_person_table p
WHERE p.name = 'John Smith';
Beksy

I managed to solve my own problem here is my solution...
/*
first create a PL/SQL object type and table type
*/
create or replace type SAMPLE_TYPE as object(
id number(34),
email varchar2(64),
info_value varchar2(255)
);
create or replace type SAMPLE_TABLE_TYPE as table of SAMPLE_TYPE;

//then in the java code
LogTrans app = new LogTrans();
int commit = 1;
Vector vector = new Vector();

Object[] attr = new Object[3];
attr[0] = (Object) new BigDecimal(8);
attr[1] = (Object) new String("TEST@TEST.COM");
attr[2] = (Object) new String("TEST DATA");

try
{
app.connect();

StructDescriptor structdesc = StructDescriptor.createDescriptor("SAMPLE_TYPE",app.con);
vector.add((Object)new STRUCT(structdesc, app.con, attr));

ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("SAMPLE_TABLE_TYPE",app.con);

Object obj_array[] = vector.toArray();
ARRAY array = new ARRAY(arraydesc,app.con,obj_array);

CallableStatement cstm = app.con.prepareCall("{ call PACKAGE.FUNCTION(?,?) }");
((OracleCallableStatement)cstm).setARRAY(1, array);
cstm.setInt(2, 1);
cstm.execute();
System.out.println("Please check database");

}
catch(Exception e)
{
System.err.println("dothis method exception: " + e.getMessage());
}
hello Beksy i think what bobby wants to ask is how to pass an array of bojects to a PL/SQL function/procedure that accepts a "SAMPLE_TABLE_TYPE" table (a table of object "SAMPLE_TYPE")...
because i also have similar problems...
thanks

Originally posted by Beksy Kurian:
I have answered to your 'own' thread, Bobby
Beksy

[ April 11, 2003: Message edited by: Glenn Prialde ]

Hi.. The post was very helpful for passing a collection of PLSQL objects..
But can anyone please let me know, how to recieve a collection of objects from the procedure and get the data..

My requirement is :

PLSQL Object

Object Box
{
Length NUMBER;
Breadth NUMBER;
}

TYPE Row is a Varray(50) of Box;

Now I am passing Row of ten boxes to a procedure and am expecting another Row with 10 different boxes.

After recieving the row, which data type I need to store them..

Please advice ..

I am able to pass the Row of Boxes to my proc but while retrieving I am facing problem..

Please help...