troubleshooting Question

REF CURSOR ISSUE WHILE CALLING FROM JAVA

Avatar of ajexpert
ajexpertFlag for United States of America asked on
JavaOracle Database
7 Comments1 Solution301 ViewsLast Modified:
We have a weird issue, I possibly think its java driver which could be a problem.

We do not see any issue when the ref cur is tested from Oracle.

When invoked from Java, the output appears to be truncated to 2000 characters, where as
refcursor value is 2600 characters.

------ORACLE CODE and testing cursor-------------------------

CREATE OR REPLACE PROCEDURE PK_REFCUR_TEST (p_clm_data   OUT sys_refcursor,
                                            p_err_code   OUT VARCHAR2,
                                            p_err_msg    OUT VARCHAR2)
AS
   v_clm_data VARCHAR2 (32000)
         := '98326|647937180003,646135937007,646135937008,646135937009,646135937010,646260429001,646260429002,646260429003,646260429004,646260429005,646260429006,647524507007,647524507008,647524507009,647524507010,647524507011,647524507001,647524507002,647524507003,647524507004,647524507005,647524507006,647937180001,647937180002,650890539011,650890539012,650890539013,650890539014,650890539016,650890539017,650890539018,650890539019,644213895001,644213895002,644213895003,644213895005,644213895004,644213895006,644213895007,644213895008,644213895009,644213895010,644213895011,645005925001,645005925002,645005925003,645005925004,645005925005,645005925006,645005925007,645005925008,645005925009,645005925010,645005925011,645005925012,645005925013,649532084001,647937180004,647937180005,647937180006,647937180007,647937180016,647937180011,647937180012,647937180013,647937180014,647937180015,647937180008,647937180009,647937180010,648532418002,648532418003,648532418001,649357071001,649357071002,649357071003,649357071004,649357071005,649357071006,649357071007,645005925014,645005925015,645005925016,645005925017,645005925018,645005925019,645005925020,645005925021,645005925022,645005925023,645005925024,645005925025,645497102015,645497102016,645497102018,645497102021,645497102019,645497102020,645497102022,645497102023,645497102024,645497102025,645497102026,649532084002,649947208003,649532084003,649532084004,649532084005,649947208004,649947208006,649532084006,649532084007,649532084008,649532084009,649532084010,649532084011,649532084012,649532084013,649532084014,649532084015,649532084016,649532084017,649532084018,649947208001,649947208005,649947208002,645497102028,645497102029,645497102001,645497102002,645497102003,645497102004,645497102005,645497102031,645497102006,645497102007,645497102008,645497102013,645497102014,645497102009,645497102010,645497102011,645497102012,645718803006,645718803007,645718803008,645718803009,645718803011,645718803010,645718803012,650890539008,650890539009,650890539010,649947208007,649947208008,650076734001,650076734002,650076734003,650076734004,650076734010,650076734005,650076734006,650076734007,650076734011,650076734012,650076734008,650076734009,650890539001,650890539002,650890539003,650890539004,650890539005,650890539006,650890539007,645718803013,645718803014,645718803015,645718803017,645718803018,645718803016,645718803002,645718803001,645718803003,645718803004,645718803005,646135937011,646135937014,646135937015,646135937012,646135937016,646135937017,646135937013,646135937001,646135937002,646135937003,646135937004,646135937005,646135937006|F|0' ;

   v_dupsql   VARCHAR2 (4000);
BEGIN
   v_dupsql :=
         'select '
      || ''''
      || v_clm_data
      || ''''
      || ' as claim_dup_data from dual ';


   OPEN p_clm_data FOR v_dupsql;

   p_err_code := '0';
EXCEPTION
   WHEN OTHERS
   THEN
      p_err_code := '1';
      p_err_msg := SQLERRM;
END;






------------testing from PL/SQL----------------------------------------
DECLARE
   P_TST_CUR   sys_refcursor;

   v_err_code           VARCHAR2 (1000);
   v_err_msg            VARCHAR2 (1000);


   v_test_date          VARCHAR2 (32767);
BEGIN
   PK_REFCUR_TEST (P_TST_CUR, v_err_code, v_err_msg);


   LOOP
      FETCH P_TST_CUR INTO   v_test_date;

      
      EXIT WHEN P_TST_CUR%NOTFOUND;

       -- THIS OUTPUT IS BEING TRUNCATED to 2000 characters WHEN CALLED FROM JAVA--- 
      DBMS_OUTPUT.put_line ('VALUE=>' || v_test_date);
   END LOOP;

   CLOSE P_TST_CUR;
END;

Open in new window

ASKER CERTIFIED SOLUTION
ajexpert

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros