REF CURSOR ISSUE WHILE CALLING FROM JAVA

ajexpert
ajexpert used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2011
Awarded 2011

Commented:

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




Commented:
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.
Awarded 2011
Awarded 2011

Commented:
Once you have it all setup, perhaps you can try it with the latest driver, I guess ojdbc5.jar
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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



Awarded 2011
Awarded 2011

Commented:
So this happens only from java and only with DUAL table?  
Awarded 2011
Awarded 2011

Commented:
But you probably would not need to use dual if you use CallableStatement with procedure in the JDBC?
Commented:
We have created the temp table and for now the issue is resolved, but this is not I was looking for.

I am requesting to close the question without awarding any points as I didnt get the solution.

If any of experts have objection, please let me know

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial