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!
SQL Jana 