SSIS : too many bind variables

[wawanco]

Hi,
I'm using:

• Visual Studio 2013
  dotConnect for Oracle 7.9
  SQL Server Data Tools 12

I try to insert data into an Oracle table using SSIS.

When I have less than 5000 rows (i just add ROWNUM < 5000 at the end of the query), everything works fine.

But when I have more than 5000 rows I have the following error :

Code: Select all

[ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is: ORA-06550: line 5463, column 205:
PLS-00183: too many bind variables

Have you ever encountered this issue ?

Edit : I also tried with dotConnect 8.4 : same issue

[Pinturiccio]

We could not reproduce the issue. Please provide the following:
1. DDL/DML scripts of you table. If a DML script is big you can send us the first couple of rows;
2. The actions you perform:
2.1. Which ADO NET Source component do you use, and with which provider;
2.2. Which ADO NET Destination component do you use;
3. Is the issue reproduced if you perform the same actions with another table?

[wawanco]

Thanks for your answer.

1. DML of my table

Code: Select all

CREATE TABLE EXT_STN_REL_RELCOM
(
  LOGIN       INTEGER,
  NUMACH      INTEGER,
  CODCLI      VARCHAR2(64 CHAR),
  CODNAF      VARCHAR2(6 CHAR),
  ENSACH      VARCHAR2(40 CHAR),
  NOMACH      VARCHAR2(255 CHAR),
  SIREN       VARCHAR2(9 CHAR),
  NUMVAT      VARCHAR2(64 CHAR),
  IDNATIONAL  VARCHAR2(50 CHAR),
  ATR_ID      VARCHAR2(66 CHAR),
  CODCLIENT   VARCHAR2(64 CHAR),
  ENTJUR      INTEGER
)

2. The actions I perform

My SSIS flow is simplest possible flow :
[SRC] --> [DST]

SRC component has the following query :

Code: Select all

SELECT        LOGIN, NUMACH, CODCLI, CODNAF, ENSACH, NOMACH, SIREN, NUMVAT, IDNATIONAL, ATR_ID, TYPE_FICHE, CODCLIENT, ENTJUR
FROM            STN_REL_RELCOM
WHERE        TYPE_FICHE = 0

2.1 Which ADO NET Source component do you use, and with which provider

I use base ADO NET source component provider : .Net Providers\dotConnect for Oracle

2.1 Which ADO NET Destination component do you use

I use base ADO NET destination component provider : .Net Providers\dotConnect for Oracle

3. Is the issue reproduced if you perform the same actions with another table

I have try with another table, another server, another Oracle instance and I have the same issue.
I also try with another ETL (with JDBC access) and everything works fine.

[Pinturiccio]

We are investigating the issue and will post here about the results as soon as possible.

[Pinturiccio]

We could not reproduce the issue with your script. Please provide us more information:
1. What connection mode do you use, OCI or Direct;
2. If you use OCI connection mode, tell us, which client version you use;
3. Oracle server versions you use in source and destination components;
4. Call stack of the error;
5. dotConnect for Oracle version;
6. Please create and send us a test package that reproduces the issue.

[mezulity]

Hi, I am having the same issue too. I am loading about 300 Million records with a CLOB datatype in Oracle using ADO.NET destination.

[mezulity]

Using ADO.NET/Oracleclient Data Provider
Call stack of error
ORA-06550: line 4099, column 1:
PLS-00183: too many bind variables

[mezulity]

Oracle version 12.1.0.2

[Pinturiccio]

Using ADO.NET/Oracleclient Data Provider

We support only Devart's products. Try using dotConnect for Oracle instead of OracleClient Data Provider.

dotConnect for Oracle supports SSIS only in the Professional edition. For more information, please refer to http://www.devart.com/dotconnect/oracle/editions.html

dotConnect for Oracle has a Trial version which allows you to evaluate dotConnect for Oracle during 30 days since the provider installation. Trial version of dotConnect for Oracle offers the same features as the Professional edition. You can download the Trial version of dotConnect for Oracle from our site http://www.devart.com/dotconnect/oracle/download.html

Is the issue reproduced with dotConnect for Oracle?