EXECUTE PROCEDURE FROM LINKED SERVER ORACLE

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

Answer 1

 

Use the following syntax:

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

Regards,

Answer 2

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

Answer 3

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

 

 

Answer 4

 

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'.

Answer 5

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

  

Answer 6

 

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

Answer 7

Hi,

    You could use

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

 

Thanks

Answer 8

Hey Mohammed,
You are awesome! I really don't know where did you get the correct syntax but it worked great!

Thanks a lot!

Answer 9

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? 

Answer 10

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

Answer 11

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.