3 Replies Latest reply: Nov 26, 2012 12:13 PM by Tridus RSS

    Trouble Inserting a Clob > 4000 charaters


      I am trying to insert a Clob into an Oracle database,, using ODP that is moderately large at 353484 characters in size. I am running into buffer too small error. This twist here is that I am running Powershell 3.0 on Window 7 rather than C#. But they are very close in most cases. I am running under a Microsoft .NET Framework 4.5. Any solutions provided in C# should be translatable into Powershell. The table has both a Blob and Xml columns, but I am ignoring these two columns for the present.

      This is my Oracle client environment:
      SQL*Plus: Release Production on Sun Nov 25 14:44:03 2012
      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      Connected to:
      Oracle Database 11g Enterprise Edition Release - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      GAC = $Env:Oracle_Home + "\" + "ODP.NET\bin\4\Oracle.DataAccess.dll"
      n        Location
      -        --------
      0319     C:\windows\Microsoft.Net\assembly\GAC_64\Oracle.DataAccess\v4.0_4.112.3.0__89b483f429c47342\Or...
      This is my target test table:
      ELSDV01> desc TestInsert
       Name                 Null?    Type
       -------------------- -------- ------------------------
       ACTION_DT                     DATE
       TEXT_STR                      VARCHAR2(2000)
       TEXT_CB                       CLOB
       TEXT_BB                       BLOB
       TEXT_XML                      SYS.XMLTYPE STORAGE BINARY
       TRANS_UID            NOT NULL VARCHAR2(128)
       TRANS_UPDATE                  TIMESTAMP(6)
      My insert fails with this error. The Insert does works if no Clob is specified:
      Oracle.DataAccess.Client.OracleException ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual:
       747898, maximum: 4000)    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection
       conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
         at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
         at CallSite.Target(Closure , CallSite , Object )
      Here is what I coded
      [String] $ClobPath = 'C:\Source Code\Work\Scripts\Powershell\Output\WorkingDoc.txt'
      # Now try and load these datatype into varibles.
         [String] $Clob_Text =  (Get-Content -Path $ClobPath -Encoding Utf8 )
         Write-Host ("`nSuccessful loaded Clob from file system with size: " + ($Clob_Text.Length).ToString() )
      Catch [System.Exception]
        Write-Host $_.Exception.ToString() -ForeGroundColor "Red"
      #Now compile the connection infor into a usable proper format
      $EZConnect       = '//' + $Server  + ':' + $Port + '/' + $Service
      $Connect_Str     = "Data Source=$EzConnect;User Id=$Schema;Password=$Pw"
      # Create the Sql Statement
      $Sql = " INSERT INTO TestInsert ( "
      $Sql +=  "TestInsert_Id, "
      $Sql +=  "Session_Year, "
      $Sql +=  "Action_Dt, "
      $Sql +=  "Text_Str, "
      $Sql +=  "Text_Cb, "
      $Sql +=  "Trans_Uid, "
      $Sql +=  "Trans_Update ) "
      $Sql +=  "VALUES (TestInsert_Seq.NEXTVAL, :Param1, :Param2, :Param3, :Param4, :Param5, LocalTimeStamp )"
      #Set up the parameters for use with the Sql command.
      $Param1 = New-Object Oracle.DataAccess.Client.OracleParameter
      . . .
      $Param5 = New-Object Oracle.DataAccess.Client.OracleParameter
      $Param1.DbType = 'Decimal'
      $Param1.OracleDbType = 'Decimal'
      $Param1.Direction = 'Input'
      $Param1.Precision = 4
      $Param1.Scale = 0
      $Param1.ParameterName = ':Param1'
      $Param1.Value = ((Get-Date).Year)
      . . .
      $Param5.DbType = 'Clob'
      $Param5.OracleDbType = 'Clob'
      $Param5.Direction = 'Input'
      $Param5.ParameterName = ':Param5'
      $Param5.Value = $Clob_Text
      # Set up the command object using the connection specified.  Ensure that
      # Bind by name is the default behavior.
      $Conn = New-Object Oracle.DataAccess.Client.OracleConnection($Connect_Str)
      $Cmd  = New-Object Oracle.DataAccess.Client.OracleCommand($Sql, $Conn)
      $Cmd.BindByName = $True
      # Now bind the parameters to the Sql command
      [Void] $Cmd.Parameters.Add($Param1)
      . . .
      [Void] $Cmd.Parameters.Add($Param5)
      # Open connection to database
         Write-Host ("`nSuccessful Database connection`n")
      Catch [System.Exception]
        Write-Host $_.Exception.ToString() -ForeGroundColor "Red"
      # Insert the row, fail if an exception occurs.
         $Result = $Cmd.ExecuteNonQuery();
         Write-Host "`n`nSuccess. Return value: $Result"
      Catch [System.Exception]
        Write-Host $_.Exception.ToString() -ForeGroundColor "Red"
        "Successfull end of script"
      Is my approach flawed?  I have searched the internet and cannot find a sample of loading a > 4000 character clob into an Oracle database using ODP.  Should I switch to Perl or Python or the like?  A BFile is not really an option here as at the moment I am forced to use Windows 7 platform and the database is hosted on Solaris.  Do I have to chunk the large document into smaller pieces?  
      Perhaps if all else fails I can FTP the document into a temporary folder on XDB and deal with the document there.  However, it seems to me to be a lot of moving pieces just to load a text document.
      Thank you for your help.