ODP.NET

Hello All,

What I am trying to do:
---------------------------------
I am trying to insert a string as a CLOB into Oracle via C# using Visual Studio (VS) 2005.

What I am using:
------------------------
The Oracle Tools for VS 2005 are installed and work fabulously with other types of data.

Research I have done on my own:
------------------------------------------------
1. I have read the thread "ORA-01461"
2. Have modified an example (see below) from the Oracle web site
..... as noted in the comments below (Jason Price).
3. Addtionally, there are a number of www references.
4. However, after a long unproductive day none are working for me.

Error messages from Oracle (caught by the catch block):
---------------------------------------------------------------------------------
The error is: Oracle.DataAccess.Client.OracleException ORA-00936: missing expression at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader() at clobSample.workThis() in c:\Documents and Settings\joe.user\My Documents\Visual Studio 2005\WebSites\HDTS\App_Code\clobSample.cs:line 71



Thank you kind people of the Oracle Community,

Mark

*********** Code is Below ***********


using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;


public class clobSample
{
public clobSample()
{
//
// TODO: Add constructor logic here
//
}

public OracleConnection getConnectionString(string DBuser, string DBpass)
{
// Build Connection String
string connectionString = "Data Source=MY_DATA_SOURCE;User Id=";
connectionString += DBuser + ";Password=";
connectionString += DBpass + ";Persist Security Info=True";
OracleConnection connection = new OracleConnection(connectionString);

return connection;
}



/*
ClobExample4.cs shows how to update a CLOB
using SELECT ... FOR UPDATE
*/


public string workThis()
{
string strResult = "";

try
{
// create an OracleConnection object to connect to the database and open
// the connection
OracleConnection myOracleConnection =
getConnectionString("DB_USER_NAME", "DB_PASSWORD");
myOracleConnection.Open();

// create an OracleCommand object to hold a SQL statement
OracleCommand myOracleCommand = myOracleConnection.CreateCommand();

// step 1: create an OracleTransaction object
OracleTransaction myOracleTransaction =
myOracleConnection.BeginTransaction();

// step 2: read the row
myOracleCommand.CommandText =
"SELECT TICKET_NUM, COMMENT " +
"FROM HDTS_TICKET_EVENTS " +
"WHERE TICKET_NUM = 1 FOR UPDATE";

// *****************************************************
// THIS IS WHERE THE CODE ERRORS OUT *
// *****************************************************
OracleDataReader myOracleDataReader =
myOracleCommand.ExecuteReader();
// *****************************************************

myOracleDataReader.Read();

// Console.WriteLine("myOracleDataReader[\"id\"] = " +
// myOracleDataReader["id"]);

// step 3: get the LOB locator
OracleClob myOracleClob =
myOracleDataReader.GetOracleClob(1);

// step 4: write to the LOB
myOracleClob.Erase();
string text = "Alas poor Yoric, I knew him well";
char[] charArray = text.ToCharArray();
myOracleClob.Write(charArray, 0, charArray.Length);
Console.WriteLine("myOracleClob.Value = " + myOracleClob.Value);

// step 5: commit the transaction
myOracleTransaction.Commit();
// Console.WriteLine("Successfully committed transaction");

// close the OracleDataReader and the OracleConnection object
myOracleDataReader.Close();
myOracleConnection.Close();

// Console.WriteLine("\nPress any key to end");
// Console.ReadLine();

return strResult;


} // close try
catch (Exception e)
{
strResult = "The error is: " + e;
return strResult;

}


} // close work this


} // close clobSample class

Edited by: microBiker on Sep 30, 2008 5:01 AM