July 22, 2004 at 3:22 am
Hi Everyone,
I have a serios problem, I want to execute an Oracle Store Proc from SQL. Is this possible, used the following methods and non is working. My schema is FM and my proc is sp_test and noparameters and I am calling this from MS SQL
SELECT * FROM OPENQUERY(ISLPBDEV01, 'call FM.sp_Test()')
SELECT * FROM OPENQUERY(ISLPBDEV01, 'exec FM.sp_Test()')
July 22, 2004 at 3:27 am
have u tried this ?, what was the result
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
July 22, 2004 at 3:30 am
Yes I did and I get this error
Could not process object 'call FM.TDP_LOADER.insert_exec_alloc()'. The OLE DB provider 'MSDAORA' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='MSDAORA', Query=call FM.TDP_LOADER.sp_Test()'].
July 23, 2004 at 6:44 am
The Oracle database stores object names as ALL CAPS. Try entering the proc name in capital letters, i.e. SP_TEST.
July 23, 2004 at 6:52 am
I used ALL CAPS, but still got the same error. I don't think you can call Oracle store proc from SQL
July 23, 2004 at 2:27 pm
You can call an oracle stored procedure from sql. I do it in a DTS package. I create a connection to Oracle using the Microsoft ODBC driver for Oracle. I then create a SQL task that uses the Oracle connection to execute the stored procedure. One thing to not is that you need to use PL/SQL syntax - something like:
BEGIN
SCHEMA.PKG_NAME.PROCEDURE_NAME
END;
Hope this helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply