Custom object in Oracle stored procedure 11.2.0.4.0
I am calling an Oracle stored procedure in which I have an array of a custom type object. Below is my code.
Type Object:
create or replace type xx_wf_svc_ntf_attr_record is object (CITY varchar(4000),
COUNTRY number,
POP number,
CURRENT_DATE date);
Table of Object
create or replace type XX_WF_SVC_NTF_ATTR_ARRAY is table of XX_WF_SVC_NTF_ATTR_RECORD;
Procedure
CREATE OR REPLACE PROCEDURE Respond (
p_notification_id IN NUMBER,
p_respond_result IN VARCHAR2,
p_respond_comments IN VARCHAR2,
p_responder IN VARCHAR2,
p_attr_array IN XX_WF_SVC_NTF_ATTR_ARRAY,
p_result OUT NOCOPY NUMBER,
p_error_code OUT NOCOPY NUMBER,
p_error_msg OUT NOCOPY VARCHAR2)
AS
BEGIN
FOR i IN p_attr_array.FIRST .. p_attr_array.LAST
LOOP
INSERT INTO temp_1 (city,
country,
pop,
CURRENT_DATE)
VALUES (p_attr_array (i).CITY,
p_attr_array (i).COUNTRY,
p_attr_array (i).POP,
NVL (p_attr_array (i).CURRENT_DATE, SYSDATE));
END LOOP;
p_result := 0;
END;
Java code :
package com.push.db;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
public class SupplierArray {
public static void passArray()
{
try {
DBConnectionManager db = new DBConnectionManager();
Connection conn = db.getConnection();
CallableStatement cstmt = conn.prepareCall("call APPS.Respond(?,?,?,?,?,?,?,?)");
STRUCT recStruct = null;
StructDescriptor recDescriptor = null;
ArrayDescriptor arrayDescriptor = null;
ARRAY array = null;
Object[] objResult = new Object[4];
recDescriptor = StructDescriptor.createDescriptor("APPS.XX_WF_SVC_NTF_ATTR_RECORD", conn);
arrayDescriptor = ArrayDescriptor.createDescriptor ("APPS.XX_WF_SVC_NTF_ATTR_ARRAY", conn);
objResult [0] = "kantilal";
objResult [1] = Integer.parseInt("100");
objResult [2] = Integer.parseInt("300"); //new Integer(1234);
objResult [3] = getCurrentDate();
recStruct = new STRUCT(recDescriptor, conn, objResult);
STRUCT[] recStructArray = {recStruct};
array = new ARRAY(arrayDescriptor, conn, recStructArray);
cstmt.setInt(1, 1234);
cstmt.setString(2, "test");
cstmt.setString(3, "US");
cstmt.setString(4, "I");
cstmt.setArray(5, array);
cstmt.registerOutParameter(6,Types.NUMERIC);
cstmt.registerOutParameter(7,Types.NUMERIC);
cstmt.registerOutParameter(8,Types.VARCHAR);
cstmt.execute();
System.out.println("result : " + cstmt.getInt(6));
System.out.println("msg : " + cstmt.getString(8));
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String args[]){
passArray();
}
public static java.sql.Date getCurrentDate() {
java.util.Date today = new java.util.Date();
return new java.sql.Date(today.getTime());
}
}
In Oracle version : 11.2.0.4.0 Only number and date are stored in table named temp_1. But string value is not stored in the table. This code works fine in Oracle.
In Oracle version : 12.1.0.1.0.
All data is stored in database. NO issue.
Test_1 Table :
CREATE TABLE temp_1
(
city VARCHAR2 (4000),
country NUMBER,
pop NUMBER,
CURRENT_DATE DATE
);
I have tried ojdbc5.jar, ojdbc6.jar, ojdbc7.jar etc. I know this is versioning issue, but I don't know how to resolve it.
链接地址: http://www.djcxy.com/p/20298.html