caliing stored procedure issue

[abhijit]

Hi,

i have created linked server between my sqlserver2008 and Oracle10g.

the following query is working fine

select * from openquery (DCCOM,'select * from sample).

but if i try to call procedure


select
* from openquery(DCCOM, '{CALL scott.get_test({resultset 25, OUTPUT})}');

it gives an error as "

Msg 7357, Level 16, State 2, Line 1

Cannot process the object "{CALL scott.get_test({resultset 25, OUTPUT})}". The OLE DB provider "OraOLEDB.Oracle" for linked server "DCCOM" indicates that either the object has no columns or the current user does not have permissions on that object."

how to solve this issue. please help me in this

What is get_test? I tried to call a function from SQL Server Express to Oracle 9i database and it worked for me.

I used a different syntax.

select * from openquery(NTEST, 'select callabc(to_date(''10/01/2019'',''mm/dd/yyyy'')) from dual')

[karthikeyan]

Hi abhijit,

get_ test is my procedure name inoracle. could you please help me now

[abhijit]

Hi abhijit,

get_ test is my procedure name inoracle. could you please help me now

I will try the same thing with a stored procedure. callabc is a function in my case which accepts a date parameter. i am on Oracle 9i and SQL Server 2008 express edition.

[karthikeyan]

hi abhijit,

i am using Oracle11g and sqlserver2008. still facing the same issue.

i have tried using sqlserver 2005 also using MSDAORA.getting the error as follows

select * from openquery(DCCOM, '{CALL CCOM.get_test({resultset 25, ReturnVal})}')

error :
OLE DB provider "MSDAORA" for linked server "DCCOM" returned message "Argument name not found for PL/SQL procedure.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "{CALL CCOM.get_test({resultset 25, ReturnVal})}" for execution against OLE DB provider "MSDAORA" for linked server "DCCOM".

could you please help me in this

[abhijit]

hi abhijit,

i am using Oracle11g and sqlserver2008. still facing the same issue.

i have tried using sqlserver 2005 also using MSDAORA.getting the error as follows

select * from openquery(DCCOM, '{CALL CCOM.get_test({resultset 25, ReturnVal})}')

error :
OLE DB provider "MSDAORA" for linked server "DCCOM" returned message "Argument name not found for PL/SQL procedure.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "{CALL CCOM.get_test({resultset 25, ReturnVal})}" for execution against OLE DB provider "MSDAORA" for linked server "DCCOM".

could you please help me in this

when I attempt to call a stored procedure, i am getting the following message.

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "CALL delrelation('100081')". The OLE DB provider "OraOLEDB.Oracle" for linked server "PROD8" indicates that either the object has no columns or the current user does not have permissions on that object.