Home » SQL & PL/SQL » SQL & PL/SQL » LONG to CLOB in Java Stored Function (Oracle DB 9i/10g, Linux)
icon5.gif  LONG to CLOB in Java Stored Function [message #281148] Thu, 15 November 2007 19:09 Go to next message
gthiruva
Messages: 9
Registered: November 2007
Junior Member
I have a table with a LONG column type that I want to extract in a SELECT statement and return to the calling application. The data in the column is in Unicode format. It appears that when bound to PL/SQL through CREATE FUNCTION, PL/SQL does not support returning a LONG data type.

So I figure maybe there's a way to convert the LONG to a CLOB before returning. Is there a way that I can create a Java stored function that converts the LONG to a CLOB and returns the CLOB?

I've tried a bunch a things with no luck. Currently, I'm writing the LONG to a oracle.sql.CLOB object using CLOB's putString() method. Then I have Java return the CLOB. But that doesn't seem to work - I get an "invalid LOB locator error" when trying to call the function in SQL*PLUS.

Anyone have any ideas? Or better yet, pointers to any sample code on the web?

FYI: The architecture of this system is such that the stored function will eventually be called from a C++ application using OCI.

--
George
Re: LONG to CLOB in Java Stored Function [message #281150 is a reply to message #281148] Thu, 15 November 2007 19:14 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
LONG data type are no longer supported.
visit http://asktom.oracle.com & do some KEY WORD searchs
Tom Kyte has many, many find coding examples.
You should be able to find something close to your requirements.
Re: LONG to CLOB in Java Stored Function [message #281163 is a reply to message #281148] Thu, 15 November 2007 23:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Anyway PL/SQL does not supports strings larger than 32K.
If you have a LONG/CLOB larger you have to use an external program and directly query this data.

Regards
Michel
Re: LONG to CLOB in Java Stored Function [message #281418 is a reply to message #281163] Fri, 16 November 2007 15:49 Go to previous messageGo to next message
gthiruva
Messages: 9
Registered: November 2007
Junior Member
Really? I thought returning a CLOB from a Java Stored Function was a way around that limitation since it really returns a LOB locator instead of the whole data.
Re: LONG to CLOB in Java Stored Function [message #281422 is a reply to message #281148] Fri, 16 November 2007 16:09 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
SQL> desc dbms_lob
PROCEDURE APPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_LOB                        BLOB                    IN
PROCEDURE APPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_LOB                        CLOB                    IN
PROCEDURE CLOSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
PROCEDURE CLOSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
PROCEDURE CLOSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN/OUT
FUNCTION COMPARE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_1                          BLOB                    IN
 LOB_2                          BLOB                    IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET_1                       NUMBER(38)              IN     DEFAULT
 OFFSET_2                       NUMBER(38)              IN     DEFAULT
FUNCTION COMPARE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_1                          CLOB                    IN
 LOB_2                          CLOB                    IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET_1                       NUMBER(38)              IN     DEFAULT
 OFFSET_2                       NUMBER(38)              IN     DEFAULT
FUNCTION COMPARE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_1                         BINARY FILE LOB         IN
 FILE_2                         BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN
 OFFSET_1                       NUMBER(38)              IN     DEFAULT
 OFFSET_2                       NUMBER(38)              IN     DEFAULT
PROCEDURE CONVERTTOBLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_CLOB                       CLOB                    IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN/OUT
 SRC_OFFSET                     NUMBER(38)              IN/OUT
 BLOB_CSID                      NUMBER                  IN
 LANG_CONTEXT                   NUMBER(38)              IN/OUT
 WARNING                        NUMBER(38)              OUT
PROCEDURE CONVERTTOCLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_BLOB                       BLOB                    IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN/OUT
 SRC_OFFSET                     NUMBER(38)              IN/OUT
 BLOB_CSID                      NUMBER                  IN
 LANG_CONTEXT                   NUMBER(38)              IN/OUT
 WARNING                        NUMBER(38)              OUT
PROCEDURE COPY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_LOB                        BLOB                    IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN     DEFAULT
 SRC_OFFSET                     NUMBER(38)              IN     DEFAULT
PROCEDURE COPY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_LOB                        CLOB                    IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN     DEFAULT
 SRC_OFFSET                     NUMBER(38)              IN     DEFAULT
PROCEDURE CREATETEMPORARY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 CACHE                          BOOLEAN                 IN
 DUR                            BINARY_INTEGER          IN     DEFAULT
PROCEDURE CREATETEMPORARY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 CACHE                          BOOLEAN                 IN
 DUR                            BINARY_INTEGER          IN     DEFAULT
PROCEDURE ERASE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN     DEFAULT
PROCEDURE ERASE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN     DEFAULT
PROCEDURE FILECLOSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN/OUT
PROCEDURE FILECLOSEALL
FUNCTION FILEEXISTS RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
PROCEDURE FILEGETNAME
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
 DIR_ALIAS                      VARCHAR2                OUT
 FILENAME                       VARCHAR2                OUT
FUNCTION FILEISOPEN RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
PROCEDURE FILEOPEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN/OUT
 OPEN_MODE                      BINARY_INTEGER          IN     DEFAULT
PROCEDURE FREETEMPORARY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
PROCEDURE FREETEMPORARY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
FUNCTION GETCHUNKSIZE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
FUNCTION GETCHUNKSIZE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
FUNCTION GETLENGTH RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
FUNCTION GETLENGTH RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
FUNCTION GETLENGTH RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
FUNCTION GET_STORAGE_LIMIT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
FUNCTION GET_STORAGE_LIMIT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
FUNCTION INSTR RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
 PATTERN                        RAW                     IN
 OFFSET                         NUMBER(38)              IN     DEFAULT
 NTH                            NUMBER(38)              IN     DEFAULT
FUNCTION INSTR RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
 PATTERN                        VARCHAR2                IN
 OFFSET                         NUMBER(38)              IN     DEFAULT
 NTH                            NUMBER(38)              IN     DEFAULT
FUNCTION INSTR RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
 PATTERN                        RAW                     IN
 OFFSET                         NUMBER(38)              IN     DEFAULT
 NTH                            NUMBER(38)              IN     DEFAULT
FUNCTION ISOPEN RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
FUNCTION ISOPEN RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
FUNCTION ISOPEN RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
FUNCTION ISTEMPORARY RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
FUNCTION ISTEMPORARY RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
PROCEDURE LOADBLOBFROMFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_BFILE                      BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN/OUT
 SRC_OFFSET                     NUMBER(38)              IN/OUT
PROCEDURE LOADCLOBFROMFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_BFILE                      BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN/OUT
 SRC_OFFSET                     NUMBER(38)              IN/OUT
 BFILE_CSID                     NUMBER                  IN
 LANG_CONTEXT                   NUMBER(38)              IN/OUT
 WARNING                        NUMBER(38)              OUT
PROCEDURE LOADFROMFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_LOB                        BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN     DEFAULT
 SRC_OFFSET                     NUMBER(38)              IN     DEFAULT
PROCEDURE LOADFROMFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_LOB                        BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN
 DEST_OFFSET                    NUMBER(38)              IN     DEFAULT
 SRC_OFFSET                     NUMBER(38)              IN     DEFAULT
PROCEDURE OPEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 OPEN_MODE                      BINARY_INTEGER          IN
PROCEDURE OPEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 OPEN_MODE                      BINARY_INTEGER          IN
PROCEDURE OPEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN/OUT
 OPEN_MODE                      BINARY_INTEGER          IN     DEFAULT
PROCEDURE READ
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN
 BUFFER                         RAW                     OUT
PROCEDURE READ
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN
 BUFFER                         VARCHAR2                OUT
PROCEDURE READ
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN
 BUFFER                         RAW                     OUT
FUNCTION SUBSTR RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
FUNCTION SUBSTR RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
FUNCTION SUBSTR RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
PROCEDURE TRIM
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 NEWLEN                         NUMBER(38)              IN
PROCEDURE TRIM
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 NEWLEN                         NUMBER(38)              IN
PROCEDURE WRITE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN
 OFFSET                         NUMBER(38)              IN
 BUFFER                         RAW                     IN
PROCEDURE WRITE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN
 OFFSET                         NUMBER(38)              IN
 BUFFER                         VARCHAR2                IN
PROCEDURE WRITEAPPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN
 BUFFER                         RAW                     IN
PROCEDURE WRITEAPPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN
 BUFFER                         VARCHAR2                IN

SQL> 
Re: LONG to CLOB in Java Stored Function [message #281429 is a reply to message #281422] Fri, 16 November 2007 17:26 Go to previous messageGo to next message
gthiruva
Messages: 9
Registered: November 2007
Junior Member
I'm not following. What is all of that output?

Perhaps another way of rephrasing the question is how do I (or can I) return a very long string from a java stored function? What if I just prototype my Java method to return a java.lang.String object?

Is there a way to craft a PL/SQL Java wrapper that receives the String object and somehow converts it to a CLOB or chops it up into pieces to send back to the caller?
Re: LONG to CLOB in Java Stored Function [message #281459 is a reply to message #281429] Sat, 17 November 2007 00:51 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What in my answer don't you understand?
Michel Cadot wrote on Fri, 16 November 2007 06:26

Anyway PL/SQL does not supports strings larger than 32K.
If you have a LONG/CLOB larger you have to use an external program and directly query this data.


Regards
Michel
Previous Topic: parasing csv (comma seperated)
Next Topic: calculate percent(toad oracle8.5)
Goto Forum:
  


Current Time: Wed Dec 07 04:59:17 CST 2016

Total time taken to generate the page: 0.08312 seconds