using
System;
using System.Data;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace Sample33
{
// 演示通过存储过程的参数获取OracleClob数据
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
{
cmd.ExecuteNonQuery();
// Obtain Lob data as a .NET type
string lob_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);
}
return conn;
}
// 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( " '878787454551512122121545489421001212454545') " );
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) as " );
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);
}
}
}
}
引用:ODP.NET SAMPLE
using System.Data;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace Sample33
{
// 演示通过存储过程的参数获取OracleClob数据
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
{
cmd.ExecuteNonQuery();
// Obtain Lob data as a .NET type
string lob_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);
}
return conn;
}
// 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
![](https://www.cnblogs.com/Images/dot.gif)
blr.Append( " '878787454551512122121545489421001212454545') " );
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) as " );
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);
}
}
}
}