We help IT Professionals succeed at work.
Get Started

REF CURSOR ISSUE WHILE CALLING FROM JAVA

ajexpert
ajexpert asked
on
300 Views
Last Modified: 2012-12-26
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

Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE