PL/SQL : a procedure to Save content of BLOB procedure parameter onto the file system. c#

1) Create your Oracle Directory

 
CREATE OR REPLACE DIRECTORY RIDDELIMPORT AS 'c:\RIDDELIMPORT'
/
CREATE OR REPLACE DIRECTORY RIDDELEXPORT AS 'c:\RIDDELEXPORT'
/

2) Create the store procedure

 
PROCEDURE WriteBlobFile
(
   OracleDirectory IN VARCHAR2,
   FileName        IN VARCHAR2,
   filelob    BLOB
)
-- -------------------------------------------
-- Procedure to dump a BLOB column onto a file
-- -------------------------------------------
-- parameters:
-- PC$Directory : name of an existing Oracle Directory
-- PC$File_Name : name of the expected output file
-- PC$SQL_Order : SQL order to select the BLOB column
-- PB$Raise     : boolean to indicate if the process
--                would be stopped after an error
--
IS
  --src_lob    BLOB;
  buffer     RAW(16384);
  amt        BINARY_INTEGER := 16384;
  pos        INTEGER := 1;
  myfile     UTL_FILE.FILE_TYPE ;
  LC$Msg     VARCHAR2(2000) ;


BEGIN

-- get the BLOB column --
/*BEGIN
  --EXECUTE IMMEDIATE PC$SQL_Order INTO src_lob ;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    LC$Msg := 'Write_Binary_File(): NO_DATA_FOUND' ;
    IF PB$Raise THEN
      RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
    END IF ;
    Dbms_Output.Put_Line(LC$Msg) ;
  RETURN ;
END ;*/

-- open the output file --
myfile := UTL_FILE.FOPEN( OracleDirectory, FileName, 'WB'); ---, 32764 ) ; WB Sta per write byte

-- write the file --
LOOP
  -- read the chunks --
  Dbms_Lob.READ (filelob, amt, pos, buffer);
  -- write the chunks --
  Utl_File.Put_Raw(myfile, buffer);
  pos := pos + amt;
END LOOP;
-- close the file --
Utl_File.Fclose(myfile);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    utl_file.fclose(myfile);
  WHEN OTHERS THEN
/*    LC$Msg := 'Write_Binary_File() Error : ' || TO_CHAR( SQLCODE ) || ' while managing file ('
|| PC$Directory || ') ' || PC$File_Name ;
    IF PB$Raise THEN
      RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
    END IF ;*/
    utl_file.fclose(myfile);
    Dbms_Output.Put_Line('altroerrore');
END WriteBlobFile; 

3) Call the procedure from C#

 
            string constr = "User Id=OracleUser;Password=OracleUserPassword;Data Source=ORCL";

            OracleConnection con = new OracleConnection(constr);
            con.Open();
           
            String SourceLoc = @"c:\temp\arkabi.txt";
            String DestinationLoc = @"c:\temp\arkabi2.txt";
          


            FileStream fs = new FileStream(SourceLoc, FileMode.Open, FileAccess.Read);

            
            
            BinaryReader br = new BinaryReader(fs);
            

            // Create a byte array of file stream length
            byte[] fileData = new byte[fs.Length];

            br.Read(fileData, 0, System.Convert.ToInt32(fs.Length));

            ////fsw.Write(fileData, 0, System.Convert.ToInt32(fs.Length));
            //fsw.Close();

            //Read block of bytes from stream into the byte array
            //fs.Read(fileData, 0, System.Convert.ToInt32(fs.Length));




            //Close the File Stream
            fs.Close();

            // Set command to create Anonymous PL/SQL Block
            OracleCommand cmd = new OracleCommand();
            cmd.CommandText = "WriteBlobFile";
            cmd.Connection = con;


            // Since executing an anonymous PL/SQL block, setting the command type
            // as Text instead of StoredProcedure
            cmd.CommandType = CommandType.StoredProcedure;


//////////// PROCEDURE WriteBlobFile
////////////(
////////////   OracleDirectory IN VARCHAR2,
////////////   FileName        IN VARCHAR2,
////////////   filelob    BLOB
////////////)

            OracleParameter pOracleDirectory = cmd.Parameters.Add("OracleDirectory", OracleDbType.Varchar2);
            pOracleDirectory.Direction = ParameterDirection.Input;
            pOracleDirectory.Value = "RIDDELIMPORT";

            OracleParameter pFileName = cmd.Parameters.Add("FileName", OracleDbType.Varchar2);
            pFileName.Direction = ParameterDirection.Input;
            pFileName.Value = "arkabi3.txt";

            // Bind the parameter as OracleDbType.Blob to command for inserting image
            OracleParameter Pfilelob = cmd.Parameters.Add("filelob", OracleDbType.Blob);
            Pfilelob.Direction = ParameterDirection.Input;
            Pfilelob.Value = fileData;

            cmd.ExecuteNonQuery();