Execute oracle stored procedure from DTS (no parameters)

[lemkepf]

Hello DTS gods! I come before you again with a question...
Were trying to create a DTS pacakge on our SQL server to push some data over to Oracle, then when the push is done, execute an exising oracle stored procedure to move that data around and do some funky stuff with that.

After reading around, i can find someone who has said "Yes it can be done" or "No it can't be done".
I figured that i could just use the "execute sql" with the oracle connection and it would execute the SP.

Here comes the problem. I can't figure out the right execution syntax. The stored procedure takes no parameters in or out.

Here is how i've tried calling(none work):
BEGIN
LSS_PKG_PROD_DESC.LSS_PRO_UPDATE_DESC();
END;

BEGIN
USER.LSS_PKG_PROD_DESC.LSS_PRO_UPDATE_DESC();
END;

LSS_PKG_PROD_DESC.LSS_PRO_UPDATE_DESC();

Any idea why they wont work? I get errors about things not being declared. I get stuff about the function doesn't exists.

I'm seriously stumped. Anyone have any experience with this? Or an idea to help? Thanks guys! It will be a huge help!
Paul

 

[nw97761]

Hi,

Has anybody been able to do this, as i'm having exactly the same problem?

I can execute the stored procedure fine within the oracle database, however when executing it a DTS job, i get the ora-900 error.

Therefore, does anybody know how to execute a oracle stored procedure from a sql server DTS job?

Thanks

 

[lemkepf]

It's sooo simple! You call the stored procedure the same way you execute it in ADO.
sooo....
{call PACKAGENAME.STOREDPROCEDURENAM()}

Hmmm... that doesn't look right. I'm doing this off the top of my head. Let me write back when i get to work with the right command structure.
Paul

 

[juanthompson]

I have the same issue, and I would love to know if you have found a solution to it. I am trying to call a stored procedure on an Oracle database from SQL Server 7 DTS. Any suggestions would be greatly appreciated.

Juan

 

[lemkepf]

let me get to work today and i'll post it....

 

[lemkepf]

Here is the exact call i used:
{call ADM.PKG_PROD_DESC.PRO_UPDATE_DESC()}

Just have an oracle connection, and execute that SQL statement in it. Simple enough...
Paul

 

[juanthompson]

Here's what I'm doing:

Have an OLEDB data source (Using the Oracle-provided OLEDB driver, not the MS version).

Have an Execute SQL Task. That task contains:

BEGIN
CMG_PS_STAGING_LOAD.LoadPayrollJEFromStagingToGL;
END;

This works fine when I execute it in Oracle directly, but fails when I run the DTS job.

Any insights?

Juan

 

[lemkepf]

Try it the way i suggested and it should work beautifully. (if it doesn't, try the MS provider.)
Paul