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

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Tue, 21 Jan 2003 21:53:32 +0300
Message-ID: <b0k4vf$9c0$1@babylon.agtel.net>

If I am not mistaken, since 8i there are temporary LOBs. You create them using DBMS_LOB.createTemporary() and they do not reside in any particular table. For example, I am using them in my UTL_BINFILE package (which is available as part of our commercial PSP_Mail product, but may be extracted from it and used freely) for reading/writing LOBs from/to OS files - the actual code is in Java. I just call createTemporary() and then write what I need to that temporary LOB and return the locator. Caller is responsible for freeing that LOB when no longer needed using DBMS_LOB.freeTemporary(), and they are disposed automatically when session ends anyway. Java code is nearly trivial, and to simplify even further I used SQLJ to avoid typing JDBC calls. :) Here's a snippet:

 oracle.sql.BLOB lob = null;
 try {

// create a temporary cached BLOB

       #sql {CALL dbms_lob.createtemporary(:inout lob, true)};


       // get the output stream into BLOB

       OutputStream ostream = lob.getBinaryOutputStream();
       ....
      }

catch (Exception e)
  { // cast all exceptions to SQLException

     throw new SQLException(e.getMessage());    }
 return lob;
}

For CLOBs, you will use getCharacterOutputStream() for Unicode (returns java.io.Writer) or getAsciiOutputStream() for ASCII (returns java.io.OutputStream) instead of getBinaryOutputStream().

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Peter Sulikowski" <peterjsulikowski_at_hotmail.com> wrote in message
news:44442672.0301170630.4dff6f45_at_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 Tue Jan 21 2003 - 12:53:32 CST