I want to call oracle pl/sql stored procedure using java.
I have tried JDBC connection,Hibernate and also JPA,
but i am getting one error:
invalid name pattern: APPS.cust_account_rec_type.
Following is code snippet
//java code
try {
OracleCallableStatement callStmt = null;
String dbURL2 = "jdbc:oracle:thin:@192.168.3.12:1522:TEST";
String username = "test";
String password = "test";
Connection conn2 = DriverManager.getConnection(dbURL2, username,
password);
// Stage values for each field in the Oracle record in an array
Object[] javacustRec = new Object[101];
javacustRec[74]="test1";
javacustRec[99]="TCA_V2_API";
Object[] javapersonRec = new Object[63];
javapersonRec[1]="test2";
javapersonRec[3]="test3";
Object[] javacustProfileRec = new Object[108];
if (conn2 != null) {
logger.info("Connected with connection ");
// Create descriptor for the Oracle Record type "cust_account_rec_type" required
StructDescriptor recCustDesc = StructDescriptor.createDescriptor("cust_account_rec_type", conn2);
// Create descriptor for the Oracle Record type "cust_account_rec_type" required
StructDescriptor recPersonDesc = StructDescriptor.createDescriptor("person_rec_type", conn2);
// Create descriptor for the Oracle Record type "cust_account_rec_type" required
StructDescriptor recCustProfileDesc = StructDescriptor.createDescriptor("customer_profile_rec_type", conn2);
//create struct to send data as records to procedure parameter
STRUCT custRecStruct = new STRUCT(recCustDesc, conn2, javacustRec);
//create struct to send data as records to procedure parameter
STRUCT personRecStruct = new STRUCT(recPersonDesc, conn2, javapersonRec);
//create struct to send data as records to procedure parameter
STRUCT custProfileRecStruct = new STRUCT(recCustProfileDesc, conn2, javacustProfileRec);
String callProc = "{CALL HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT(:p_create_profile_amt,:P_CUST_ACCOUNT_REC, :P_PERSON_REC, :P_CUSTOMER_PROFILE_REC, :p_create_profile_amt, :X_CUST_ACCOUNT_ID, :X_ACCOUNT_NUMBER, :X_PARTY_ID, :X_PARTY_NUMBER, :X_PROFILE_ID, :X_RETURN_STATUS, :X_MSG_COUNT, :X_MSG_DATA) }";
// Create a Callable Statement
callStmt = (OracleCallableStatement) conn2.prepareCall(callProc);
callStmt.setObject("p_create_profile_amt", 'T'); // in parameter with default value 'TRUE'
callStmt.setSTRUCT("P_CUST_ACCOUNT_REC", custRecStruct); //in cust_account_rec_type parameter to send IN RECORDS
//callStmt.setObject("P_CUST_ACCOUNT_REC", custAccountRecType); //in parameter to send IN RECORDS
callStmt.setSTRUCT("P_PERSON_REC", personRecStruct); //in cust_account_rec_type parameter to send IN RECORDS
//callStmt.setObject("P_PERSON_REC", personRecType); //in parameter to send IN RECORDS
callStmt.setSTRUCT("P_CUSTOMER_PROFILE_REC", custProfileRecStruct); //in cust_account_rec_type parameter to send IN RECORDS
//callStmt.setObject("P_CUSTOMER_PROFILE_REC",customerProfileRecType); //in parameter to send IN RECORDS
callStmt.setObject("p_init_msg_list", 'F'); // in parameter with default value 'FALSE'
callStmt.registerOutParameter("X_CUST_ACCOUNT_ID",Types.NUMERIC ); //out parameter with NUMBER
callStmt.registerOutParameter("X_ACCOUNT_NUMBER",Types.VARCHAR ); //out parameter with VARCHAR
callStmt.registerOutParameter("X_PARTY_ID", Types.NUMERIC); //out parameter with NUMBER
callStmt.registerOutParameter("X_PARTY_NUMBER", Types.VARCHAR); //out parameter with VARCHAR
callStmt.registerOutParameter("X_PROFILE_ID", Types.NUMERIC); //out parameter with NUMBER
callStmt.registerOutParameter("X_RETURN_STATUS", Types.VARCHAR); //out parameter with VARCHAR
callStmt.registerOutParameter("X_MSG_COUNT",Types.NUMERIC); //out parameter with NUMBER
callStmt.registerOutParameter("X_MSG_DATA", Types.VARCHAR); //out parameter with VARCHAR
callStmt.executeUpdate();
//response after executing procedure
clientContactRespose.setX_MSG_COUNT( callStmt.getInt("X_MSG_COUNT"));
clientContactRespose.setX_MSG_DATA(callStmt.getString("X_MSG_DATA"));
clientContactRespose.setX_PARTY_ID(callStmt.getInt("X_PARTY_ID"));
clientContactRespose.setX_RETURN_STATUS(callStmt.getString("X_RETURN_STATUS"));
clientContactRespose.setX_PROFILE_ID( callStmt.getInt("X_PROFILE_ID"));
clientContactRespose.setX_PARTY_NUMBER(callStmt.getString("X_PARTY_NUMBER"));
clientContactRespose.setX_CUST_ACCOUNT_ID(callStmt.getInt("X_CUST_ACCOUNT_ID"));
}
} catch (SQLException e) {
logger.info("Error occurred at = "+e);
}
Pl/Sql stored procedure As follows:
DECLARE
P_CUST_ACCOUNT_REC HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
P_PERSON_REC HZ_PARTY_V2PUB.PERSON_REC_TYPE;
P_CUSTOMER_PROFILE_REC HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
X_CUST_ACCOUNT_ID NUMBER;
X_ACCOUNT_NUMBER VARCHAR2(2000);
X_PARTY_ID NUMBER;
X_PARTY_NUMBER VARCHAR2(2000);
X_PROFILE_ID NUMBER;
X_RETURN_STATUS VARCHAR2(2000);
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2(2000);
BEGIN
FND_CLIENT_INFO.SET_ORG_CONTEXT('1057');
P_CUST_ACCOUNT_REC.ACCOUNT_NAME := 'test1';
P_CUST_ACCOUNT_REC.CREATED_BY_MODULE := 'TCA_V2_API';
P_PERSON_REC.PERSON_FIRST_NAME := 'test2';
P_PERSON_REC.PERSON_LAST_NAME := 'test3';
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT('T', P_CUST_ACCOUNT_REC, P_PERSON_REC, P_CUSTOMER_PROFILE_REC, 'F', X_CUST_ACCOUNT_ID, X_ACCOUNT_NUMBER, X_PARTY_ID, X_PARTY_NUMBER, X_PROFILE_ID, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA);
DBMS_OUTPUT.PUT_LINE('x_return_status = ' || SUBSTR(X_RETURN_STATUS, 1, 255));
IF X_MSG_COUNT > 1 THEN
FOR I IN 1 .. X_MSG_COUNT LOOP
DBMS_OUTPUT.PUT_LINE(I || '. ' ||
SUBSTR(FND_MSG_PUB.GET(P_ENCODED => FND_API.G_FALSE), 1, 255));
END LOOP;
END IF;
END;
Note-cust_account_rec_type Is Record .(Not Object). and I don't want to change my stored procedure.
TYPE cust_account_rec_type IS RECORD (
cust_account_id NUMBER,
account_number VARCHAR2(30),
attribute_category VARCHAR2(30),
attribute1 VARCHAR2(150),
attribute2 VARCHAR2(150),
attribute3 VARCHAR2(150),
attribute4 VARCHAR2(150)
}
Your help is appreciable.