locked
Converting string to OracleClob help! RRS feed

  • Question

  • User667727165 posted

    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();
    
    }
    
    }



    Wednesday, November 14, 2012 1:31 PM

Answers

  • User269602965 posted

    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 Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 29, 2012 11:25 AM

All replies

  • User269602965 posted
    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

    Thursday, November 15, 2012 7:46 PM
  • User667727165 posted

    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...

    Thursday, November 29, 2012 10:39 AM
  • User269602965 posted

    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 Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 29, 2012 11:25 AM
  • User667727165 posted

    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!

    Thursday, November 29, 2012 1:37 PM
  • User-126879547 posted

    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

    Thursday, November 29, 2012 1:43 PM