Error when executing Oracle stored proc in DTS

Question

Post reply

Error when executing Oracle stored proc in DTS

gvaldridge

SSC-Addicted

Points: 478
More actions
September 26, 2006 at 12:13 pm

#367450

I'm trying to execute an Oracle stored procedure inside of an Execute SQL Task in my DTS package.
My code is just "exec proc_y". It runs fine within my Oracle client.
When I try to run that particular step, I get an "ORA:00900 Invalid SQL Statement" error.
I've tried using both the ODBC version of the Oracle driver and the OLE DB version for the "Existing Connection" property of the Execute SQL Task. I get the same error with both.
I can run individual statements such as "truncate table xxx". And those work with no errors. I've never tried to run Oracle statements within DTS before and I couldn't find a whole lot of information on the web about how to do this either. Perhaps there are some syntax idiosyncracies I'm not aware of when doing this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

gvaldridge SSC-Addicted Points: 478 More actions · Answer 1

Wow. Lately I don't get any responses to my posts.

Any particular reasons why? Was I not descriptive enough? I searched the forums before posting.

Loner SSC-Insane Points: 21279 More actions · Answer 2

In PL/SQL when you want to exec a procedure, the syntax is

Begin

ProcedureName();

End

It is different from SQL Server.

I did some research to exec Oracle procedure in DTS. I don't think you can do it. I only copied the data from Oracle to SQL Server.

What does your oracle procedure do? Can you change it to use script and transfer data task?

gvaldridge SSC-Addicted Points: 478 More actions · Answer 3

My Oracle proc populates a staging table and then I use a cursor in order to update other tables according to the data in the staging table (ie update TableA set value1 = cursor.value1 where value2 = cursor.value2).

Loner SSC-Insane Points: 21279 More actions · Answer 4

Loner

SSC-Insane

Points: 21279

October 3, 2006 at 10:47 am

#663906

Why can't you run the procedure in Oracle?

gvaldridge SSC-Addicted Points: 478 More actions · Answer 5

I'm doing this as part of a data conversion, where I've been told I need to update certain account data in SQL Server and Oracle.

I was hoping to do this within the same DTS package, since it seems logical to me to execute both sets of code in one place. Otherwise, you're right I would just run this procedure within my Oracle client.

I think I may have found a workaround. I'm testing out having the package execute a VB app which will execute the stored procedure in Oracle. It seems a little bass ackwards to me to HAVE to do it this way, but the less manual tasks I have on conversion day, the less chance I have to screw things up.

Loner SSC-Insane Points: 21279 More actions · Answer 6

I don't know what do you do in your oracle procedure. If it is just a query and update statement, then you can use DTS package to run the query and update statement and transfer the data to SQL Server table.