Oracle Database Discussions

Hi all,

With reference to the example on "How to: Read and Write BLOB Data to a Database Table Through an Anonymous PL/SQL Block" on http://otn.oracle.com/sample_code/tech/windows/odpnet/howto/anonyblock/index.html I have a question and would be really greatful if someone could give me the solution. In the example, it shows the read and write immediately after one another.

****************************************************************************************

' Step 3
' Create Anonymous PL/SQL block string
Dim block As String =
" BEGIN " & _
" INSERT INTO testblob (id, photo) VALUES (100, :1) ;" & _
" SELECT photo into :2 from testblob WHERE id = 100 ;" & _
" end ;"

' Set command to create Anonymous PL/SQL Block
Dim cmd As OracleCommand = New OracleCommand()
cmd.CommandText = block
cmd.Connection = con


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

' Step 4
' Setting Oracle parameters

' Bind the parameter as OracleDbType.Blob
' to command for inserting image
Dim param As OracleParameter = cmd.Parameters.Add("blobtodb", OracleDbType.Blob)
param.Direction = ParameterDirection.Input

****************************************************************************************

These 2 steps establish that Parameters(1) contain the data pertaining to the location of the photo data. This Parameter is then subsequently used IMMEDIATELY afterwards to retrieve the image and store it in a file on the system:

****************************************************************************************

' Step 6
' Save the retrieved image to the DestinationLoc in the file system
' Create a byte array
Dim byteData As Byte()
Dim Paramvalue As OracleBlob
Paramvalue = cmd.Parameters(1).Value

' fetch the value of Oracle parameter into the byte array
byteData = CType((Paramvalue.Value), Byte())

' get the length of the byte array
Dim ArraySize As Integer = New Integer()
ArraySize = byteData.GetUpperBound(0)

****************************************************************************************

What do I need to do, if I need to retrieve this data in another script altogther? I will obviously not have access to "Parameter(1)" and will have to get this data, presumably from the BLOB data stored in the database, ithis case a table called "testblob". I guess my question is, provided I need to do the exact samething, i.e. stream the BLOB data stored inside this table, what would I need to do. The SQL statement would probably look

select photo from testblob where WHERE id = 100;

How do I then get the result into VB .NET environment so that I can display the image as a JPEG?

My apologies if this is rather trivial - I'm very new at this stuf...... Thanks for all your help.