Oracle Database LOB Data Handling with C# and VB.NET

Posted on Feb 3, 2025 in Communication and Cultural Industries

Oracle LOB Data Handling

BFILE Image

This section demonstrates how to handle BFILE data in Oracle using C#.

CREATE TABLE bfile_tab(
  dogID NUMBER NOT NULL PRIMARY KEY,
  dog_img BFILE
);

INSERT INTO bfile_tab VALUES(10, BFILENAME('DOG', '2.jpg'));
INSERT INTO bfile_tab VALUES(20, BFILENAME('DOG', '3.jpg'));
COMMIT;

C# Code

using System;
using System.IO;
using System.Windows.Forms;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace DOG
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OracleConnection cn = new OracleConnection("data source = orcl; user id = Ravali; password =rav;");
            string sqlstr = "SELECT dog_img FROM bfile_tab WHERE dogid=20";
            cn.Open();
            OracleCommand cmd = new OracleCommand(sqlstr, cn);

            OracleDataReader dr = cmd.ExecuteReader();
            dr.Read();

            OracleBFile Bfile = dr.GetOracleBFile(0);
            Bfile.OpenFile();

            MemoryStream strmpic = new MemoryStream(Bfile.Value);
            pictureBox1.Image = Image.FromStream(strmpic);

            cn.Close();
            cn.Dispose();
            cmd.Dispose();
            Bfile.Close();
        }
    }
}

BLOB Table

This section shows how to handle BLOB data in Oracle using VB.NET.

CREATE TABLE BLOB_TABLE (ID NUMBER PRIMARY KEY, IMAGE_BLOB BLOB);

CREATE OR REPLACE PROCEDURE image_blob_import (dest_id IN NUMBER, filename VARCHAR2) IS
  img_blob BLOB;
  ctx RAW(64) := NULL;
BEGIN
  DELETE FROM blob_table WHERE id = dest_id;
  INSERT INTO blob_table (id, image_blob) VALUES (dest_id, EMPTY_BLOB())
  RETURNING image_blob INTO img_blob;
  ORDSYS.ORDImage.importFrom(ctx, img_blob, 'file', 'DOG', filename);
  UPDATE blob_table SET image_blob = img_blob WHERE id = dest_id;
  COMMIT;
END;

CALL image_blob_import(20, '6.jpg');
CALL image_blob_import(30, '4.jpg');

VB.NET Code

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.IO

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim cn As New OracleConnection("data source = orcl; user id = RAN; password = ran;")
        Dim sqlstr As String = "SELECT IMAGE_BLOB FROM BLOB_TABLE WHERE ID=30"
        cn.Open()
        Dim cmd As New OracleCommand(sqlstr, cn)
        Dim dr As OracleDataReader = cmd.ExecuteReader()
        dr.Read()

        Dim Blob As OracleBlob = dr.GetOracleBlob(0)

        Dim strmPic As New MemoryStream(Blob.Value)

        PictureBox1.Image = Image.FromStream(strmPic)
        cn.Close()
        cn.Dispose()
        Blob.Close()
        Blob.Dispose()

    End Sub
End Class

ORDImage Table

This section demonstrates how to handle ORDImage data in Oracle using VB.NET.

CREATE TABLE ordimage_table(PetID NUMBER PRIMARY KEY, Petimg ORDImage);

CREATE SEQUENCE petseq START WITH 10;

CREATE OR REPLACE PROCEDURE ORDimage_import(filename VARCHAR2) IS
  img ORDImage;
  imgid INT;
  ctx RAW(64) := NULL;
BEGIN
  SELECT petseq.nextval INTO imgid FROM dual;
  INSERT INTO ORDimage_table VALUES (imgid, ordsys.ordimage.init())
  RETURNING petimg INTO img;
  img.importFrom(ctx, 'file', 'DOG', filename);
  UPDATE ORDimage_table SET petimg = img WHERE petid = imgid;
  COMMIT;
END;

CALL ORDimage_import('4.jpg');
CALL ORDimage_import('5.jpg');

VB.NET Code

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.IO

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim cn As New OracleConnection("data source = orcl; user id = RAN; password= ran")
        Dim sqlstr As String = "SELECT t.Petimg.getContent() FROM ordimage_table t WHERE t.PetID= :picid"
        cn.Open()
        Dim cmd As New OracleCommand(sqlstr, cn)
        Dim pic_id As OracleParameter = New OracleParameter()
        pic_id.Value = Integer.Parse(TextBox1.Text)
        pic_id.OracleDbType = OracleDbType.Int16
        cmd.Parameters.Add(pic_id)
        Dim dr As OracleDataReader = cmd.ExecuteReader()
        dr.Read()
        Dim Blob As OracleBlob = dr.GetOracleBlob(0)
        Dim strmPic As New MemoryStream(Blob.Value)
        PictureBox1.Image = New Bitmap(strmPic)
        cn.Close()
        cn.Dispose()
        cmd.Dispose()
        Blob.Close()
        Blob.Dispose()
    End Sub
End Class

ORDVideo Table

This section demonstrates how to handle ORDVideo data in Oracle using VB.NET. Ensure you have a “TEMP” folder in C drive.

CREATE TABLE video_table (productid NUMBER PRIMARY KEY, ad_video ORDVideo);

CREATE SEQUENCE vidseq START WITH 10;

CREATE OR REPLACE PROCEDURE load_video(filename VARCHAR2) IS
  adfile ORDVideo;
  pid INT;
  ctx RAW(64) := NULL;
BEGIN
  SELECT vidseq.nextval INTO pid FROM dual;
  INSERT INTO video_table VALUES(pid, ORDVideo.init())
  RETURNING ad_video INTO adfile;
  adfile.setMimetype('application/x-shockwave-flash');
  adfile.importFrom(ctx, 'file', 'DOG', filename);
  UPDATE video_table SET ad_video = adfile WHERE productid = pid;
  COMMIT;
END;

CALL Load_video('Wildlife.wmv');
CALL Load_video('Video.wmv');

CREATE OR REPLACE PROCEDURE video_download(pid IN INT) AS
  vid ORDVideo;
  ctx RAW(4000) := NULL;
  ext VARCHAR2(25);
BEGIN
  SELECT ad_video INTO vid FROM video_table WHERE productID = pid;
  ext := vid.getFrameRate();
  vid.export(ctx, 'FILE', 'TEMP', pid || '.wmv');
  COMMIT;
END;

CALL video_download(11);
CALL video_download(12);

VB.NET Code

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.IO

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim video_id, FileName As String
        video_id = (TextBox1.Text).ToString
        FileName = "C:\temp\" & video_id & ".wmv"

        Dim cn As New OracleConnection("data source= orcl; user id= Ravali; password= rav")
        cn.Open()
        Dim cmd As New OracleCommand("video_download", cn)
        cmd.CommandType = CommandType.StoredProcedure

        Dim pic_id As OracleParameter = New OracleParameter()
        pic_id.Value = Integer.Parse(TextBox1.Text)
        pic_id.OracleDbType = OracleDbType.Int16
        cmd.Parameters.Add(pic_id)
        Dim dr As OracleDataReader = cmd.ExecuteReader()

        cn.Close()
        cn.Dispose()
        cmd.Dispose()
        dr.Close()
        dr.Dispose()
        FileName = "C:\temp\" & video_id & ".wmv"
        MyPlayer.URL = FileName
        System.IO.File.Delete(FileName)

    End Sub
End Class

Transaction Table

This section demonstrates transaction handling in Oracle using VB.NET.

VB.NET Code

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.Data

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim cn As New OracleConnection("user id = oe; password = oe; Data Source = ORCL")
        cn.Open()

        Dim Mytrans As OracleTransaction
        Mytrans = cn.BeginTransaction(IsolationLevel.ReadCommitted)
        Dim cmd As OracleCommand = cn.CreateCommand()

        Try
            cmd.CommandText = "INSERT INTO customers(CUSTOMER_ID,CUST_FIRST_NAME,CUST_LAST_NAME,CUST_EMAIL) VALUES(10020, 'John','Pearson','john@ucmo.edu')"
            cmd.ExecuteNonQuery()
            Mytrans.Save("MySavePoint1")

            cmd.CommandText = "INSERT INTO customers(CUSTOMER_ID,CUST_FIRST_NAME,CUST_LAST_NAME,CUST_EMAIL) VALUES(10021, 'Sanju','Samson','sanju@ucmo.edu')"
            cmd.ExecuteNonQuery()
            Mytrans.Save("MySavePoint2")

            Mytrans.Commit()
            Label1.Text = "Success"
        Catch ex As Exception
            Label1.Text = ex.Message.ToString
            Mytrans.Rollback("MySavePoint1")

        Finally
            cn.Close()
        End Try
    End Sub
End Class

Customer and Order Data Entry

This section demonstrates how to insert customer and order data using VB.NET with multiple text boxes.

VB.NET Code

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.Data

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim conn As New OracleConnection("User Id = OE; Password = oe; Data Source = ORCL")
        conn.Open()

        Dim Mytrans As OracleTransaction
        Mytrans = conn.BeginTransaction(IsolationLevel.ReadCommitted)

        Dim cmd As OracleCommand = conn.CreateCommand()

        Try

            cmd.CommandText = "INSERT INTO customers (CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_EMAIL) VALUES (:cust_ID, :cust_Fname, :cust_Lname, :cust_email)"
            cmd.Parameters.Add(":cust_ID", OracleDbType.Int32).Value = CType(TextBox1.Text, Integer)
            cmd.Parameters.Add(":cust_Fname", OracleDbType.Varchar2, 20).Value = TextBox2.Text.ToString
            cmd.Parameters.Add(":cust_Lname", OracleDbType.Varchar2, 20).Value = TextBox3.Text.ToString
            cmd.Parameters.Add(":cust_email", OracleDbType.Varchar2, 20).Value = TextBox4.Text.ToString

            cmd.ExecuteNonQuery()
            Mytrans.Save("mySavePoint1")

            cmd.CommandText = "INSERT INTO orders (order_id, Order_Date, Customer_id, order_total, Sales_rep_id) VALUES (:orderID, sysdate, :cust_ID, :total, :rep)"

            cmd.Parameters.Add(":orderID", OracleDbType.Int32).Value = CType(TextBox5.Text, Integer)
            cmd.Parameters.Add(":cust_ID", OracleDbType.Int32).Value = CType(TextBox1.Text, Integer)
            cmd.Parameters.Add(":total", OracleDbType.Int32).Value = CType(TextBox6.Text, Integer)
            cmd.Parameters.Add(":rep", OracleDbType.Int32).Value = CType(TextBox7.Text, Integer)

            cmd.ExecuteNonQuery()
            Mytrans.Save("mySavePoint2")

            Mytrans.Commit()
            label1.text = "success"

        Catch ex As Exception
            Label1.Text = ex.Message.ToString
            Mytrans.Rollback("MySavePoint1")

        Finally
            conn.Close()
        End Try
    End Sub
End Class