locked
EXECUTE PROCEDURE FROM LINKED SERVER ORACLE RRS feed

  • Question

  •  

    I AM TRYING TO RUN AN ORACLE  PROCEDURE FROM SLQ 2005, BUT I CAN NOT

     

    THE PROC NAME IS = ZZ_INTEK_FIX_ANAL_OIK  --- PROC UPDATES ONE TABLE

     

    PROVIDER MICROSOFT OLE DB PROVIDER FOR ORACLE

    LINKS SERVER NAME  = SENDB

    DATA SOURCE NAME = SEN_DB

    PROVIDER STRING MSDAORA

     

    STATEMENT :

    SELECT * FROM OPENQUERY(SENDB, '{CALL ZZ_INTEK_FIX_ANAL_OIK}')

     

    I GOT THIS MSG

     

    Msg 7321, Level 16, State 2, Line 1

    An error occurred while preparing the query "{CALL ZZ_INTEK_FIX_ANAL_OIK}" for execution against OLE DB provider "MSDAORA" for linked server "SENDB".

     

    ANY IDEAS PLS

    Tuesday, November 20, 2007 1:06 PM

Answers

  • Hi,

        Could you make sure that the procedure ZZ_INTEK_FIX_ANAL_OIK , exists in Oracle, under the login specified for SENDB linked server object ?

    Thahseen

      

    Monday, August 4, 2008 5:29 PM

All replies

  •  

    Use the following syntax:

    SELECT 1 FROM OPENQUERY(SENDB, 'select ZZ_INTEK_FIX_ANAL_OIK  from dual')

    Regards,
    Friday, May 2, 2008 9:55 AM
  • I'm not sure if OpenQuery works if the procedure doesn't return a results set.

    The following works for a simple Oracle procedure that does not return a results set.

     

    execute ('call <OracleSchema>.<OracleProcedureName>()') at <LinkedServerName>

     

    Regards,

    Carl

    Friday, May 23, 2008 2:23 PM
  • Please try  :

     

    EXECUTE ( 'begin ZZ_INTEK_FIX_ANAL_OIK; end;')  AT   SENDB;

     

    Also try ( If you have a Oracle stored procedure with parameters ):

     

    EXECUTE ( 'begin proc_test (?,?,?,?,?); end;', @aval, @bval, @cval, @dval, @r output) AT   LINKSvrtest;

     

    and try ( If you have a Oracle (Stored) function with parameters ) :

     

    EXECUTE ( 'begin ? := func_test (?,?,?,?); end;', @ret output , @aval, @cval, @dval, @r output) AT  LINKSvrTest;

     

    Thanks,

     

    Thahseen Mohammed

     



     

    Tuesday, May 27, 2008 7:04 PM
  •  

    I run this

     

    EXECUTE ( 'begin ZZ_INTEK_FIX_ANAL_OIK; end;')  AT   SENDB;

     

    and I got the following msg

     

     

    OLE DB provider "MSDAORA" for linked server "SENDB" returned message "One or more errors occurred during processing of command.".

    OLE DB provider "MSDAORA" for linked server "SENDB" returned message "ORA-06550: γραμμή 1, στήλη 7:

    PLS-00201: το αναγνωριστικό 'ZZ_INTEK_FIX_ANAL_OIK' πρέπει να δηλωθεί ( transaltion :  must be declared )

    ORA-06550: γραμμή 1, στήλη 7:

    PL/SQL: Statement ignored

    ".

    Msg 7215, Level 17, State 1, Line 3

    Could not execute statement on remote server 'SENDB'.

    Tuesday, July 15, 2008 11:03 AM
  • Hi,

        Could you make sure that the procedure ZZ_INTEK_FIX_ANAL_OIK , exists in Oracle, under the login specified for SENDB linked server object ?

    Thahseen

      

    Monday, August 4, 2008 5:29 PM
  •  

    BY USING THE SAME WAY,

    EXECUTE ( 'begin ZZ_INTEK_FIX_ANAL_OIK; end;') AT SENDBTEST;

     

     

    IS IT POSSIBLE TO RUN VIEW FROM ORACLE

     

     

     

    LIKE  :  SELECT * FROM INTEK_SSS WHERE YEAR = 2008 AND MONTH = 8

     

     

    THANKS

    GORE

    Thursday, September 11, 2008 9:15 AM
  • Hi,

        You could use

    select * from Openquery ( SENDBTEST, 'SELECT * FROM INTEK_SSS WHERE YEAR = 2008 AND MONTH = 8')

     

    Thanks

    Thursday, September 11, 2008 2:35 PM
  • Hey Mohammed,
    You are awesome! I really don't know where did you get the correct syntax but it worked great!

    Thanks a lot!
    Friday, March 6, 2009 11:39 PM
  • Hi Mohammed, thank you very much forthe  sql statement to run a stored procedure from sql server.

    I have to call an oracle prcedure that uses an parameter that is decared as an record datatyp inside the oracle procedure declared for in out. In T-sql  there is no equivalent for the record datatype ins't it? So I tried with t-sql CURSOR datatyp. But it dosnt work.

    Do you have any idea? 

    Wednesday, May 25, 2011 11:19 AM
  • You can easily transfer data from ORACLE to SQL Server with the SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/

    The following article deals with ORACLE linked server:

    http://www.sqlusa.com/bestpractices/linktooracle/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Wednesday, September 26, 2012 2:14 PM
    Wednesday, May 25, 2011 1:01 PM
  • Thanks for your help. but i am getting error when I pass 2 output parameters. 

    for 1 out parameter its working fine but when i use two output parameters it gives this error:

    "Msg 7215, Level 17, State 1, Procedure Function_Name, Line 28 Could not execute statement on remote server 'linkserver_name'."

    Here is the query:

    DECLARE @RET INT

    EXECUTE ('BEGIN ? := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,?,?); END;', @RET OUTPUT
    , @USER_NAME, @FIRST_NAME, @MIDDLE_NAME, @LAST_NAME, @DATE_OF_BIRTH
    , @MOTHER_MAIDEN_NAME, @MOBILE_NO, @P_NATIONAL_ID, @NATIONAL_ID_EXP_DT, @PASSPORT_NO, @PPT_EXP_DATE 
    , @ERROR_CODE OUTPUT, @ERROR_DESCRIPTION OUTPUT )
    AT linkserver_name;

    waiting for the reply.



    • Edited by Obaid Altaf Monday, July 1, 2013 7:53 AM changed
    Sunday, June 30, 2013 1:46 PM