Read a text file in Unix

[Greg Mendez]

The java program stays in a windows box.
The text file stays in a unix box and Oracle also stays in the same unix box.
So the java program wants the content of the text file.

I can do this via JDBC
LOOP
BEGIN
utl_file.get_line(vInHandle, vNewLine);
dbms_output.put_line(vNewLine);
EXCEPTION
WHEN OTHERS THEN
EXIT;
END;
END LOOP;

I can use VARCHAR2, but it has limits. Only 4k size.
So how exactly can java gets the content?
By the way, no ftp, or share drive exists.

[Rene Larsen]

You can use a CLOB instead of VARCHAR2

[Greg Mendez]

Thanks Rene. Works great.
Here is the code if anyone wants to see it.

      public String read() throws Exception {

        Connection conn = getOracleConnection();
        CallableStatement cs = conn.prepareCall("{? = call ReadFile}");
        cs.registerOutParameter(1, Types.CLOB);
        cs.execute();
        Clob c = cs.getClob(1);
        int length = (int) c.length();
        String ret = c.getSubString(1, length);
        return ret;
    }  

Here is the code in oracle...

  
create or replace function ReadFile
return clob
is

 vDirectory 	 varchar2(50) := 'mydirectory/abc/';
 vFileName 	 varchar2(250) = 'textfile.txt';
 vInHandle utl_file.file_type;
 vNewLine  varchar2(250);

 clobvar clob := EMPTY_CLOB;

begin

    vInHandle := utl_file.fopen(vDirectory, vFileName, 'R');
    
    dbms_lob.createtemporary(clobvar, true);
    dbms_lob.open(clobvar, dbms_lob.lob_readwrite);
    
    -- loop text file
    loop
        begin
            utl_file.get_line(vInHandle, vNewLine);
            dbms_lob.writeappend (clobvar, length(vNewLine), vNewLine);
        exception
            when OTHERS then
            exit;
        end;
    end loop;
    utl_file.fclose(vInHandle);
    
    dbms_lob.close(clobvar);
    
    return clobvar;
end;