retrieving BLOB from Oracle

Discussion in 'ASP .Net' started by matt@mailinator.com, Dec 1, 2006.

  1. Guest

    hello,

    im doing my first ASP.NET app that inserts & retrieves files from
    Oracle (no need for a discussion on *that*!).

    i learned first-hand of the somewhat awkward technique for inserting
    binary data into an Oracle BLOB column via ADO.NET. since my files are
    larger than 33k, it seemed had to use this technique:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;322796

    ....in which you fill an OracleLob object and pass it into your BLOB
    column (via a proc in my case).


    now i have to retrieve my blob file, and response it to the user. i
    imagine the process is something like: 1) get data out of db, 2)
    convert to working format 3) response to client.

    however, im running into hitches. not sure what is wrong. can anyone
    post snippets of working code for doing this? for retrieval, heres my
    snippet:


    //conn & command
    OracleConnection conn = new
    OracleConnection(ConfigurationSettings.AppSettings["connStr"]);
    OracleCommand command = new
    OracleCommand("COFE.GetInspectionReportByID", conn);
    command.CommandType = CommandType.StoredProcedure;

    //params
    command.Parameters.Add("p_fileID", OracleType.Number).Value = reportID;
    command.Parameters.Add("cur_results", OracleType.Cursor).Direction =
    ParameterDirection.Output; //output

    OracleLob blob = null;

    //fill blob
    conn.Open();
    OracleDataReader dr = command.ExecuteReader();

    if (dr.Read()) //has row
    blob = dr.GetOracleLob(5);

    byte[] byteArray = (byte[])blob.Value;

    conn.Close();

    //response (kitchen sink for tests)
    Response.Clear();
    Response.ClearContent();
    Response.ClearHeaders();
    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("content-disposition", "attachment; filename=" +
    report.FileName);
    Response.BufferOutput = true;

    Response.BinaryWrite(byteArray);
    Response.End();


    ....however, when excel loads the file, it says its an "unrecognizable
    format". opening it in notepad, its empty. *but*, right-clicking the
    file, its size on disk matches the pre-databased version exactly!


    any idea what im missing?


    thanks!
    matt
     
    , Dec 1, 2006
    #1
    1. Advertising

  2. Guest

    anybody? anybody know how to retrieve a binary file out of an Oracle
    BLOB column via a proc? c'mon, somebody has to know.....

    thanks,
    matt
     
    , Dec 4, 2006
    #2
    1. Advertising

  3. RobinS Guest

    If nobody here seems to have an answer for you, maybe you could
    post this to an Oracle group?

    Sorry I can't help you.
    Robin S.
    ----------------------------------
    <> wrote in message
    news:...
    > anybody? anybody know how to retrieve a binary file out of an Oracle
    > BLOB column via a proc? c'mon, somebody has to know.....
    >
    > thanks,
    > matt
    >
     
    RobinS, Dec 4, 2006
    #3
  4. Guest

    RobinS wrote:
    > If nobody here seems to have an answer for you, maybe you could
    > post this to an Oracle group?


    yeah thats my next stop. i just figured since this app-code (.NET)
    dependant, and not really an Oracle issue, that the .NET groups would
    be most on-topic.

    thanks,
    matt
     
    , Dec 4, 2006
    #4
  5. Guest

    ....i tested this w/ another file format, .PDF. same thing -- the byte
    array of the retrieved file matches the original's size-on-disk. when
    its saved via the response to disk, it also matches the orig.

    however, opening it is impossible. opening it in notepad yields an
    empty file, devoid of the normal binary garbage characters.

    if somebody could help me confirm the insert/retrieve techniques, id
    likely be golden.


    matt
     
    , Dec 4, 2006
    #5
  6. nil Guest

    wrote:
    > hello,
    >
    > im doing my first ASP.NET app that inserts & retrieves files from
    > Oracle (no need for a discussion on *that*!).
    >
    > i learned first-hand of the somewhat awkward technique for inserting
    > binary data into an Oracle BLOB column via ADO.NET. since my files are
    > larger than 33k, it seemed had to use this technique:
    >
    > http://support.microsoft.com/default.aspx?scid=kb;en-us;322796
    >
    > ...in which you fill an OracleLob object and pass it into your BLOB
    > column (via a proc in my case).
    >
    >
    > now i have to retrieve my blob file, and response it to the user. i
    > imagine the process is something like: 1) get data out of db, 2)
    > convert to working format 3) response to client.
    >
    > however, im running into hitches. not sure what is wrong. can anyone
    > post snippets of working code for doing this? for retrieval, heres my
    > snippet:
    >
    >
    > //conn & command
    > OracleConnection conn = new
    > OracleConnection(ConfigurationSettings.AppSettings["connStr"]);
    > OracleCommand command = new
    > OracleCommand("COFE.GetInspectionReportByID", conn);
    > command.CommandType = CommandType.StoredProcedure;
    >
    > //params
    > command.Parameters.Add("p_fileID", OracleType.Number).Value = reportID;
    > command.Parameters.Add("cur_results", OracleType.Cursor).Direction =
    > ParameterDirection.Output; //output
    >
    > OracleLob blob = null;
    >
    > //fill blob
    > conn.Open();
    > OracleDataReader dr = command.ExecuteReader();
    >
    > if (dr.Read()) //has row
    > blob = dr.GetOracleLob(5);
    >
    > byte[] byteArray = (byte[])blob.Value;
    >
    > conn.Close();
    >
    > //response (kitchen sink for tests)
    > Response.Clear();
    > Response.ClearContent();
    > Response.ClearHeaders();
    > Response.ContentType = "application/vnd.ms-excel";
    > Response.AddHeader("content-disposition", "attachment; filename=" +
    > report.FileName);
    > Response.BufferOutput = true;
    >
    > Response.BinaryWrite(byteArray);
    > Response.End();
    >
    >
    > ...however, when excel loads the file, it says its an "unrecognizable
    > format". opening it in notepad, its empty. *but*, right-clicking the
    > file, its size on disk matches the pre-databased version exactly!
    >
    >
    > any idea what im missing?
    >
    >
    > thanks!
    > matt





    hey can u tell me how can i save the image in the oracle in blob column
    and retrieve from that?thanks in advance...
    nil
     
    nil, Dec 5, 2006
    #6
  7. Guest

    wrote:
    > ...however, when excel loads the file, it says its an "unrecognizable
    > format". opening it in notepad, its empty. *but*, right-clicking the
    > file, its size on disk matches the pre-databased version exactly!


    figured it out. it had nothing to w/ my retrieval code.

    the files were coming out of oracle w/ the correct byte size, but empty
    in notepad, because i inserted them incorrectly.

    for the insert, i had *sized* the byte array prior to blob-and-insert,
    but i hadnt *filled* it! so the insert worked, the retrieval worked,
    but the file contents were simply empty.


    matt
     
    , Dec 5, 2006
    #7
  8. Guest

    nil wrote:
    > hey can u tell me how can i save the image in the oracle in blob column
    > and retrieve from that?thanks in advance...
    > nil


    well, first follow the link in my OP. if youre using the ms
    dataprovider (System.Data.OracleClient), and not ODP.NET (oracle's data
    provider) then its how you do the blob shuffle.

    getting the file *out* was much simpler for me:

    //conn & command
    OracleConnection conn = new
    OracleConnection(ConfigurationSettings.AppSettings["connStr"]);
    OracleCommand command = new OracleCommand("Foo.GetFileByID", conn);
    command.CommandType = CommandType.StoredProcedure;

    //params
    command.Parameters.Add("p_reportID", OracleType.Number).Value =
    reportID;
    command.Parameters.Add("cur_results", OracleType.Cursor).Direction =
    ParameterDirection.Output; //output

    //do it
    DataSet ds = new DataSet();
    OracleDataAdapter da = OracleClient.OracleDataAdapter(command);
    da.Fill(ds);

    //get file contents from returned data
    byte[] fileContents = (byte[])row["FileContents"];

    Response.BinaryWrite(fileContents );


    matt
     
    , Dec 5, 2006
    #8
  9. nil Guest

    wrote:
    > nil wrote:
    > > hey can u tell me how can i save the image in the oracle in blob column
    > > and retrieve from that?thanks in advance...
    > > nil

    >
    > well, first follow the link in my OP. if youre using the ms
    > dataprovider (System.Data.OracleClient), and not ODP.NET (oracle's data
    > provider) then its how you do the blob shuffle.
    >
    > getting the file *out* was much simpler for me:
    >
    > //conn & command
    > OracleConnection conn = new
    > OracleConnection(ConfigurationSettings.AppSettings["connStr"]);
    > OracleCommand command = new OracleCommand("Foo.GetFileByID", conn);
    > command.CommandType = CommandType.StoredProcedure;
    >
    > //params
    > command.Parameters.Add("p_reportID", OracleType.Number).Value =
    > reportID;
    > command.Parameters.Add("cur_results", OracleType.Cursor).Direction =
    > ParameterDirection.Output; //output
    >
    > //do it
    > DataSet ds = new DataSet();
    > OracleDataAdapter da = OracleClient.OracleDataAdapter(command);
    > da.Fill(ds);
    >
    > //get file contents from returned data
    > byte[] fileContents = (byte[])row["FileContents"];
    >
    > Response.BinaryWrite(fileContents );
    >
    >
    > matt


    should i follow the same thing when i want to retrieve image from the
    database and what if i am using oracle's provider like msdaora.1.....
    so is there any way to do for this provider....thanks a lot for reply
     
    nil, Dec 6, 2006
    #9
  10. Guest

    nil wrote:
    > should i follow the same thing when i want to retrieve image from the
    > database and what if i am using oracle's provider like msdaora.1.....
    > so is there any way to do for this provider....thanks a lot for reply


    dunno, dude. research time.
     
    , Dec 6, 2006
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Robert Vabo

    .NET and Oracle BLOB

    Robert Vabo, Aug 20, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    6,063
    Mark Kamoski
    Aug 27, 2003
  2. nkunapa
    Replies:
    4
    Views:
    6,752
    nkunapa
    Aug 10, 2005
  3. gbattine
    Replies:
    9
    Views:
    605
    Moiristo
    Jun 29, 2006
  4. Peter Afonin

    Retrieving Oracle BLOB

    Peter Afonin, Jul 25, 2006, in forum: ASP .Net
    Replies:
    11
    Views:
    1,702
    Peter Afonin
    Jul 27, 2006
  5. Feyruz
    Replies:
    4
    Views:
    2,298
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page