Retrive from DB

[Dileep]

How to retrive outputparam using callable statement from java.

the oputput pram is a type of VARCHAR2_TABLE. and varchar_table defined as below

TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;

If anybody know can u please tell me.i have strucked here since last 2 days.

[RamyaSivakanth]

Gothru this article and section 8.1.4 for Out parameter.
http://java.sun.com/j2se/1.4.2/docs/...statement.html

[r035198x]

Gothru this article and section 8.1.4 for Out parameter.
http://java.sun.com/j2se/1.4.2/docs/...statement.html

Those docs are for JDK 1.4 which has already reached its end of life of service.

[RamyaSivakanth]

Hi Mr r0...,
The above link is applicable for current version also.Its a excellent article which guides the beginners.Please don't try to pinpoint everything.

-Regards
Ramya

[r035198x]

..
The above link is applicable for current version also...

No it's not. It's applicable for JDK 1.4 which is now obsolete. If you want to help then at least give people relevant links. Old, deprecated links will only mislead people.

[RamyaSivakanth]

The above article with the above section is not having any deprecated methods.Please gothru..

[r035198x]

The above article with the above section is not having any deprecated methods.Please gothru..

I don't need to go through it. Neither does the OP. In fact no one should be reading it these days for new programs. I don't see why you are trying to defend yourself here. Just post relevant links that's all.

[Dileep]

Hi i already gone through that.I didn't get any exact answer..I will copy code for you...

[Dileep]

String dealerdetailscall = "{ ? = call CSI_REMEDY.GET_DEALER_DETAILS(?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?) }";

/* CallableStatement callableStatement = con.prepareCall(dealerdetailscall);
DelegatingCallableStatement cstmt = (DelegatingCallableStatement)callableStatement;
*/
logger.info(" before con wrap");
Connection conn = ((DelegatingConnection) con).getInnermostDelegate();
logger.info(" after con wrap");
OracleCallableStatement cstmt = (OracleCallableStatement) conn.prepareCall(dealerdetailscall);
logger.info(" after con wrap****");
boolean flag = false;
logger.info(" after flag****");
try{
cstmt.registerOutParameter(1, Types.INTEGER);
logger.info(" after flag****");
cstmt.setString(2, DealereCode.trim());
cstmt.setInt(3, 0);
cstmt.registerOutParameter(4, Types.INTEGER);
logger.info(" after 4 ****");
cstmt.registerOutParameter(5, OracleTypes.ARRAY,"VARCHAR2_TABLE");
logger.info(" after 5 ****");
cstmt.registerOutParameter(6, Types.ARRAY, "VARCHAR2_TABLE");
logger.info(" after 6 ****");
cstmt.registerOutParameter(7, Types.ARRAY, "VARCHAR2_TABLE");
cstmt.registerOutParameter(8, Types.ARRAY, "VARCHAR2_TABLE");
cstmt.registerOutParameter(9, Types.ARRAY, "VARCHAR2_TABLE");
cstmt.registerOutParameter(10, Types.ARRAY, "VARCHAR2_TABLE");
cstmt.registerOutParameter(11, Types.ARRAY, "VARCHAR2_TABLE");
cstmt.registerOutParameter(12, Types.ARRAY, "VARCHAR2_TABLE");
cstmt.registerOutParameter(13, Types.ARRAY, "VARCHAR2_TABLE");
cstmt.registerOutParameter(14, Types.ARRAY, "VARCHAR2_TABLE");
cstmt.registerOutParameter(15, Types.INTEGER);
cstmt.registerOutParameter(16, Types.VARCHAR);
logger.info(" after 14 ****");

//ResultSet rs =cstmt.executeQuery();
logger.info(" after 14 ****"+cstmt.toString());
logger.info("get dealer deails "+cstmt.execute());



******************* below one is Db func**** see that below*****

FUNCTION GET_DEALER_DETAILS (IN_DEALER_CODE IN VARCHAR2,
IN_CIS_ACCT IN NUMBER,
OUT_COUNT OUT NUMBER,
OUT_DEALER_CODE OUT VARCHAR2_TABLE,
OUT_CIS_ACCT OUT VARCHAR2_TABLE,
OUT_NAME OUT VARCHAR2_TABLE,
OUT_ADDRESS OUT VARCHAR2_TABLE,
OUT_CITY OUT VARCHAR2_TABLE,
OUT_STATE OUT VARCHAR2_TABLE,
OUT_COUNTRY OUT VARCHAR2_TABLE,
OUT_ZIP OUT VARCHAR2_TABLE,
OUT_OEM_CIS_ACCT OUT VARCHAR2_TABLE,
OUT_OEM_NAME OUT VARCHAR2_TABLE,
OUT_ERROR_CODE OUT NUMBER,
OUT_ERROR_MESSAGE OUT VARCHAR2) RETURN NUMBER AS

V_COUNT NUMBER := 0;
V_DEALER_CODE ENTIGO.PARTNER.REFERENCE%TYPE;
V_CIS_ACCT ENTIGO.ORG_EXT.ACCT_NUMBER%TYPE;
V_NAME ENTIGO.ORG.ORG_NAME%TYPE;
V_ADDRESS ENTIGO.ADDR.LINES%TYPE;
V_CITY ENTIGO.ADDR.CITY%TYPE;
V_STATE ENTIGO.GEO.NAME%TYPE;
V_COUNTRY ENTIGO.GEO.NAME%TYPE;
V_ZIP ENTIGO.ADDR.ZIP%TYPE;
V_OEM_CIS_ACCT ENTIGO.ORG_EXT.ACCT_NUMBER%TYPE;
V_OEM_NAME ENTIGO.ORG.ORG_NAME%TYPE;

CURSOR C1 IS
SELECT TRIM(A.REPAIR_FACILITY_ID), A.ACCT_NUMBER, TRIM(C.ACCT_NAME), TRIM(C.ADDRESS1)||' '||TRIM(C.ADDRESS2), TRIM(C.CITY), TRIM(C.STATE), TRIM(C.COUNTRY), TRIM(C.ZIPCODE), B.OEM_ACCT_NUMBER, D.ACCT_NAME
FROM CIS2.OEM_REPAIR_ASSC@TCOPCIS2 A, CIS2.OEM_MFG@TCOPCIS2 B, CIS2.ACCOUNT@TCOPCIS2 C, CIS2.ACCOUNT@TCOPCIS2 D
WHERE A.REPAIR_FACILITY_ID LIKE '%'||TRIM(IN_DEALER_CODE)||'%'
AND A.OEM_CODE = B.OEM_CODE
AND A.ACCT_NUMBER = C.ACCT_NUMBER
AND B.OEM_ACCT_NUMBER = D.ACCT_NUMBER;

CURSOR C2 IS
SELECT TRIM(A.REPAIR_FACILITY_ID), A.ACCT_NUMBER, TRIM(C.ACCT_NAME), TRIM(C.ADDRESS1)||' '||TRIM(C.ADDRESS2), TRIM(C.CITY), TRIM(C.STATE), TRIM(C.COUNTRY), TRIM(C.ZIPCODE), B.OEM_ACCT_NUMBER, D.ACCT_NAME
FROM CIS2.OEM_REPAIR_ASSC@TCOPCIS2 A, CIS2.OEM_MFG@TCOPCIS2 B, CIS2.ACCOUNT@TCOPCIS2 C, CIS2.ACCOUNT@TCOPCIS2 D
WHERE A.ACCT_NUMBER = IN_CIS_ACCT
AND A.OEM_CODE = B.OEM_CODE
AND A.ACCT_NUMBER = C.ACCT_NUMBER
AND B.OEM_ACCT_NUMBER = D.ACCT_NUMBER;


BEGIN

IF (IN_CIS_ACCT IS NOT NULL AND IN_CIS_ACCT > 0) THEN
BEGIN
OPEN C2;
LOOP
FETCH C2 INTO V_DEALER_CODE, V_CIS_ACCT, V_NAME, V_ADDRESS, V_CITY, V_STATE, V_COUNTRY, V_ZIP, V_OEM_CIS_ACCT, V_OEM_NAME;

EXIT WHEN C2%NOTFOUND;

V_COUNT := V_COUNT + 1;

OUT_DEALER_CODE(V_COUNT) := V_DEALER_CODE;
OUT_CIS_ACCT(V_COUNT) := V_CIS_ACCT;
OUT_NAME(V_COUNT) := V_NAME;
OUT_ADDRESS(V_COUNT) := V_ADDRESS;
OUT_CITY(V_COUNT) := V_CITY;
OUT_STATE(V_COUNT) := V_STATE;
OUT_COUNTRY(V_COUNT) := V_COUNTRY;
OUT_ZIP(V_COUNT) := V_ZIP;
OUT_OEM_CIS_ACCT(V_COUNT):= V_OEM_CIS_ACCT;
OUT_OEM_NAME(V_COUNT) := V_OEM_NAME;


END LOOP;
CLOSE C2;

OUT_COUNT := V_COUNT;

END;
ELSE IF (IN_DEALER_CODE IS NOT NULL) THEN
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO V_DEALER_CODE, V_CIS_ACCT, V_NAME, V_ADDRESS, V_CITY, V_STATE, V_COUNTRY, V_ZIP, V_OEM_CIS_ACCT, V_OEM_NAME;

EXIT WHEN C1%NOTFOUND;

V_COUNT := V_COUNT + 1;

OUT_DEALER_CODE(V_COUNT) := V_DEALER_CODE;
OUT_CIS_ACCT(V_COUNT) := V_CIS_ACCT;
OUT_NAME(V_COUNT) := V_NAME;
OUT_ADDRESS(V_COUNT) := V_ADDRESS;
OUT_CITY(V_COUNT) := V_CITY;
OUT_STATE(V_COUNT) := V_STATE;
OUT_COUNTRY(V_COUNT) := V_COUNTRY;
OUT_ZIP(V_COUNT) := V_ZIP;
OUT_OEM_CIS_ACCT(V_COUNT):= V_OEM_CIS_ACCT;
OUT_OEM_NAME(V_COUNT) := V_OEM_NAME;


END LOOP;
CLOSE C1;

OUT_COUNT := V_COUNT;

END;
END IF;
END IF;

RETURN 0;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR CODE = '||SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE = '||SQLERRM);
RETURN 1; --ERROR

END GET_DEALER_DETAILS;


TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;



when i tried to call with above code to function i am getting below error** please look error *****


Exception occured java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'GET_DEALER_DETAILS'
ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'GET_DEALER_DETAILS'
ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'GET_DEALER_DETAILS'
ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'GET_DEALER_DETAILS'
ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'GET_DEALER_DETAILS'
ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'GET_DEALER_DETAILS'
ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'GET_DEALER_DETAILS'
ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'GET_DEALER_DETAILS'
ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'GET_DEALER_DETAILS'
ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'GET_DEALER_DETAILS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


DO you worked at any time or Do u have any idea about.


If you know can please post for me....Its very important.

Please......

[r035198x]

The errors you are getting are very suggestive.
Did you cross check to see if you are passing the right number and types of arguments when making the call?

[RamyaSivakanth]

Hi,
Just check u have properly put the placeholder or not.Another one in 13th index u are trying to retreive "VARCHAR2_TABLE".Please cross check it once.

Send the complete output generated.

-Regards
Ramya

[Dileep]

Hi I checked twice.But still i did n't get that...If you don't mind can u please cross check for me what i did wrong??

[RamyaSivakanth]

Are u able to get the debugging statement lines ..put after each registerOutParameter.

Iam having doubt in 13th placeholder retreival.Just check..whether it is a number ,but u are trying to retreive as "stmt.registerOutParameter(13, Types.ARRAY, "VARCHAR2_TABLE");" Just cross check this.