Results 1 to 13 of 13

Thread: Retrive from DB

  1. #1
    Dileep is offline Member
    Join Date
    Sep 2009
    Posts
    4
    Rep Power
    0

    Default Retrive from DB

    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.

  2. #2
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    836
    Rep Power
    6

    Default

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

  3. #3
    r035198x is offline Senior Member
    Join Date
    Aug 2009
    Posts
    2,388
    Rep Power
    8

    Default

    Quote Originally Posted by RamyaSivakanth View Post
    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.

  4. #4
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    836
    Rep Power
    6

    Default

    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
    Ramya:cool:

  5. #5
    r035198x is offline Senior Member
    Join Date
    Aug 2009
    Posts
    2,388
    Rep Power
    8

    Default

    Quote Originally Posted by RamyaSivakanth View Post
    ..
    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.

  6. #6
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    836
    Rep Power
    6

    Default

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

  7. #7
    r035198x is offline Senior Member
    Join Date
    Aug 2009
    Posts
    2,388
    Rep Power
    8

    Default

    Quote Originally Posted by RamyaSivakanth View Post
    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.

  8. #8
    Dileep is offline Member
    Join Date
    Sep 2009
    Posts
    4
    Rep Power
    0

    Default

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

  9. #9
    Dileep is offline Member
    Join Date
    Sep 2009
    Posts
    4
    Rep Power
    0

    Default

    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......

  10. #10
    r035198x is offline Senior Member
    Join Date
    Aug 2009
    Posts
    2,388
    Rep Power
    8

    Default

    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?
    Last edited by r035198x; 09-24-2009 at 10:36 AM.

  11. #11
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    836
    Rep Power
    6

    Default

    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
    Ramya:cool:

  12. #12
    Dileep is offline Member
    Join Date
    Sep 2009
    Posts
    4
    Rep Power
    0

    Default

    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??

  13. #13
    RamyaSivakanth's Avatar
    RamyaSivakanth is offline Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    836
    Rep Power
    6

    Default

    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.
    Ramya:cool:

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •