asked on 5/13/2011

REF CURSOR ISSUE WHILE CALLING FROM JAVA

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;

Last Comment

6/20/2011

5/14/2011

Sure, it seems the issue should be with the JDBC driver.
What version of Oracle and what verison of jdbc driver are you using?

With older versions of Oracle the setting of NLS_LNAGUAGE and NLS_TERRITORY from JVM
user.language property may play the role. If for some reason (perhpas not probable, but still)
you have some "non-trivial" setting maximum character for varchar2
may be 2000

See

http://download.oracle.com/docs/cd/A91202_01/901_doc/java.901/a90211/advanc.htm
and in particular table 18-1

If you have an older JDBC driver, you may consider to try the latest one
which deals better with the issue of varchar2 length, see

http://confluence.atlassian.com/display/JIRA/Using+Oracle+10g+drivers+to+solve+the+4000+character+limitation

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

Am P

5/15/2011

Agreed with for_yan.
There might be a problem with a jdbc driver. I've verified it with your procedure and i'm getting the same result using the java code and oracle code (provided by you for testing).

I'm using the classes12.jar that contains the jdbc driver for oracle.

for_yan

5/15/2011

Once you have it all setup, perhaps you can try it with the latest driver, I guess ojdbc5.jar

ajexpert

5/16/2011

ASKER

Folks, I have an update on this. May be I led all of you in other direction by stating problem with Oracle driver.

If you look in the code, we have used somewhat like, SELECT(v_clm_data) FROM DUAL

If I open the store this string (v_clm_data) in temp table as VARCHAR2 column and open it as
open p_clm_data as
'SELECT * FROM TEMP' ....................the code works !!.

So, I guess (not sure), there is a problem with Oracle dual table, which eventually cannot handle more than 2000 characters ONLY when called through Java. May be an oracle bug

It may not be driver issue, I suspect its Oracle 10g R2 and may work with Oracle 11g R2

Also we use odbc14.jar. Its not possible to change the driver at this stage as our code is alreay in production and not recommened to change unless required

for_yan

5/16/2011

So this happens only from java and only with DUAL table?

for_yan

5/16/2011

But you probably would not need to use dual if you use CallableStatement with procedure in the JDBC?

ASKER CERTIFIED SOLUTION

ajexpert

6/20/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial