Oops perdon este es el codigo en java
import java.sql.*;
import java.util.LinkedList;
import java.util.List;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
//import oracle.jdbc.driver.OracleCallableStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
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 = DataBase.getConnectionJDBC();
// call the plsql function
//OracleCallableStatement cs = (OracleCallableStatement) con.prepareCall("BEGIN ? := pkgInvestor.fnGetInvestorPreferences(?, ?, ?); END;");
oracle.jdbc.OracleCallableStatement cs = (oracle.jdbc.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"));
System.out.println(""+listOfPreferences.toArray());
Connection con = DataBase.getConnectionJDBC();
// 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;");
oracle.jdbc.OracleCallableStatement cs = (oracle.jdbc.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 = DataBase.getConnectionJDBC();
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;");
oracle.jdbc.OracleCallableStatement cs = (oracle.jdbc.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 = DataBase.getConnectionJDBC();
//OracleCallableStatement cs = (OracleCallableStatement) con.prepareCall("BEGIN ? := pkgInvestor.fnGetInvestor(?, ?); END;");
oracle.jdbc.OracleCallableStatement cs = (oracle.jdbc.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();
}
}
Aqui envio el package oracle y los types
CREATE OR REPLACE PACKAGE BODY MINERVA.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
dbms_output.put_line('inInvestor.investor_id : '|| inInvestor.investor_id);
dbms_output.put_line('inInvestor.investor_type : '|| inInvestor.investor_type);
dbms_output.put_line('inInvestor.first_name : '|| inInvestor.first_name);
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;
/
DROP TYPE MINERVA.INVESTOR_PREF_TAB;
CREATE OR REPLACE TYPE MINERVA."INVESTOR_PREF_TAB" as table of investor_pref_obj;
/
DROP TYPE MINERVA.INVESTOR_OBJ;
CREATE OR REPLACE TYPE MINERVA."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)
)
/
DROP TYPE MINERVA.INVESTOR_PREF_OBJ;
CREATE OR REPLACE TYPE MINERVA."INVESTOR_PREF_OBJ" as object( preference_name varchar2(30), preference_value varchar2(30));
/