Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Variable sizing problem, using Java in Oracle

    We're using java (stored in the database) to read in the names of all the files stored in a particular folder. All of these names are actually stored in a single java.lang.String variable, delimited by "|", and then we parse through the string in PL/SQL to create a list.

    When the java.lang.String variable reaches a length of 32767+1, we get the error

    Code:
    ORA-24345: A Truncation or null fetch error occurred

    Initially, we were passing the return string from the java function into a VARCHAR2(32767), which we later changed to a CLOB when we noticed the relationship of the actual string length and the length attribute of the VARCHAR2. But, even with the CLOB, we're getting the same error.

    So now I'm kind of confused. The error indicates that the PL/SQL variable I'm using to store the return value is too small. But, I can prove that I can store more than 32767 bytes in a CLOB:

    Code:
    declare
     l_clob clob;
    begin
     for i in 1..32768
     loop
       l_clob := l_clob || '1';
     end loop;
    end;

    Here's the java code we're using

    Code:
    import java.io.File;
    public class JFile {
    /***************** Modification History **************************
     || from O'Reilly website
    ******************************************************************/
     
       public static String dirContents (String dir, String delim) {
          File myDir = new File (dir);
          String[] filesList = myDir.list();
          String contents = new String();
          for (int i = 0; i < filesList.length; i++)
             contents = contents + filesList[i] + delim; // Utrecht 4/2002
          return contents;
       }
    };

    And the package code, minimized to get straight to the error (and, I've got the CLOB here, rather than the VARCHAR2(32767):

    Code:
    create or replace
    PACKAGE               JFILE
    AS
      FUNCTION DIR_CONTENTS (dir IN VARCHAR2, delim IN VARCHAR2) RETURN VARCHAR2;
      PROCEDURE GET_DIR_CONTENTS (dir_obj_name IN VARCHAR2, delim IN VARCHAR2 := NULL);
    END JFILE;
    / 
    create or replace
    PACKAGE BODY                      JFILE_CWF 
    AS
      FUNCTION DIR_CONTENTS (dir IN VARCHAR2, delim IN VARCHAR2) 
        RETURN VARCHAR2
      AS 
      LANGUAGE JAVA
        NAME 'JFile.dirContents (java.lang.String, java.lang.String) 
          return java.lang.String';    
     
      PROCEDURE GET_DIR_CONTENTS (dir_obj_name IN VARCHAR2, delim IN VARCHAR2 := NULL)
      IS
        dir_path   VARCHAR2(1000);
        delimiter  VARCHAR2(10);
        file_list  CLOB;
     
      BEGIN
    	
        SELECT directory_path
        INTO dir_path
        FROM all_directories
        WHERE directory_name = dir_obj_name;
    	
        IF NVL(delim, ' ') = ' ' THEN
          delimiter := '|';
        END IF;
    	
        file_list := DIR_CONTENTS (dir_path, delimiter);
     
      END GET_DIR_CONTENTS;
    END JFILE;
    /

    The error occurs at "file_list := DIR_CONTENTS (dir_path, delimiter);"

    Thanks for your help,
    --=Chuck

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Try changing the 'return' type to "CLOB" instead of 'VARCHAR2' as in:
    Code:
      FUNCTION DIR_CONTENTS (dir IN VARCHAR2, delim IN VARCHAR2) 
        RETURN CLOB
      AS

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    I hadn't even seen that! It was such a simple oversight, thanks for noticing.

    Unfortunately though, after that change, I got a data conversion error:

    Code:
    ORA-00932: inconsistent datatypes: expected a return value that is convertible to an Oracle CLOB got a Java java.lang.String
    --=cf

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    I found some code on the AskTom website which looks like it's working pretty well. Instead of having Java return a String, you have it actually perform the inserts to the Global Temporary table. Here's the code from Tom Kyte's website that I started with:

    Code:
    ops$tkyte@ORA10GR2> create global temporary table DIR_LIST
      2  ( filename varchar2(255) )
      3  on commit delete rows
      4  /
    
    ops$tkyte@ORA10GR2>
    ops$tkyte@ORA10GR2> create or replace and compile java source named "DirList"
      2  AS
      3     import java.io.*;
      4     import java.sql.*;
      5     import oracle.sql.*;
      6     import oracle.jdbc.*;
      7     public class DirList
      8  {
      9     public static void getList(String directory) throws SQLException
     10     {
     11     File path = new File( directory );
     12     String[] list = path.list();
     13     String element;
     14             for(int i = 0;i < list.length; i++)
     15             {
     16               element = list[i];
     17               #sql{ Insert into DIR_LIST (FILENAME) values (:element) };
     18             }
     19    }
     20  };
     21  /
    
    ops$tkyte@ORA10GR2> create or replace procedure get_dir_list( p_directory in varchar2 )
      2  as language java
      3  name 'DirList.getList( java.lang.String )';
      4  /
    
    ops$tkyte@ORA10GR2> exec get_dir_list( '/tmp' );
    
    ops$tkyte@ORA10GR2> select * from dir_list;
    
    FILENAME
    -------------------------------------------------------------------------------
    jd_sockV4
    .iroha_unix
    .font-unix

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •