Oracle & .NET: Resolve – Exception calling “Fill” with “1” argument(s): ‘count’ must be non-negative.

This is one of those obscure errors that has no answer on the Web. I ran into this error trying to migrate an Oracle table to SQL Server (one of many). The issue seemed to be only with certain rows of this table as it migrated majority of the data without issues. Below is the complete error

System.Management.Automation.MethodInvocationException: Exception calling "Fill" with "1" argument(s): "'count' must be non-negative.
Parameter name: count" ---> System.ArgumentOutOfRangeException: 'count' must be non-negative.
Parameter name: count
at System.String.CtorCharCount(Char c, Int32 count)
at Oracle.DataAccess.Client.OracleDataReader.GetString(Int32 i)
at Oracle.DataAccess.Client.OracleDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable,
DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords,
IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at CallSite.Target(Closure , CallSite , Object , Object )
--- End of inner exception stack trace ---
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

Oddly, I am NOT getting any “count” as the error message would suggest. It was a simple SELECT.

Looking into the table, it had a CLOB and BLOB column besides other columns. I fought with the issue and narrowed down the problem to just one row when my PowerShell code SELECTed from the table.

How to identify the problem row(s)?

To find the row, simply loop through and keep SELECTing from the table and print out the ROWID. When it hits the problem row(s), you will know the ROWID and hence the row itself.

Is there corruption?

My first suspicion was that the row was corrupted. So, I ran a check on the table. As you might already know, one has to create the two tables to check for corruption and orphaned entries. Then, the call to DBMS_REPAIR.CHECK_OBJECT populates the table(s) with corruption related meta-data.


begin

  dbms_repair.admin_tables(
     table_name => 'REPAIR_TEST',
     table_type => dbms_repair.repair_table,
     action     => dbms_repair.create_action,
     tablespace => 'MY_TABLESPACE'
   );

end;

begin

  dbms_repair.admin_tables(
     table_name => 'ORPHAN_TEST',
     table_type => dbms_repair.orphan_table,
     action     => dbms_repair.create_action,
     tablespace => 'MY_TABLESPACE'
   );

end;

declare corr_count binary_integer;

begin

    corr_count := 0;

    dbms_repair.CHECK_OBJECT (
       schema_name       => 'MY_SCHEMA',
       object_name       => 'MY_TABLE',
       partition_name    => null,
       object_type       => dbms_repair.table_object,
       repair_table_name => 'REPAIR_TEST',
       flags             => null,
       relative_fno      => null,
       block_start       => null,
       block_end         => null,
       corrupt_count     => corr_count
       );

    dbms_output.put_line(to_char(corr_count));

end;
/

Well, this came back with nothing. Meaning, my table was not corrupted.

Could it be the size?

Then, it occurred to be that it could be due to the size of the data in the row that could be causing the issue.

To check the size of the rows, I ran the below query. My suspect column is the CRIXML_CF and this query gives me the size of the CLOB and BLOB columns and orders the results DESC by size of one of the suspect columns.

SELECT
    rowid as rowid1,
    DBMS_LOB.GETLENGTH("CRIXML_CF") len_crixml_cf,
    DBMS_LOB.GETLENGTH("CNTZIP_CF") len_cntzip_cf,
    a.*
FROM (MY_SCHEMA.MY_TABLE) a
WHERE DBMS_LOB.GETLENGTH("CRIXML_CF") > 500000
ORDER BY DBMS_LOB.GETLENGTH("CRIXML_CF") DESC

Sure enough, my problem row is the only one that is sticking out like a sore thumb with a size above 1 billion (number of bytes which is about 1 GB in that one column of that row!).

ROWID1          LEN_CRIXML_CF	LEN_CNTZIP_CF
AAARfrAAEAAAAv1AAL	1098766846	5432917
AAARfrAAEAAAAuyAAB	237799725	1690030
AAARfrAAEAAAAuaAAF	173354181	1126698
AAARfrAAEAAAAuaAAA	173348052	1125658
AAARfrAAEAAAAukAAK	173290622	1130015
AAARfrAAEAAAAuuAAC	173290622	1128185
AAARfrAAEAAAAv7AAE	172926257	1122596
AAARfrAAEAAAAvzAAD	172362276	1338564

Solution:

At this point, I just had to go with the instincts which told me that it could be the provider. I was using Oracle’s UnManaged provider (because I have an old Oracle 9i database against which the Managed provider does not work). If you get a chance, you should migrate from the Unmanaged provider to the Managed one.

Note that he connection string is slightly different. Please substitute the appropriate values in the connectionstring.

$connectionString = "User ID=MY_USER;Password=PASSWORD;Data Source=HOSTNAME:PORT/DATABASENAME;Connection Timeout=120;Pooling=true"
$connection = Get-OraConnection -ConnectionString $connectionString -DotNetProviderType Managed -Verbose

$sql = "SELECT a.*, ROWID AS ROWID1 FROM ( MY_SCHEMA.MY_TABLE ) a"
$rslt = Invoke-OraCmd sql $sql -as DataRow -Connection $connection

The above code is just to show the connection string. Someday, I will get to cleanup my PowerShell code library I created to work with Oracle and post it on GitHub!

Simply switching to the Managed provider made the error go away!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s