How to pass java objects to PL/SQL procedures?

[Nithya Natarajan]

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

[Beksy Kurian]

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

[Nithya Natarajan]

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

[Beksy Kurian]

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

[Nithya Natarajan]

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

[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

[Nithya Natarajan]

Thanks so much Beksy!
That helped a lot.

[bobby, morkos]

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

[Beksy Kurian]

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

[Nithya Natarajan]

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;

[Beksy Kurian]

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

[Glenn Prialde]

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("[e-mail]");
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 ]

[Sujith Pesala]

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...

[barbe cuee]

I would like to pass the plsql object type pass to Java. Java will get this object and after modification, I want to pass back the same object to plsql. Main program will be calling from plsql to Java and the expecting result return from java is to same object at plsql. May I know if there is anyway to ? Following is the code that I'm testing it.

PL/SQL object Type
------------------------

CREATE OR REPLACE TYPE Address AS OBJECT
( street VARCHAR2(30),
  city VARCHAR2(20)
);

PL/SQL Package calling java program

create or replace package body TYPE_ADD_TEST is
FUNCTION Test_ADD(in_address IN address
               ) RETURN address IS
LANGUAGE JAVA
NAME 'TestPrg.TestAddress(java.sql.Struct
                  ) return java.sql.Struct';
end TYPE_ADD_TEST;

Java code
------------

create or replace and compile java source named testprg as
import java.util.*;
import java.io.*;
import java.sql.*;

public class TestPrg{

   public static String TestAddress(Struct TestAddress) {

       Address testAdd = (Address) TestAddress;
       // Address testAdd = new Address("s","c");
       return testAdd.street;
   }

}

Java Object Class
-----------------------

create or replace and compile java source named address as
import java.util.*;
import java.io.*;

public class Address {

   public String street ;
   public String city;  
   public Address() {}
   public Address(String a, String  b) {
       street = "java street";
       city = "java city";
   }
}

Thank you very much