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

    Trouble Inserting a Clob > 4000 charaters

    CRoberts
      Hello.

      I am trying to insert a Clob into an Oracle database, 11.2.0.2, 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 11.2.0.3.0 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 11.2.0.2.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      
      GAC = $Env:Oracle_Home + "\" + "ODP.NET\bin\4\Oracle.DataAccess.dll"
      [Reflection.Assembly]::LoadFile($Gac)
      
      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
       -------------------- -------- ------------------------
       TESTINSERT_ID        NOT NULL NUMBER
       SESSION_YEAR         NOT NULL NUMBER(4)
       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.
      Try
        {
         [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
      Try
        {
         $Conn.Open();
         Write-Host ("`nSuccessful Database connection`n")
        }
      Catch [System.Exception]
       {
        Write-Host $_.Exception.ToString() -ForeGroundColor "Red"
       }
      
      
      # Insert the row, fail if an exception occurs.
      Try
        {
         $Result = $Cmd.ExecuteNonQuery();
         Write-Host "`n`nSuccess. Return value: $Result"
        }
      Catch [System.Exception]
       {
        Write-Host $_.Exception.ToString() -ForeGroundColor "Red"
       }
      Finally
       {
        $Conn.Close();
        $Conn.Dispose();
        "Successfull end of script"
       }
      
      [\code]
      
      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.