(Original) ODP. Net demonstrate how to obtain oracleclob data through the parameters of the stored procedure

Using System;
Using System. Data;
Using System. text;
Using Oracle. dataaccess. client;
Using Oracle. dataaccess. types;

Namespace Sample33
{
// Demonstrate how to obtain oracleclob data through parameters of the stored procedure
Class Program
{
Static   Void Main ( String [] ARGs)
{
// Connect
String Constr =   " User ID = Scott; Password = tiger; Data Source = bjoracle " ;
Oracleconnection Conn = Connect (constr );

//Setup
Setup (conn );

// Set the command
Oraclecommand cmd =   New Oraclecommand ( "" , Conn );
Cmd. commandtext =   " Selectstory " ;
Cmd. commandtype = Commandtype. storedprocedure;

// bind the Oracle clob object
oracleparameter Param = cmd. parameters. add ( " clobdata " , oracledbtype. clob);
param. direction = parameterdirection. output;

// Execute Command
try
{< br> cmd. executenonquery ();

//Obtain lob data as a. Net Type
StringLob_data=(String) (Oracleclob) (CMD. Parameters [0]. Value). value;

// Print out the text
Console. writeline ( " Data is: "   + Lob_data );
}
Catch (Exception ex)
{
Console. writeline ( " Error: {0} " , Ex. Message );
}
Finally
{
// Dispose oraclecommand object
Cmd. Dispose ();

//Close and dispose oracleconnection object
Conn. Close ();
Conn. Dispose ();
}

//Waiting
Console. Readline ();
}

// Opening a new connection
Public   Static Oracleconnection connect ( String Connectstr)
{
Oracleconnection Conn =   New Oracleconnection (connectstr );
Try
{
Conn. open ();
}
Catch (Exception ex)
{
Console. writeline ( " Error: {0} " , Ex. Message );
}

ReturnConn;
}

// Setup the necessary tables & Test Data
Public   Static   Void Setup (oracleconnection conn)
{
Stringbuilder BLR;
Oraclecommand cmd =   New Oraclecommand ( "" , Conn );

BLR =   New Stringbuilder ();
BLR. append ( " Drop table multimedia_tab " );
Cmd. commandtext = BLR. tostring ();
Try
{
Cmd. executenonquery ();
}
Catch (Exception ex)
{
Console. writeline ( " Warning: {0} " , Ex. Message );
}

BLR =   New Stringbuilder ();
BLR. append ( " Create Table multimedia_tab (thekey number (4) primary key, " );
BLR. append ( " Story clob, sound BLOB) " );
Cmd. commandtext = BLR. tostring ();
Try
{
Cmd. executenonquery ();
}
Catch (Exception ex)
{
Console. writeline ( " Error: {0} " , Ex. Message );
}

BLR =   New Stringbuilder ();
BLR. append ( " Insert into multimedia_tab values ( " );
BLR. append ( " 1, " );
BLR. append ( " 'This is a long story. Once upon a time .', " );
BLR. append ( " '123 ') " );
Cmd. commandtext = BLR. tostring ();
Try
{
Cmd. executenonquery ();
}
Catch (Exception ex)
{
Console. writeline ( " Error: {0} " , Ex. Message );
}

// Build a SQL that creates Stored Procedure
Stringbuilder SQL =   New Stringbuilder ();
SQL. append ( " Create or replace procedure selectstory ( " );
SQL. append ( " Clob_data out clob) " );
SQL. append ( " Begin " );
SQL. append ( " Select story into clob_data from multimedia_tab where thekey = 1; " );
SQL. append ( " End selectstory; " );
Cmd. commandtext = SQL. tostring ();
Try
{
Cmd. executenonquery ();
}
Catch (Exception ex)
{
Console. writeline ( " Error: {0} " , Ex. Message );
}
}
}
}

Reference: ODP. Net sample