Converting string to OracleClob help! RSS

• • Reply
  

  steveDy

  Member

  1 Points

  3 Posts

  Converting string to OracleClob help!

  Nov 14, 2012 05:31 PM|LINK

  I have a C# application that reads a text file. I created a stored procedure in Oracle to accept a clob as input. The text is read to a string and i need to convert it to an Oracleclob but cant seem to get it to work.

  Any help would be greatly appreciated.

  Code:

  string clobData;
  
  using (StreamReader sr = new StreamReader(@"C:\LogFileName.txt"))
  {
  sr.ReadLine();
  clobData = sr.ReadToEnd();
  
  }
  
  Oracle.DataAccess.Types.OracleClob test12;
  
  //How Do i convert the string to Oracle Clob Format?
  
  
  
  using (OracleConnection insertConnect = new OracleConnection(connectionString))
  {
  
  OracleCommand InsertData = new OracleCommand();
  InsertData.Connection = insertConnect;
  InsertData.CommandText = "RunSP";
  InsertData.CommandType = CommandType.StoredProcedure;
  
  InsertData.Parameters.Add("pFileName", OracleType.VarChar).Direction = ParameterDirection.Input;
  
  InsertData.Parameters["pFileName"].Value = "LogFileName.txt";
  
  InsertData.Parameters.Add("pString", OracleType.Clob).Direction = ParameterDirection.Input;
  
  InsertData.Parameters["pString"].Value = test12;
  
  InsertData.Parameters.Add("pDelim", OracleType.VarChar).Direction = ParameterDirection.Input;
  
  InsertData.Parameters["pDelim"].Value = "|";
  
  try
  {
  insertConnect.Open();
  InsertData.ExecuteNonQuery();
  
  }
  catch (Exception e)
  {
  Console.WriteLine(e);
  Console.ReadLine();
  
  }
  
  }

  
  
  

• • Reply
  

  Lannie

  Contributor

  3736 Points

  727 Posts

  Re: Converting string to OracleClob help!

  Nov 15, 2012 11:46 PM|LINK

  I insert CLOBS this way in VB.NET

  Imports System.Xml.Linq.XElement
  
    Public Shared Sub updateUnitsActiveFlag(ByVal decQuantity As Decimal, ByVal strDescription As String, ByVal dateDateClosed As DateTime)
      ' Insert data into new row Units table'
      Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("OraConnStr").ConnectionString
      Try
        Dim SQL =
        <SQL>
        INSERT INTO {YOURSCHEMANAME}.UNITS
          (UNITS_SEQ, QUANTITY, DESCRIPTION, DATE_CLOSED, DATE_TODAY) 
        VALUES 
          (UNIT_SEQ.NextVal, :BindVarQuantity, :BindVarDescription, :BindVarDateClosed, SYSDATE)
        </SQL>
        Using conn As New OracleConnection(OraConnStr)
          Using cmd As New OracleCommand(SQL.Value, conn)
            cmd.Parameters.Clear()
            cmd.Parameters.Add("BindVarQuantity", OracleDbType.Decimal, decQuantity, ParameterDirection.Input)
            cmd.Parameters.Add("BindVarDescription", OracleDbType.CLOB, strDescription, ParameterDirection.Input)
            cmd.Parameters.Add("BindVarDateClosed", OracleDbType.Date, dateDateClosed, ParameterDirection.Input)
            conn.Open()
            cmd.ExecuteNonQuery()
          End Using
        End Using
      Catch ex As Exception
        AppCalls.WriteToEventLog(ex, "Inserting UNITS.QUANTITY failed", "AppCalls.updateUnitsActiveFlag.vb")
      End Try
  End Sub

  • Edited by Lannie on Nov 15, 2012 11:47 PM
• • Reply
  

  steveDy

  Member

  1 Points

  3 Posts

  Re: Converting string to OracleClob help!

  Nov 29, 2012 02:39 PM|LINK

  Lannie, i appeciate you sharing that code with me. My question is in .NET where did ":BindVarDescription" come from? I have a sp that accepts a clob, the clob is a string in .NET that i cant seem to convert to the clob datatype...

• • Reply
  

  Lannie

  Contributor

  3736 Points

  727 Posts

  Re: Converting string to OracleClob help!

  Nov 29, 2012 03:25 PM|LINK

  I passed a VB.NET string strDescription and assigned it to the Oracle Bind Variable I named BindVarDescription and defined as CLOB datatype, and then I inserted the passed VB.NET string into an Oracle table with a column DESCRIPTION that is a CLOB datatype.

  Another note: Oracle expects the bind variables to be in the same order on both sides, VB.NET and the SQL statement.

  • Marked as answer by steveDy on Nov 29, 2012 05:49 PM
• • Reply
  

  steveDy

  Member

  1 Points

  3 Posts

  Re: Converting string to OracleClob help!

  Nov 29, 2012 05:37 PM|LINK

  Lannie thanks for getting back to me,

  I found the error, it was in the stored procedure, I need to go back to troubleshooting 101...

  Thanks so much!

  • Edited by steveDy on Nov 29, 2012 05:51 PM
  • Edited by steveDy on Nov 29, 2012 05:51 PM
• • Reply
  

  RameshRajend...

  Star

  7983 Points

  2099 Posts

  Re: Converting string to OracleClob help!

  Nov 29, 2012 05:43 PM|LINK

  Hi,

  It should be possible since Value is a string type property. Say if your OracleClob object name is clob1 then it should be possible to write something like -
  
  string str = clob1.Value;//C# syntax
  
  If you are using OracleParameter's Value property then you will have to do ".Value" twice as shown below -
  
  OracleParameter paramHavingClobInIt;
  ....
  string str = (paramHavingClobInIt.Value as OracleClob).Value; //C# syntax

  plz prefer other links also

  http://www.codeproject.com/KB/database/C__and_Oracle.aspx