Hi, I want to to execute Oracle Stored Procdeure through Execute SQL Task of SSIS 2008. Strored Procedure which I want to execute is present in Oracle 64 bit 11g database. Could you please provide the syntax for the same.Has anybody succeded in such activity yet? Thanks, Vaibhav

You have to use the syntax from oracle to call the SP. The Connection has to set to Oracle. If you don't have yet, create one.

Can you please provide me the syntax... Are there any limitations to call Oracle 64 bit procedure in SSIS 2008 package...

I can't tell you the syntax, because this is a SQL Server Integration Service Forum. Please ask some oracle guys.

Hi, I am calling the Oracle Stored Procedure through 'Execute SQL Task' in SSIS.I am using below syntax still I am not able to execute the SP successfully.Has anybody got success while calling Oracle 11g SP through SSIS 2008? Please share your methods/inputs. I am stuck with some urgent developments,not able to overcome this. Regards, Vaibhav

What is the statement that you use? What are the errors that you get? What connection manager do you use?MCTS, MCITP - Please mark posts as answered where appropriate.

This depends on the Connection manager , look at this page from msdn , and you can find different signatures for parameter names and parameter markers for different connection managers: http://msdn.microsoft.com/en-us/library/ms140355.aspxhttp://www.rad.pasfu.com

execute nameof_spBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Thank you for your help. I am using below syntax: begin declare i_cd NUMBER:=1000; i_cnt VARCHAR2(100):=to_char(sysdate,'yyyy/mm/dd'); i_cntl_nbr DATE:='10-APR-11'; i_nbr NUMBER:=1 ; begin Schema_Name.Package_Name.SP_Name(i_cd,i_cnt,i_cntl_nbr,i_nbr); end; I am using Ole Db Connection, Target database is Oracle 11g 64 bit database. Regards, Vaibhav

OK, so you're using an OLE DB connection. What OLE DB provider are you using? Regarding the statement that you provided: at first glance you are missing an END.MCTS, MCITP - Please mark posts as answered where appropriate.

Thanks Verbeek for your quick response. It was actually typo.I am using code as follows: declare i_cd NUMBER:=1000; i_cnt VARCHAR2(100):=to_char(sysdate,'yyyy/mm/dd'); i_cntl_nbr DATE:='10-APR-11'; i_nbr NUMBER:=1 ; begin Schema_Name.Package_Name.SP_Name(i_cd,i_cnt,i_cntl_nbr,i_nbr); end; I am using 'Oracle Provider for Oracle' provider.

As far as my limited knowledge of Oracle goes, the syntax of the code looks fine. Do you get any errors when you run the code?MCTS, MCITP - Please mark posts as answered where appropriate.

Hi, Whenevr I execute the above script in Oracle SQL Developer it executes fine but whenever I tried to call the same through 'Execute SQL Task' I am getting below error: I am getting below error: [Execute SQL Task] Error: Executing the query " declare i_prc_cd NUMBER ; i_prc_cnt VARC..." failed with the following error: "ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "Schema_Name.Package_Name", line 508 ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at line 12 ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. First Parameter is in parameter and other three parameters are in out parameters. Do I have to use any other syntax?Kindly suggest. Thanks, Vaibhav