July 16, 2004 at 6:55 am
Hi,
I need to call an Oracle Stored Procedure from a DTS package. The stored procedure will return either a single-row rowset or a number of distinct paremeters (the procedure has not been developed yet and so we can state how we want the data to be returned) that will be used to populate a SQL Server table.
I've seen some postings that can execute a stored procedure but these don't appear to return data.
It would be of great help if someone who has done this in the past could help with this.
Thanks,
Ian
July 16, 2004 at 9:04 am
Try this:
1) Write a Oracle .sql file that executes the SP.
2) Create a batch file (.bat or .cmd extension) that calls up SQL*Plus and executes the .sql file
3) Run the batch file in DTS using the "Execute Process" task
July 16, 2004 at 9:04 am
Try this:
1) Write a Oracle .sql file that executes the SP.
2) Create a batch file (.bat or .cmd extension) that calls up SQL*Plus and executes the .sql file
3) Run the batch file in DTS using the "Execute Process" task
July 16, 2004 at 9:04 am
Try this:
1) Write a Oracle .sql file that executes the SP.
2) Create a batch file (.bat or .cmd extension) that calls up SQL*Plus and executes the .sql file
3) Run the batch file in DTS using the "Execute Process" task
July 16, 2004 at 9:18 am
Thanks for the suggestion, but how would I then get the data back into the DTS Package.
I've managed to come up with a bit of a workaround by getting the SP to write to the results to a table and I can then use a standard Oracle connection to query the table. Not ideal, but it works.
Ian
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply