(原) ODP.NET 演示通过存储过程的参数获取OracleClob数据

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

转载于:https://www.cnblogs.com/mjgforever/archive/2007/12/26/1015602.html