How to: Read and Write BLOB Data to a Database Table
Through an Anonymous PL/SQL Block
Date: 18-Oct-2004
Objective
After reading this how-to document you should be able
to:
-
Execute an Anonymous PL/SQL block using ODP.NET
-
Save Image data from a file to database
- Write Image data to a file from database
Prerequisite
The reader is assumed to be familiar with MS Visual
Studio.NET, and also possess an understanding of the rudimentaries of ODP.NET
and databases.
Introduction
This document illustrates how to use an Anonymous
PL/SQL block to insert BLOB (Binary Large OBject)
data in a file into a database table and then retrieve the same data from
the database and write to a file. Anonymous PL/SQL block is an unnamed
block of PL/SQL statements. This block is not stored as an object in the
database, but is executed once and removed.
In this how-to document, an Anonymous PL/SQL block
is created that inserts BLOB data from an image file into a database table
and then retrieves the inserted image and writes it to another file on
the local filesystem. One has to provide the location of the source image
file and the destination image file. Parameters of OracleDbType.Blob
type is bound to the Anonymous PL/SQL block. Note:
To test this how-to , any image file of type like .jpg, .gif etc can be
used. No image file is supplied with this how-to.
Value Proposition
The primary advantage of using anonymous PL/SQL blocks
is improved performance -they can be used to batch a series of SQL calls
together in a single round-trip. This feature allows multiple commands,
separated by semi-colons, to be executed one after another. Further, the
batch SQL can include a number of SELECT or non-SELECT statements; if
the batch contains multiple SELECT statements, multiple number of result
sets will be returned.
Anonymous Pl/SQL blocks can be used for the retrieval
of LOBs. They can also be used to fill a Dataset with multiple tables,
by setting multiple commands in the OracleCommand (one for each table
in the Dataset). In such a scenario you can return multiple REF cursors
from a PL/SQL Anonymous Block, for example, like this:
BEGIN
OPEN :1 FOR SELECT * FROM A;
OPEN :2 FOR SELECT * FROM B;
OPEN :3 FOR SELECT * FROM C;
END;
Requirements
Creating Database Objects
This How-to document uses TestBlob
table. Connect to a database as any user, for eg. scott/tiger
using SQL*Plus and run the following commands
given in Listing 1, to create the required
database object.
Listing 1
DROP TABLE TestBlob;
CREATE TABLE TestBlob (id number, photo BLOB);
|
|
Code Walk-Through
Include Required Namespaces: It is worthwhile
to add references of the namespaces in the 'general declarations' section
of the .cs or .vb
file, to avoid qualifying their usage later in the script:
| C# |
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.IO;
using System.Text;
|
| Visual
Basic .NET |
Imports System
Imports System.Data
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.IO
Imports System.Text
|
1. Establish connection to an
Oracle database using ODP.NET:
| C# |
|
//Step 1
// Connect to database
// Note: Modify User Id, Password, Data Source as per your database setup
string constr = "User Id=Scott;Password=tiger;Data Source=orcl9i";
OracleConnection con = new OracleConnection(constr);
con.Open();
Console.WriteLine("Connected to database!");
|
| Visual
Basic .NET |
'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!")
|
2. Read an image file from the
source location to a byte array:
| C# |
// Step 2
// Note: Modify the Source and Destination location
// of the image as per your machine settings
String SourceLoc = "D:/Images/photo.jpg";
String DestinationLoc = "D:/Images/TestImage.jpg";
// provide read access to the file
FileStream fs = new FileStream(SourceLoc, FileMode.Open,FileAccess.Read);
// Create a byte array of file stream length
byte[] ImageData = new byte[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();
|
| Visual
Basic .NET |
' 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"
' provide 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()
|
3. Create an Anonymous PL/SQL block using the OracleCommand
object:
| C# |
// Step 3
// Create Anonymous PL/SQL block string
String block = " 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
OracleCommand cmd = 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;
|
| Visual
Basic .NET |
' 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
|
4. Bind the Byte Array containing image data to an
OracleCommand object:
| C# |
// Step 4
// Setting Oracle parameters
// Bind the parameter as OracleDbType.Blob to command for inserting image
OracleParameter param = cmd.Parameters.Add("blobtodb", OracleDbType.Blob);
param.Direction = ParameterDirection.Input;
// Assign Byte Array to Oracle Parameter
param.Value = ImageData;
// Bind the parameter as OracleDbType.Blob to command for retrieving the image
OracleParameter param2 = cmd.Parameters.Add("blobfromdb", OracleDbType.Blob);
param2.Direction = ParameterDirection.Output;
|
| Visual
Basic .NET |
' 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
' Assign 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
|
5. Execute the Anonymous Block using the OracleCommand
object:
| C# |
|
// 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);
|
| Visual
Basic .NET |
' 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)
|
6. Save the OracleBlob
object retrieved from the database to the destination location given:
| C# |
|
// Step 6
// Save the retrieved image to the DestinationLoc in the file system
// Create a byte array
byte[] byteData = new byte[0];
// fetch the value of Oracle parameter into the byte array
byteData = (byte[])((OracleBlob)(cmd.Parameters[1].Value)).Value;
// get the length of the byte array
int ArraySize = new int();
ArraySize = byteData.GetUpperBound(0);
// Write the Blob data fetched from database to the filesystem at the
// destination location
FileStream fs1 = 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("***********************************************************");
|
| Visual
Basic .NET |
' 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("***********************************************************")
|
Set up and Run the How-To
1.Open Visual Studio.NET.
2. Create a Console Application Project:
| C# |
Create a Console
Application Project in C#. Class1.cs is added to the
project by default. |
| Visual
Basic .NET |
Create
a Console Application Project in VB.NET. Module1.vb
is added to the project by default.
|
3. Ensure that your project contains references
to the System, Oracle.DataAccess
and System.Data Namespaces. Add references
to these Namespaces if they do not exist.
4. Copy the code:
| C# |
|
Using Solution Explorer open Class1.cs.
For complete listing of code for this How-to article in C#
click here. Copy this code and overwrite the contents of Class1.cs.
|
| Visual
Basic .NET |
Using Solution Explorer open Module1.vb.
For the complete listing of code for this How-to article in VB.NET
click here. Copy this code and
overwrite the contents of Module1.vb.
|
5. Modify User Id, Password and Data Source as per
your database setup in Step 1 of the code.
6.Modify the SourceLoc,
DestinationLoc variable as per your machine settings in Step
2 of the code.
7.To
compile and run this application press Ctrl+F5.
This displays the output as given in Figure 1.1:
Figure 1.1 - Screenshot of output
8.Before running this application again,
execute Listing 1 given in the "Create
Database Objects" section.
Resources
Please enter your comments on this How-To in the
OTN Sample Code Discussion Forum.
|
Bookmark
