Create Java on Oracle database with JDBC

Question

I'm trying to create a Java source object on an oracle database using JDBC.

The source I want to create is the following:

create or replace and resolve java source named "BlobIO" as package dbjava;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.*;


public class BlobIO {

    /**
     * Stores a blob into a local file (or even UNC-path)
     * @param blob The blob locator that should be stored in a file
     * @param filename The filename to write to
     * @param bufferSize The buffer size for data transfer
     * @return 1 if successful, 0 if failed
     */
    public static int blobToFile(java.sql.Blob blob, String filename, int bufferSize)
    {
        OutputStream os = null;
        InputStream is = null;
        boolean fail = true;
        try {

            is = blob.getBinaryStream();
            os = new FileOutputStream(filename);
            int amountRead = 0;
            byte[] buffer = new byte[bufferSize];
            while ((amountRead = is.read(buffer, 0, buffer.length)) != -1) {
                os.write(buffer, 0, amountRead);
            }
            is.close();
            os.flush();
            os.close();
            fail = false;
        } catch (IOException ex) {
            new File(filename).delete();
            System.err.println("Could not store blob to file.");
            System.err.println("File : " + filename);
            System.err.println("Reason : " + ex.getClass().getName() + " : " + ex.getMessage());
            fail = true;
        } catch (SQLException ex) {
            new File(filename).delete();
            System.err.println("Could not store blob to file.");
            System.err.println("File : " + filename);
            System.err.println("Reason : " + ex.getClass().getName() + " : " + ex.getMessage());
            fail = true;
        } finally {
            try {is.close();} catch (Exception ex) {}
            try {os.flush();} catch (Exception ex) {}
            try {os.close();} catch (Exception ex) {}
        }
        return fail? 0:1;
    }

    /**
     * Stores a blob into a local file (or even UNC-path)
     * @param query The query that should select ONLY the blob field
     * @param filename The filename to write to
     * @param bufferSize The buffer size for data transfer
     * @return 1 if successful, 0 if failed
     */
    public static int blobToFile(String query, String filename, int bufferSize) {
        try {
            Connection conn = DriverManager.getConnection("jdbc:default:connection:");
            Statement stmt = conn.createStatement();
            ResultSet rset = stmt.executeQuery(query);
            InputStream is;


            if (rset.next())
            {
                int ret = blobToFile(rset.getBlob(1), filename, bufferSize);
                if (rset.next())
                {
                    new File(filename).delete();
                    System.err.println("Could not store blob to file.");
                    System.err.println("Blob query : " + query);
                    System.err.println("File : " + filename);
                    System.err.println("Reason : too many rows");
                    rset.close();
                    stmt.close();
                    return 0;
                } else {
                    rset.close();
                    stmt.close();
                    return ret;
                }
            } else {
                System.err.println("Could not store blob to file.");
                System.err.println("Blob query : " + query);
                System.err.println("File : " + filename);
                System.err.println("Reason : no records retrieved by query");
                rset.close();
                stmt.close();
                return 0;
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
            return 0;
        }

    }

}
/

I have tried with a CallableStatement using the execute method and this gives me the error: "Missing IN/OUT parameters" When I try using the execute method on a normal Statement object I get the error: "

Non supported SQL92 token at position: 262"

Anyone has any idea what I'm doing wrong? Can't seem to find anything on google either.

EDIT: This the code I use to try to execute the script (the String sql contains the script you can see above, the variable conn is the Connection object.

CallableStatement stat = conn.prepareCall(sql);
stat.setEscapeProcessing(false);
stat.execute();

If i try with just Statement it is this:

Statement stat = conn.createStatement();
stat.execute(sql);

Could you please post the complete code you are using to create the Java object (not only the source code of this Java object itself)? — Frank Schmitt
– Frank Schmitt, Commented Apr 22, 2013 at 7:56
@FrankSchmitt : This is the complete code, this is the script I'm trying to execute, do you mean the the Java code for JDBC then? — Sonaryr
– Sonaryr, Commented Apr 22, 2013 at 8:05

Sonaryr · Accepted Answer · 2013-04-26 12:59:06Z

8

Ok found the problem eventually, it needed to be CallableStatement with setEscapeProcessing(false).

answered Apr 26, 2013 at 12:59

Sonaryr

1,1222 gold badges10 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Realn0whereman Over a year ago

You're my savior.

vadipp Over a year ago

I tried this and it worked for some source files, but failed for others with Missing IN or OUT parameter at index:: 1. And then I found this: stackoverflow.com/a/21882264/501399 which worked for any files. So it should be a Statement, not a CallableStatement.

Vincent Malgrat · Accepted Answer · 2013-04-22 08:39:29Z

2

Please post a complete thorough example. Your code is working, following is the result on my 11.1.0.7.0 db. First the setup:

SQL> create or replace directory TMP as '/tmp';

Directory created

SQL> CREATE TABLE testBlob (a BLOB);

Table created

SQL> INSERT INTO testBlob VALUES (utl_raw.cast_to_raw('StackOverflow'));

1 row inserted

The first function:

SQL> CREATE OR REPLACE FUNCTION blob2File(p_blob BLOB,
  2                                       p_path VARCHAR2,
  3                                       p_buffer NUMBER)
  4    RETURN NUMBER AS
  5  LANGUAGE JAVA NAME
  6     'dbjava.BlobIO.blobToFile(java.sql.Blob,
  7                               java.lang.String,
  8                               int) return int';
  9  /

Function created

SQL> DECLARE
  2     l_blob   BLOB;
  3     l_return INT;
  4  BEGIN
  5     SELECT * INTO l_blob FROM testBlob;
  6     l_return := blob2File(l_blob, '/tmp/test.blob', 1024);
  7     dbms_output.put_line(l_return);
  8  END;
  9  /

1

For the second function:

SQL> CREATE OR REPLACE FUNCTION queryBlob2File(p_query VARCHAR2,
  2                                            p_path VARCHAR2,
  3                                            p_buffer NUMBER) RETURN NUMBER AS
  4  LANGUAGE JAVA NAME
  5     'dbjava.BlobIO.blobToFile(java.lang.String,
  6                               java.lang.String,
  7                               int) return int';
  8  /

Function created

SQL> DECLARE
  2     l_query  VARCHAR2(1000);
  3     l_return INT;
  4  BEGIN
  5     l_query := 'SELECT * FROM testBlob';
  6     l_return := queryBlob2File(l_query, '/tmp/test.blob', 1024);
  7     dbms_output.put_line(l_return);
  8  END;
  9  /

1

PL/SQL procedure successfully completed

You can use the UTL_FILE package to deal with files directly in PL/SQL:

SQL> DECLARE
  2     l_file utl_file.file_type;
  3     l_line VARCHAR2(1024);
  4  BEGIN
  5     l_file := utl_file.fopen(location => 'TMP',
  6                              filename => 'test.blob',
  7                              open_mode => 'R');
  8     utl_file.get_line(l_file, l_line);
  9     dbms_output.put_line(l_line);
 10     utl_file.fclose(l_file);
 11  END;
 12  /

StackOverflow

edited Apr 22, 2013 at 8:39

answered Apr 22, 2013 at 8:34

Vincent Malgrat

68k9 gold badges122 silver badges176 bronze badges

4 Comments

Sonaryr Over a year ago

I know my code is working, when I execute the script using sqlplus it is created, but not when I run the script the using JDBC.

Vincent Malgrat Over a year ago

How do you call a database java source from jdbc? Please post a THOROUGH example. I showed you how you can call it from PL/SQL. Since you can call PL/SQL scripts from jdbc I don't see where the problem is!

Sonaryr Over a year ago

I have told in my beginning post, I put the entire script in a java String variable and create an CallableStatement or Statement to execute it trough JDBC. I do not wish to call the "Java Source" using JDBC, I wish the create it using JDBC.

Vincent Malgrat Over a year ago

Oh sorry I didn't understand that you want to execute DDL with JDBC. My advice would be that some tools are more naturally suited to certain tasks: SQL*Plus is more suited to execute DDL scripts than JDBC. If you really want to use JDBC, I suggest you start with a very small example (one line class) and work your way from there.

Collectives™ on Stack Overflow

Create Java on Oracle database with JDBC

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related