Pushing a file to Oracle database to write to database filesystem

import java.sql.*;
import java.io.*;

public class sendFile {
  public static void main (String args[]) throws Exception {
    File file = new File("a.txt");
    FileInputStream fis = new FileInputStream(file);
    byte[] data = new byte[(int)file.length()];
    fis.read(data);
    fis.close();
    String s = new String(data, "UTF-8");
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection("user/pwd@dbhost:dbport/dbservice.domain");
    String t = "declare l_file utl_file.file_type utl_file.fopen('FOO','test.txt','w'); begin utl_file.put_line(l,?); end;";
    CallableStatement cstmt = conn.prepareCall(t);
    cstmt.setString(1,s);
    cstmt.execute();
  }

Large strings result in the following…

-bash-4.1$ time java sendFile
Exception in thread "main" java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
        at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)
        at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1041)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
        at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376)
        at sendFile.main(sendFile.java:16)

real    0m32.713s
user    0m2.076s
sys     0m1.185s
}

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.