VARCHAR2>32k, Java - modification of Tom Kytes Routine

From: Peter Sulikowski <peterjsulikowski_at_hotmail.com>
Date: 17 Jan 2003 06:30:44 -0800
Message-ID: <44442672.0301170630.4dff6f45@posting.google.com>

Hi, this is one of Tom Kytes sample apps that works perfectly from:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:650337598357

I have a problem and it isn't Toms but, I think, a limit of 32K on a VARCHAR2. I've posted a reply to Tom, he's a pretty busy chap and I'm wanting ideas on a resolution rather quickly. Can anyone suggest a way to return character data >32K by modifying this code.

The best solution for me would be something like:

procedure getContentClob(p_content OUT CLOB)   is language java name 'Test.getContent(oracle.sql.CLOB[])';

OR

function getContentClob return clob;

But I'm not sure how to code the Java properly in "Test" to return the clob.

Thank you
Peter

• Here down from Tom's web-site.

create or replace and compile java source named "Test" as

      class Test 
      {
        private static String strContent_;
        public static void setContent(String strContent) 
          {
           strContent_ = strContent;
          }
        public static void getContent(String[] p_content)
          {
           p_content[0] = strContent_;
          }    
     }

/

create or replace package testjava as

        procedure  setContent(p_content VARCHAR2) 
          is language java name 'Test.setContent(java.lang.String)';
        function getContent return varchar2;
        procedure getContent(p_content OUT varchar2) 
          is language java name 'Test.getContent(java.lang.String[])';

   end testjava;
/

create or replace package body testjava as

    function getContent return varchar2
    is

        l_data long;
    begin

        getContent(l_data);
        return l_data;

    end;
end testjava;
/

exec testjava.setcontent( rpad('*',32000,'*') ) exec dbms_output.put_line( length(testjava.getcontent) )

exec testjava.setcontent( rpad('*',33000,'*') )

--problem here, cannot return > 32K.

---

exec dbms_output.put_line( length(testjava.getcontent) )

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production NLSRTL Version 3.4.1.0.0 - Production Received on Fri Jan 17 2003 - 08:30:44 CST