How To Execute an Oracle Stored Procedure from DTS
In SQL Server 2000 DTS
How do I call and oracle stored procedure?
I've tried using the Execute SQL task with the
Exec <my procedure name> ;
and it errors.
I've been searching for the answer on how to execute Oracle Stored procedures from DTS without any luck.
The stored procedure creates the table and data that I want to pump into SQL Server.
Any help would be greatly appreciated.
Thanks!
April 15th, 2006 12:04am
What ConnectionType are you using? What error message are you getting?
Free Windows Admin Tool Kit Click here and download it now
April 17th, 2006 8:27pm
Connection type is Oracle provider for ole db
When I put the following syntax in an execute sql task, I recieve an invalid SQL statement error:
exec domainName.<my procedure name>
April 17th, 2006 10:24pm
Use this syntax: SQLStatement={call <procedure name>}
Free Windows Admin Tool Kit Click here and download it now
April 17th, 2006 11:20pm
Hi,
I tried this syntax, but I'm still with this problem.
How did you resolve this issue ?
Did you have success using this syntax ?
Thanks
October 19th, 2006 11:46pm
Hi Ewata,
The syntax changes depending on the connection type you're using. What type of connection are you using?
~Matt
Free Windows Admin Tool Kit Click here and download it now
October 20th, 2006 12:40am
Hi Matt,
I'm using "Oracle Provider for OLE DB" and I've tried the syntax: SQLStatement = { call<sp_name> }.
My Oracle Stored Procedure runs efficiently at Oracle client, but I've taken error messages when calling from DTS.
Thanks for reply.
October 20th, 2006 7:32pm
I don't have access to an oracle server right now to confirm, butI believe thatsyntax should work.
The SQLStatement property should be set to:
{ call my_sp() }
Make sure SQLSource Type is set to "Direct Input".
What is the exact error you are getting?
~Matt
Free Windows Admin Tool Kit Click here and download it now
October 20th, 2006 7:46pm
That's right. I've tested that syntax with a simple code in the stored procedure and the DTS worked successifully. When I tried a more "complex" code like "insert into <table> select <field list> from ..." the following message error occurs:
ORA-01861: literal does not match format string
ORA-06512: at <procedure name>, line 9
ORA-06512: at line 1
October 20th, 2006 9:26pm
Hi,
Where do we set this SQLSourceType to 'Direct Input'? is it in ExecuteSQLTask? or Transform Data Task?
I am getting error as "Provider cannot derive parameter information and SetParameterInfo has not been called" when I try like this.
{call schema_name.proc_name(?,?,?,?,?)}
Free Windows Admin Tool Kit Click here and download it now
December 20th, 2010 10:44pm