Imports System
Imports System.Data
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.IO
Imports System.Text
 
Module Module1
 
    Sub Main()
 
        'Step 1
        ' Connect to database
        ' Note: Modify User Id, Password, Data Source as per your database setup
        Dim constr As String = "User Id=scott;Password=tiger;Data Source=orcl9i"
        Dim con As OracleConnection = New OracleConnection(constr)
        con.Open()
        Console.WriteLine("Connected to database!")
 
        ' Step 2
        ' Note: Modify the Source and Destination location
        ' of the image as per your machine settings
        Dim SourceLoc As String = "D:/Images/photo.jpg"
        Dim DestinationLoc As String = "d:/Images/TestImage.jpg"
 
        ' providing read access to the file
        Dim fs As FileStream = New FileStream(SourceLoc, FileMode.Open, FileAccess.Read)
 
        ' Create a byte array of file stream length
        Dim ImageData As Byte()
        ReDim ImageData(fs.Length)
 
        'Read block of bytes from stream into the byte array
        fs.Read(ImageData, 0, System.Convert.ToInt32(fs.Length))
 
        'Close the File Stream
        fs.Close()
 
 
        ' Step 3
        ' Creating 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
 
        ' Assigning Byte Array to Oracle Parameter
        param.Value = ImageData
 
        ' Bind the parameter as OracleDbType.Blob to command for retrieving the image
        Dim param2 As OracleParameter = cmd.Parameters.Add("blobfromdb", OracleDbType.Blob)
        param2.Direction = ParameterDirection.Output
 
 
        Try
 
            ' Step 5
            ' Execute the Anonymous PL/SQL Block. The anonymous PL/SQL block inserts the
            ' image to the database and then retrieves the images as an output parameter
            cmd.ExecuteNonQuery()
            Console.WriteLine("Image file inserted to database from " + SourceLoc)
 
 
            ' 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)
 
            ' Write the Blob data fetched from database to the filesystem at the destination location
            Dim fs1 As FileStream = New FileStream(DestinationLoc, FileMode.OpenOrCreate, FileAccess.Write)
            fs1.Write(byteData, 0, ArraySize)
            fs1.Close()
 
            Console.WriteLine("Image saved to " + DestinationLoc + " successfully !")
            Console.WriteLine("")
            Console.WriteLine("***********************************************************")
            Console.WriteLine("Before running this application again, execute 'Listing 1' ")

            Console.WriteLine("given in 'Create Database Objects' section in the How-to.")
            Console.WriteLine("***********************************************************")
 
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            cmd.Dispose()
            con.Close()
            con.Dispose()
        End Try
 
    End Sub
 
End Module