Hi,
I am in the middle of migrating from Oracle to SQL Server - both on different platforms. Firstly, I have to export tables with BLOB datatypes. Just to summarise what I have done:
1) Table structure from which to export from:
DROP TABLE PDFS CASCADE CONSTRAINTS ;
CREATE TABLE PDFS (
ID NUMBER NOT NULL,
FILENAME VARCHAR2 (250),
CONTENT BLOB);
2) Procedure to export the data (Java):
CREATE OR REPLACE import java.lang.*;
import java.sql.*;
import oracle.sql.*;
import java.io.*;
public class BlobHandler
{
public static void ExportBlob(String myFile, BLOB myBlob) throws Exception
{
// Bind the image object to the database object
// Open streams for the output file and the blob
File binaryFile = new File(myFile);
FileOutputStream outStream = new FileOutputStream(binaryFile);
InputStream inStream = myBlob.getBinaryStream();
// Get the optimum buffer size and use this to create the read/write buffer
int size = myBlob.getBufferSize();
byte[] buffer = new byte[size];
int length = -1;
// Transfer the data
while ((length = inStream.read(buffer)) != -1)
{
outStream.write(buffer, 0, length);
outStream.flush();
}
// Close everything down
inStream.close();
outStream.close();
}
};
/
3) Oracle procedure :
CREATE OR REPLACE PROCEDURE ExportBlob (p_file IN VARCHAR2,
p_blob IN BLOB)
AS LANGUAGE JAVA
NAME 'BlobHandler.ExportBlob(java.lang.String, oracle.sql.BLOB)';
/
4) CREATE OR REPLACE PROCEDURE RUN_EXPORTBLOB IS
BEGIN
DECLARE
v_blob BLOB;
BEGIN
SELECT CONTENT
INTO v_blob
FROM PDFS;
ExportBlob('c:\hometrial\mycopy.pdf',v_blob);
END;
END;
/
5) Attempt to run this procedure:
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
c:\hometrial\mycopy.pdf write) has not been granted by
dbms_java.grant_permission to
SchemaProtectionDomain(SAMP|PolicyTableProxy(SAMP))
ORA-06512: at "SAMP.EXPORTBLOB", line 0
ORA-06512: at "SAMP.RUN_EXPORTBLOB", line 9
ORA-06512: at line 1
6) This is despite me running:
EXEC Dbms_Java.Grant_Permission( -
'SAMP', -
'java.io.FilePermission', -
'C:\HOMETRIAL', -
'read ,write, execute, delete');
I have even tried to change the third parameter to be a lot of variations on 'C:\HOMETRIAL' ... including specifying the filenames.
Please provide exact problem. If any better and easier solution, then that would be greatly appreciated.
Thanks in advance,
Vivek
