Export LOB datatype to flat file
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Export LOB datatype to flat file

Hybrid View

  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Unhappy Export LOB datatype to flat file

    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

  2. #2
    Join Date
    Dec 2000
    Posts
    138
    Just a piece of note: I havent tried this out

    Can you try creating a directory using
    CREATE DIRECTORY...
    and give permissions to the user

    or

    adding the directory to the init.ora file using utl_file_dir parameter and then try to do it.

    -dharma

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width