Tom,
Only the insert with empty_blob() worked with sql developer. I mentioned that just to show that the access to the procedure schema is correct.
Please find the simple java code to try the BLOB issue with EBR. This program loads an image to blob table.
Java Code
---------
import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class TestBlobWrite
{
//java TestBlobWrite jdbc:oracle:thin:@192.168.2.53:1521:panorama IRESEBRTBLS IRESEBRTBLS D:\Apr11.jpeg
public static void main(String args[]) {
Connection conn = null;
PreparedStatement stmt1 = null;
PreparedStatement stmt2 = null;
OutputStream os =null;
ResultSet rset = null;
try {
if(args.length != 4) {
System.out.println("\nCheck Arguments!! \n\nUsage:java TestBlobWrite <jdbc:oracle:thin:@IP:PORT:SID> <username> <password> <file-path>");
System.exit(1);
}
Class.forName ("oracle.jdbc.driver.OracleDriver").newInstance();
conn = DriverManager.getConnection(args[0], args[1], args[2]);
conn.setAutoCommit(false);
System.out.println("Got Connection ..");
File inputBinaryFile = new File(args[3]);
stmt1 = conn.prepareStatement("INSERT INTO test_blob (id, image_name, image, timestamp) VALUES(2, '" + inputBinaryFile.getName() + "', EMPTY_BLOB(), SYSDATE)");
stmt1.executeUpdate();
stmt2 = conn.prepareStatement("SELECT image FROM test_blob WHERE id = 2 FOR UPDATE");
rset = stmt2.executeQuery();
rset.next();
Blob image = (rset).getBlob("image");
os = image.setBinaryStream(1);
ByteArrayOutputStream bytesOut = new ByteArrayOutputStream();
ObjectOutputStream out = new ObjectOutputStream(bytesOut);
out.writeObject(inputBinaryFile);
out.flush();
out.close();
bytesOut.close();
byte[] bytes = bytesOut.toByteArray();
os.write(bytes);
os.flush();
conn.commit();
System.out.println("Insert Committed...");
os.close();
rset.close();
stmt1.close();
stmt2.close();
conn.close();
System.out.println("Disconnected...");
} catch(Exception e) {
e.printStackTrace();
if (conn != null) {
try {
conn.rollback();
System.out.println("Exception and Insert Rolled back...");
} catch(Exception ex){}
}
}
}
}
To compile
----------
set path=C:\Program Files\Java\jdk1.6.0\bin;
set classpath=D:\Applns\oracle11gXE\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc5.jar;
javac TestBlobWrite.java
To run
-------
create two schemas EBRTBLS and EBRPROCS
conn sys/manager123@orcl as sysdba
create user EBRTBLS identified by EBRTBLS;
grant connect,resource to EBRTBLS;
create user EBRPROCS identified by EBRPROCS;
grant connect,resource to EBRPROCS;
CONN EBRTBLS/BRTBLS@ORCL
CREATE TABLE test_blob (
id NUMBER(15)
, image_name VARCHAR2(1000)
, image BLOB
, timestamp DATE
);
GRANT INSERT,UPDATE,DELETE,SELECT ON test_blob TO EBRPROCS;
CONN EBRPROCS/EBRPROCS@ORCL
CREATE SYNONYM test_blob FOR EBRTBLS.test_blob;
Run the java program (replace the connection details, schema details and the path of the file name D:\20110206_024.jpg)
--------------------
set path=C:\Program Files\Java\jdk1.6.0\bin;
set classpath=D:\Applns\oracle11gXE\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc5.jar;
java TestBlobWrite jdbc:oracle:thin:@localhost:1521:ORCL EBRPROCS EBRPROCS D:\20110206_024.jpg
This works connecting to EBRPROCS schema which refers the synonym of blob table.
Now execute the following to create editions on blob table
CONN EBRTBLS/BRTBLS@ORCL
REVOKE INSERT,UPDATE,DELETE,SELECT ON test_blob FROM EBRPROCS;
RENAME test_blob TO test_blob_1;
CREATE OR REPLACE EDITIONING VIEW test_blob AS SELECT * FROM test_blob_1;
GRANT INSERT,UPDATE,DELETE,SELECT ON test_blob TO EBRPROCS;
Run the same java program (replace the connection details, schema details and the path of the file name D:\20110206_024.jpg)
---------------------------
set path=C:\Program Files\Java\jdk1.6.0\bin;
set classpath=D:\Applns\oracle11gXE\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc5.jar;
java TestBlobWrite jdbc:oracle:thin:@localhost:1521:ORCL EBRPROCS EBRPROCS D:\20110206_024.jpg
The following exeption is thrown
---------------------------------
Got Connection ..
java.sql.SQLException: ORA-00942: table or view does not exist
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3
576)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:365
7)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWr
apper.java:1062)
at TestBlobWrite.main(TestBlobWrite.java:28)
Exception and Insert Rolled back...
If the grant on base table is given this works
CONN EBRTBLS/BRTBLS@ORCL
GRANT INSERT,UPDATE,DELETE,SELECT ON test_blob_1 TO EBRPROCS;
Run the same java program (replace the connection details, schema details and the path of the file name D:\20110206_024.jpg)
---------------------------
set path=C:\Program Files\Java\jdk1.6.0\bin;
set classpath=D:\Applns\oracle11gXE\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc5.jar;
java TestBlobWrite jdbc:oracle:thin:@localhost:1521:ORCL EBRPROCS EBRPROCS D:\20110206_024.jpg