Post reply

Oracle Stor Proc from SQL

  • July 22, 2004 at 3:22 am

    #360418

    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

    #515618

    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

    #515619

    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

    #515855

    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

    #515857

    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

    #515966

    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