1 Reply Latest reply: Apr 10, 2012 10:08 PM by 850247 RSS

    Unable to retrieve Blobs in 10g

    610112
      Hi Friends,

      Earlier I was using Oracle 9i (version 9.2.0.1.0) Database.
      (On Windows Server 2003 32 bit)
      And in my database I used BLOBs.

      Then, I upgraded Database to Oracle 10g (version 10.2.0.4.0)
      (On Windows Server 2008 32 bit)

      Now I'm unable to view the BLOBs.*

      I used the following codes to handle BLOBs with 9i
      ------------------------------------------------------------------------------------
      --Connect to system user and create Directories

      create or replace directory INSERT_PDF as 'F:\INSERT_PDF';
      create or replace directory DISPLAY_PDF as 'F:\DISPLAY_PDF';


      --Execute Java files / Procedures for granting permission

      GRANT ALL ON DIRECTORY DISPLAY_PDF TO ABHIDB;
      GRANT ALL ON DIRECTORY INSERT_PDF TO ABHIDB;

      EXEC Dbms_Java.Grant_Permission('ABHIDB','SYS:java.io.FilePermission', 'F:\DISPLAY_PDF\*','read,write,execute,delete');
      EXEC dbms_java.grant_permission('ABHIDB','SYS:java.lang.RuntimePermission','writeFileDescriptor', null);
      EXEC dbms_java.grant_permission('ABHIDB','SYS:java.lang.RuntimePermission','readFileDescriptor', null);
      EXEC dbms_java.grant_permission('ABHIDB','SYS:java.util.PropertyPermission','F:\DISPLAY_PDF\*','read,write');


      --Connect to AbhiDB user on SQL prompt

      --Create Java source file for BlobHandler

      create or replace java source named "BlobHandler" as
      import java.lang.*;
      import java.sql.*;
      import java.sql.*;
      import oracle.sql.*;
      import java.io.*;
      public class Blobhandler
      {
      public static void ExportBlob(String myFile,BLOB myblob) throws Exception
      {
      File binaryFile = new File(myFile);
      FileOutputStream outstream = new FileoutputStream(binaryFile);
      inputStream instream = myblob.getBinaryStream();
      int size = myblob.getBuffersize();
      byte[] buffer = new byte[size];
      int length = -1;
      while((length = instream.read(buffer))!= -1)
      {
      outstrem.write(buffer,0,length);
      outstream.flush();
      }
      instream.close();
      outstream.close();
      }
      };

      --Create Procedure for ExportBlob

      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)';
      -------------------------------------------------------------------------------------------------

      can anyone help?