Shulou(Shulou.com)06/01 Report--

The Oracle .NET Framework data provider includes the OracleLob class, which is used to work with Oracle LOB data types.

OracleLob may be one of the following OracleType data types:

Data type

Description

Blob

The Oracle BLOB data type that contains binary data, with a maximum size of 4 GB. This data type maps to an Array of type Byte.

Clob

The Oracle CLOB data type that contains character data, with a maximum size of 4 GB, depending on the default character set of the server. This data type maps to String.

NClob

The Oracle NCLOB data type that contains character data with a maximum size of 4G bytes, depending on the server's regional character set. This data type maps to String.

OracleLob differs from OracleBFile in that the data of the former is stored on the server rather than in the physical files of the operating system. It can also be a read-write object, unlike OracleBFile (which is always read-only).

Create, retrieve, and write LOB

The following C# example shows how to create a LOB in an Oracle table and then retrieve and write it as an OracleLob object. This example shows how to use the OracleDataReader object as well as the OracleLobRead and Write methods. The example uses the Oracle BLOB, CLOB, and NCLOB data types.

[C#]

Using System

Using System.IO

Using System.Text

Using System.Data

Using System.Data.OracleClient

/ / LobExample

Public class LobExample

{

Public static int Main (string [] args)

{

/ / Create a connection.

OracleConnection conn = new OracleConnection (

"Data Source=Oracle8i;Integrated Security=yes")

Using (conn)

{

/ / Open a connection.

Conn.Open ()

OracleCommand cmd = conn.CreateCommand ()

/ / Create the table and schema.

CreateTable (cmd)

/ / Read example.

ReadLobExample (cmd)

/ / Write example

WriteLobExample (cmd)

}

Return 1

}

/ / ReadLobExample

Publicstatic void ReadLobExample (OracleCommand cmd)

{

Int actual = 0

/ / Table Schema:

/ / "CREATE TABLE tablewithlobs (an int, b BLOB, c CLOB, dNCLOB)"

/ / "INSERT INTO tablewithlobs values (1, 'AA',' AAA',N'AAAA')"

/ / Select some data.

Cmd.CommandText = "SELECT * FROM tablewithlobs"

OracleDataReader reader = cmd.ExecuteReader ()

Using (reader)

{

/ / Obtain the first row of data.

Reader.Read ()

/ / Obtain the LOBs (all 3 varieties).

OracleLob blob = reader.GetOracleLob (1)

OracleLob clob = reader.GetOracleLob (2)

OracleLob nclob = reader.GetOracleLob (3)

/ / Example-Reading binary data (in chunks)

Byte [] buffer = new byte [100]

While ((actual = blob.Read (buffer, 0, buffer.Length)) > 0)

Console.WriteLine (blob.LobType + ".Read (" + buffer + "," +

Buffer.Length + ") = >" + actual)

/ / Example-Reading CLOB/NCLOB data (in chunks)

/ / Note: You can read characterdata as raw Unicode bytes

/ (using OracleLob.Read as in the above example)

/ / However, because the OracleLob object inherits directly

/ / from the .Net stream object

/ / all the existing classes that manipluate streams can

/ / also be used. For example, the

/ / .Net StreamReader makes it easier to convert the raw bytes

/ / into actual characters.

StreamReader streamreader =

New StreamReader (clob, Encoding.Unicode)

Char [] cbuffer = new char [100]

While ((actual = streamreader.Read (cbuffer)

0, cbuffer.Length)) > 0)

Console.WriteLine (clob.LobType + ".Read (

"+ new string (cbuffer, 0, actual) +", "+

Cbuffer.Length + ") = >" + actual)

/ / Example-Reading data (all at once)

/ / You could use StreamReader.ReadToEnd to obtain

/ / all the string data, or simply

/ / call OracleLob.Value to obtain a contiguous allocation

/ / of all the data.

Console.WriteLine (nclob.LobType + ".value = >" + nclob.Value)

}

}

/ / WriteLobExample

Public static void WriteLobExample (OracleCommand cmd)

{

/ / Note:Updating LOB data requires a transaction.

Cmd.Transaction = cmd.Connection.BeginTransaction ()

/ / Select some data.

/ / Table Schema:

/ / "CREATE TABLE tablewithlobs (an int, b BLOB, c CLOB, dNCLOB)"

/ / "INSERT INTO tablewithlobs values (1, 'AA',' AAA',N'AAAA')"

Cmd.CommandText = "SELECT * FROM tablewithlobs FOR UPDATE"

OracleDataReader reader = cmd.ExecuteReader ()

Using (reader)

{

/ / Obtain the first row of data.

Reader.Read ()

/ / Obtain a LOB.

OracleLob blob = reader.GetOracleLob (1/*0:based ordinal*/)

/ / Perform any desired operations on the LOB

/ (read, position, and so on).

/ / Example-Writing binary data (directly to the backend)

/ / To write, you can use any of the stream classes, or write

/ / raw binary data using

/ / the OracleLob write method. Writing character vs. Binary

/ / is the same

/ / however note that character is always in terms of

/ / Unicode byte counts

/ / (for example, even number of bytes-2 bytes for every

/ / Unicode character).

Byte [] buffer = new byte [100]

Buffer [0] = 0xCC

Buffer [1] = 0xDD

Blob.Write (buffer, 0,2)

Blob.Position = 0

Console.WriteLine (blob.LobType + ".Write (

"+ buffer +", 0,2) = > "+ blob.Value)

/ / Example-Obtaining a temp LOB and copying data

/ / into it from another LOB.

OracleLob templob = CreateTempLob (cmd, blob.LobType)

Long actual = blob.CopyTo (templob)

Console.WriteLine (blob.LobType + ".CopyTo (

"+ templob.Value +") = > "+ actual)

/ / Commit the transaction now that everything succeeded.

/ / Note: On error, Transaction.Dispose is called

/ / (from the using statement)

/ / and will automatically roll back the pending transaction.

Cmd.Transaction.Commit ()

}

}

/ / CreateTempLob

Public static OracleLob CreateTempLob (

OracleCommand cmd, OracleType lobtype)

{

/ / Oracle server syntax to obtain a temporary LOB.

Cmd.CommandText = "DECLARE A" + lobtype + ";" +

"BEGIN" +

"DBMS_LOB.CREATETEMPORARY (A, FALSE);" +

": LOC: = A;" +

"END;"

/ / Bind the LOB as an output parameter.

OracleParameter p = cmd.Parameters.Add ("LOC", lobtype)

P.Direction = ParameterDirection.Output

/ / Execute (to receive the output temporary LOB).

Cmd.ExecuteNonQuery ()

/ / Return the temporary LOB.

Return (OracleLob) p.Value

}

/ / CreateTable

Public static void CreateTable (OracleCommand cmd)

{

/ / Table Schema:

/ / "CREATE TABLE tablewithlobs (an int, b BLOB, c CLOB, dNCLOB)"

/ / "INSERT INTO tablewithlobs VALUES (1, 'AA',' AAA',N'AAAA')"

Try

{

Cmd.CommandText = "DROPTABLE tablewithlobs"

Cmd.ExecuteNonQuery ()

}

Catch (Exception)

{

}

Cmd.CommandText =

"CREATE TABLE tablewithlobs (an int, b BLOB, c CLOB, d NCLOB)"

Cmd.ExecuteNonQuery ()

Cmd.CommandText =

"INSERT INTO tablewithlobs VALUES (1, 'AA',' AAA', unknown AAAAAAAAAAAAAAAA')"

Cmd.ExecuteNonQuery ()

}

}

Create a temporary LOB

The following C# example shows how to create a temporary LOB.

[C#]

OracleConnection conn = new OracleConnection (

"server=test8172; integrated security=yes;")

Conn.Open ()

OracleTransaction tx = conn.BeginTransaction ()

OracleCommand cmd = conn.CreateCommand ()

Cmd.Transaction = tx

Cmd.CommandText =

"declare xx blob; begin dbms_lob.createtemporary (

Xx,false, 0);: tempblob: = xx; end; "

Cmd.Parameters.Add (newOracleParameter ("tempblob")

OracleType.Blob) .Direction = ParameterDirection.Output

Cmd.ExecuteNonQuery ()

OracleLob tempLob = (OracleLob) cmd.Parameters [0] .value

TempLob.BeginBatch (OracleLobOpenMode.ReadWrite)

TempLob.Write (tempbuff,0,tempbuff.Length)

TempLob.EndBatch ()

Cmd.Parameters.Clear ()

Cmd.CommandText = "myTable.myProc"

Cmd.CommandType = CommandType.StoredProcedure

Cmd.Parameters.Add (new OracleParameter (

"ImportDoc", OracleType.Blob). Value = tempLob

Cmd.ExecuteNonQuery ()

Tx.Commit ()

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.