SQL & PL/SQL

I've been searching in different forums/documents to find a solution to pass a Java object and array to procedure. I spent lot of time to get a answer to all these. Below is the solution which may help to some. In this sample code the method implemented are
1. Pass a Java object to PL/SQL
2. Get a Java object from PL/SQL
3. Pass a Java array object to PL/SQL
4. Get a Java array object from PL/SQL

Implemented code to store person(investor) data and persons preferences.


ORACLE OBJECTS DEFINED
==================
CREATE TABLE INVESTOR
(
INVESTOR_ID VARCHAR2(10 BYTE),
INVESTOR_TYPE VARCHAR2(30 BYTE),
FIRST_NAME VARCHAR2(30 BYTE),
MIDDLE_NAME VARCHAR2(30 BYTE),
LAST_NAME VARCHAR2(30 BYTE)
)
/

CREATE OR REPLACE TYPE investor_obj as object(
INVESTOR_ID VARCHAR2(10 BYTE),
INVESTOR_TYPE VARCHAR2(30 BYTE),
FIRST_NAME VARCHAR2(30 BYTE),
MIDDLE_NAME VARCHAR2(30 BYTE),
LAST_NAME VARCHAR2(30 BYTE)
)
/

CREATE TABLE INVESTOR_PREFERENCE
(
INVESTOR_ID VARCHAR2(10 BYTE),
PREFERENCE_TYPE VARCHAR2(30 BYTE),
PREFERENCE_NAME VARCHAR2(30 BYTE),
PREFERENCE_VALUE VARCHAR2(30 BYTE)
)
/

CREATE OR REPLACE TYPE investor_pref_obj as object( preference_name varchar2(30), preference_value varchar2(30));
/
CREATE OR REPLACE TYPE INVESTOR_PREF_TAB as table of investor_pref_obj;
/


ORACLE PACKAGE DEFINED
======================
CREATE OR REPLACE PACKAGE pkgInvestor IS
FUNCTION fnSetInvestorPreferences
(
inInvestorId IN investor_preference.investor_id%TYPE
,inPreferenceType IN investor_preference.preference_type%TYPE
,inInvestorPref IN investor_pref_tab
)
RETURN NUMBER;

FUNCTION fnGetInvestorPreferences
(
inInvestorId IN investor_preference.investor_id%TYPE
,inPreferenceType IN investor_preference.preference_type%TYPE
,outInvestorPref OUT investor_pref_tab
)
RETURN NUMBER;

FUNCTION fnSetInvestor
(
inInvestor IN investor_obj
)
RETURN NUMBER;

FUNCTION fnGetInvestor
(
inInvestorId IN investor.investor_id%TYPE
,outInvestor OUT investor_obj
)
RETURN NUMBER;

END pkgInvestor;
/


CREATE OR REPLACE PACKAGE BODY pkgInvestor IS

cSUCCESS CONSTANT NUMBER(1) := 0;
cFAILURE CONSTANT NUMBER(1) := 1;

FUNCTION fnSetInvestorPreferences
(
inInvestorId IN investor_preference.investor_id%TYPE
,inPreferenceType IN investor_preference.preference_type%TYPE
,inInvestorPref IN investor_pref_tab
)
RETURN NUMBER
IS

BEGIN

DELETE FROM investor_preference
WHERE investor_id = inInvestorId;

INSERT INTO investor_preference
(
investor_id
,preference_type
,preference_name
,preference_value
)
SELECT
inInvestorId
,inPreferenceType
,preference_name
,preference_value
FROM TABLE(inInvestorPref);

COMMIT;
RETURN cSUCCESS;

EXCEPTION
WHEN OTHERS THEN
RETURN cFAILURE;

END fnSetInvestorPreferences;


FUNCTION fnGetInvestorPreferences
(
inInvestorId IN investor_preference.investor_id%TYPE
,inPreferenceType IN investor_preference.preference_type%TYPE
,outInvestorPref OUT investor_pref_tab
)
RETURN NUMBER
AS
outInvestorPrefArray investor_pref_tab := investor_pref_tab();
BEGIN

SELECT investor_pref_obj(preference_name, preference_value)
BULK COLLECT INTO outInvestorPrefArray
FROM investor_preference
WHERE investor_id = inInvestorId
AND preference_type = inPreferenceType ;

outInvestorPref := outInvestorPrefArray ;

RETURN cSUCCESS;

EXCEPTION
WHEN OTHERS THEN
RETURN cFAILURE;

END fnGetInvestorPreferences;


FUNCTION fnSetInvestor
(
inInvestor IN investor_obj
)
RETURN NUMBER
AS
BEGIN

INSERT INTO investor
(
investor_id
,investor_type
,first_name
,middle_name
,last_name
) VALUES
(inInvestor.investor_id
,inInvestor.investor_type
,inInvestor.first_name
,inInvestor.middle_name
,inInvestor.last_name
);
COMMIT;

RETURN cSUCCESS;

EXCEPTION
WHEN OTHERS THEN
RETURN cFAILURE;

END fnSetInvestor;



FUNCTION fnGetInvestor
(
inInvestorId IN investor.investor_id%TYPE
,outInvestor OUT investor_obj
)
RETURN NUMBER AS

outInvestorObj investor_obj;
investorId investor.investor_id%TYPE;
investorType investor.investor_type%TYPE;
firstName investor.first_name%TYPE;
middleName investor.middle_name%TYPE;
lastName investor.last_name%TYPE;

BEGIN

SELECT
investor_id
,investor_type
,first_name
,middle_name
,last_name
INTO investorId, investorType,firstName, middleName,lastName
FROM investor
WHERE investor_id = inInvestorId;

outInvestorObj := investor_obj(investorId,investorType,firstName,middleName,lastName );

outInvestor := outInvestorObj ;

RETURN cSUCCESS;

EXCEPTION
WHEN OTHERS THEN
RETURN cFAILURE;

END fnGetInvestor;


END pkgInvestor;
/
show errors


JAVA CLASS
========

import java.io.Serializable;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;


public class Preferences implements SQLData, Serializable {

static final long serialVersionUID = 4070409649129120458L;


//private String sql_type;

private String preferenceName;
private String preferenceValue;

public Preferences(){}

public Preferences (String preferenceName ,String preferenceValue ){
this.preferenceName = preferenceName;
this.preferenceValue = preferenceValue ;
}



// constructor that takes parameters
public String getPreferenceName() {
return preferenceName;
}

public void setPreferenceName(String preferenceName) {
this.preferenceName = preferenceName;
}

public String getPreferenceValue() {
return preferenceValue;
}

public void setPreferenceValue(String preferenceValue) {
this.preferenceValue = preferenceValue;
}

// You have to implement readSQL() , writeSQL() and getSQLTypeName() methods, as shown below.
// This is where you are mapping the table’s columns to the Preferences java bean.
public void readSQL(SQLInput stream, String typeName) throws SQLException {
//this.sql_type = typeName ;
this.preferenceName = stream.readString();
this.preferenceValue = stream.readString();
}
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeString(preferenceName);
stream.writeString(preferenceValue);
}


public String getSQLTypeName()
{
return "INVESTOR_PREF_OBJ";
}

}

JAVA CLASS
========
import java.io.Serializable;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class Investor {
private String investorId;
private String investorType;
private String firstName;
private String middleName;
private String lastName;


public String getInvestorId() {
return investorId;
}

public void setInvestorId(String investorId) {
this.investorId = investorId;
}

public String getInvestorType() {
return investorType;
}

public void setInvestorType(String investorType) {
this.investorType = investorType;
}

public String getFirstName() {
return firstName;
}

public void setFirstName(String firstName) {
this.firstName = firstName;
}

public String getMiddleName() {
return middleName;
}

public void setMiddleName(String middleName) {
this.middleName = middleName;
}

public String getLastName() {
return lastName;
}

public void setLastName(String lastName) {
this.lastName = lastName;
}

// You have to implement readSQL() , writeSQL() and getSQLTypeName() methods, as shown below.
// This is where you are mapping the table’s columns to the Preferences java bean.
public void readSQL(SQLInput stream, String typeName) throws SQLException {
this.investorId = stream.readString();
this.investorType = stream.readString();
this.firstName = stream.readString();
this.middleName = stream.readString();
this.lastName = stream.readString();
}
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeString(investorId);
stream.writeString(investorType);
stream.writeString(firstName);
stream.writeString(middleName);
stream.writeString(lastName);
}

public String getSQLTypeName()
{
return "INVESTOR_OBJ";
}

}

JAVA CLASS
========
import oracle.jdbc.*;
import oracle.sql.*;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;


public class ArrayPrefDemo {

public static final String INVESTOR_FID ="000151565";
public static final String COMMS_PREF ="COMMUNICATION";

public static void main( String args[] ) throws SQLException
{

ArrayPrefDemo demo = new ArrayPrefDemo();
demo.updateInvestorData();
demo.getInvestorData();
demo.updatePrefData();
demo.getPrefData();

}


private void getPrefData() throws SQLException{
Connection con = getConnection();
// call the plsql function
OracleCallableStatement cs = (OracleCallableStatement)con.prepareCall ("BEGIN ? := pkgInvestor.fnGetInvestorPreferences(?, ?, ?); END;");

// bind variables

//register the output parameter for return value
cs.registerOutParameter (1, Types.INTEGER);

//set the Investor Id (party_fid)
cs.setString(2, INVESTOR_FID);

//set the Preference Type (COMMUNICATION)
cs.setString(3, COMMS_PREF);

//set the preference array
cs.registerOutParameter (4, OracleTypes.ARRAY,"INVESTOR_PREF_TAB");

cs.execute();

int result = cs.getInt(1);

if (result == 0 ){

// get the results of the oracle array into a local jdbc array=20
oracle.sql.ARRAY results = (oracle.sql.ARRAY)cs.getArray(4);

// flip it into a result set
ResultSet rs = results.getResultSet();

// process the result set
while (rs.next()) {

// since it's an array of objects, get and display the value of the underlying object
oracle.sql.STRUCT obj = (STRUCT)rs.getObject(2);

Object vals[] = obj.getAttributes();

System.out.println ("Pref Name : "+vals[0] + ", Pref value : " + vals[1] );
}

rs.close();

} else {
System.out.println("Error :"+ cs.getString(5) );

}

cs.close();
con.close();
}

private void updatePrefData() throws SQLException{

//List listOfPreferences = new ArrayList();
final List listOfPreferences = new LinkedList();

listOfPreferences.add(new Preferences("P1-Name", "P1-Value"));
listOfPreferences.add(new Preferences("P2-Name", "P2-Value"));
listOfPreferences.add(new Preferences("P3-Name", "P3-Value"));

Connection con = getConnection();

// Now, create an array descriptor
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "INVESTOR_PREF_TAB", con );

ARRAY array_to_pass = new ARRAY( descriptor, con, (Object[]) listOfPreferences.toArray());

OracleCallableStatement cs = (OracleCallableStatement)con.prepareCall ("BEGIN ? := pkgInvestor.fnSetInvestorPreferences(?, ?, ?); END;");

//register the output parameter for return value
cs.registerOutParameter (1,Types.INTEGER);

//set the Investor Id (party_fid)
cs.setString(2, INVESTOR_FID);

//set the Preference Type (COMMUNICATION)
cs.setString(3, COMMS_PREF);

//set the preference array
cs.setARRAY( 4, array_to_pass );

cs.execute();

//get the result of the procedure execution
int result = cs.getInt(1);
con.commit();
cs.close();
con.close();

System.out.println("Result :"+ result);

}


private void updateInvestorData() throws SQLException{

Investor inv = new Investor();
inv.setInvestorId(INVESTOR_FID);
inv.setInvestorType("CLIENT");
inv.setFirstName("SCOTT");
inv.setFirstName("PAUL");
inv.setFirstName("FORD");

Connection con = getConnection();

StructDescriptor descriptor = StructDescriptor.createDescriptor("INVESTOR_OBJ", con);

//create attribute list as defined in the oracle type object (INVESTOR_OBJ)
Object [] attributes = { INVESTOR_FID, "CLIENT", "SCOTT","FORD","PAUL" };

STRUCT struct_to_pass = new STRUCT(descriptor, con, attributes) ;

OracleCallableStatement cs = (OracleCallableStatement)con.prepareCall ("BEGIN ? := pkgInvestor.fnSetInvestor(?); END;");

//register the output parameter for return value
cs.registerOutParameter (1,Types.INTEGER);

//set the preference array
cs.setSTRUCT( 2, struct_to_pass );

cs.execute();
int result = cs.getInt(1);
con.commit();
cs.close();
con.close();

System.out.println("Result :"+ result);
}


private void getInvestorData() throws SQLException{

Connection con = getConnection();

OracleCallableStatement cs = (OracleCallableStatement)con.prepareCall ("BEGIN ? := pkgInvestor.fnGetInvestor(?, ?); END;");

//register the output parameter for return value
cs.registerOutParameter (1,Types.INTEGER);

//set the Investor Id (party_fid)
cs.setString(2, INVESTOR_FID);

//register the output parameter for structure
cs.registerOutParameter (3,OracleTypes.STRUCT, "INVESTOR_OBJ");

cs.execute();
int result = cs.getInt(1);
System.out.println("Result :"+ result);

if (result == 0 ){
STRUCT obj = cs.getSTRUCT(3);
Object vals[] = obj.getAttributes();
System.out.println("INVESTOR_FID :"+ vals[0]+",INVESTOR_TYPE: "+ vals[1]+",FIRST_NAME: "+ vals[2]+",MIDDLE_NAME: "+ vals[3]+",LAST_NAME: "+ vals[3]);
}

con.commit();
cs.close();
con.close();

}


private Connection getConnection() throws SQLException {

DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver());
Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1525:SAMPLE_DB" , "scott", "tiger" );
con.setAutoCommit( false );
return con;
}

}

Edited by: ca109951 on Mar 1, 2011 9:25 PM