Discussion:
SSIS variable to Oracle parameter data type error
(too old to reply)
vnapoli
2006-10-04 15:31:01 UTC
Permalink
I am trying to take a SSIS String type variable and pass it as a parameter to
a varchar2 parameter in an Oracle pl/sql stored procedure and get a binding
error (conneting via Microsoft OLE DB provider for Oracle).

Error Message:
"ORA-01008: not all variables bound.

SSIS variable (XYZ) is global in scope and data type of string.

Oracle procedure:

CREATE OR REPLACE
PROCEDURE TESTPROC2
(XYZ VARCHAR2) AS
BEGIN
NULL;
END;

Calling sql is
Declare
begin
vnapoli.testproc2 (?);
end;

Parameter mapping is
variable Name Direction dataytpe Parameter Name
XYZ Input varchar XYZ

Code page: 1252

It is looking like SSIS is not able to pass a string variable datatype to
oracle correctly. Any help would be awesome.
Charles Kangai
2006-10-04 16:54:01 UTC
Permalink
In your parameter mapping, try using 0 instead of specifying parameter name
explicitly. Also, check your syntax for calling the stored procedure. I would
first test your calling SQL in SQL Plus to make sure there is no problem
there.


Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm
email: charles at kangai.demon.co.uk
Post by vnapoli
I am trying to take a SSIS String type variable and pass it as a parameter to
a varchar2 parameter in an Oracle pl/sql stored procedure and get a binding
error (conneting via Microsoft OLE DB provider for Oracle).
"ORA-01008: not all variables bound.
SSIS variable (XYZ) is global in scope and data type of string.
CREATE OR REPLACE
PROCEDURE TESTPROC2
(XYZ VARCHAR2) AS
BEGIN
NULL;
END;
Calling sql is
Declare
begin
vnapoli.testproc2 (?);
end;
Parameter mapping is
variable Name Direction dataytpe Parameter Name
XYZ Input varchar XYZ
Code page: 1252
It is looking like SSIS is not able to pass a string variable datatype to
oracle correctly. Any help would be awesome.
vnapoli
2006-10-05 22:12:02 UTC
Permalink
PL/SQL procedure works as expected from SQL PLUS and Oracle SQL Developer.
Changed parameter name to reference of 0. Same results. ORA-01008: not
all variables bound.
Post by Charles Kangai
In your parameter mapping, try using 0 instead of specifying parameter name
explicitly. Also, check your syntax for calling the stored procedure. I would
first test your calling SQL in SQL Plus to make sure there is no problem
there.
Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm
email: charles at kangai.demon.co.uk
Post by vnapoli
I am trying to take a SSIS String type variable and pass it as a parameter to
a varchar2 parameter in an Oracle pl/sql stored procedure and get a binding
error (conneting via Microsoft OLE DB provider for Oracle).
"ORA-01008: not all variables bound.
SSIS variable (XYZ) is global in scope and data type of string.
CREATE OR REPLACE
PROCEDURE TESTPROC2
(XYZ VARCHAR2) AS
BEGIN
NULL;
END;
Calling sql is
Declare
begin
vnapoli.testproc2 (?);
end;
Parameter mapping is
variable Name Direction dataytpe Parameter Name
XYZ Input varchar XYZ
Code page: 1252
It is looking like SSIS is not able to pass a string variable datatype to
oracle correctly. Any help would be awesome.
Loading...