[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2054
  • Last Modified:

Read BLOB data from oracle through a stored procedure

I have a column with blob data type in the oracle data base. i wrote a stored procedure which take an id value as in parameter and give blob value as out parameter.
From .net side i use the following code

OracleConnection conn = new OracleConnection(connnectionstring);
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procedure;
            cmd.Parameters.Add("SEQ_NO", OracleType.VarChar).Value = key;
            cmd.Parameters.Add("FILE_DATA", OracleType.Blob).Direction = ParameterDirection.Output;
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch
            {

            }
            filedata = (byte[])cmd.Parameters["FILE_DATA"].Value;
            return filedata;

Its giving me error that it con not convert oraclelob to byte[].
Any ideas please
0
psdeepu545
Asked:
psdeepu545
  • 2
1 Solution
 
teebonCommented:
Hi psdeepu545,

I modified your code based on the example at http://www.akadia.com/services/dotnet_orablobs.html:
(the code is not tested)

// Cast BLOB data using OracleBlob type
OracleBlob blob = (OracleBlob)cmd.Parameters["FILE_DATA"].Value;

// Create a byte array of the size of the Blob obtained
Byte[] filedata =  new Byte[blob.Length];

// Read blob data into byte array
blob.Read(filedata,0,System.Convert.ToInt32(blob.Length));

return filedata;

Open in new window

0
 
psdeepu545Author Commented:
There is no oracleblob class actually. i used dataset to get the blob and filled with dataadapter.
by default when blob is stored in dataset it will store as a byte array.
0
 
teebonCommented:
You may download the Oracle 11g ODAC 11.1.0.7.20 with Oracle Developer Tools for Visual Studio:

http://www.oracle.com/technology/software/tech/windows/odpnet/index.html

Then, you can include this line in your class:

 using Oracle.DataAccess.Types;
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now