caliing stored procedure issue

[karthikeyan]

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

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

[abhijit]

So now, what I did was wrapped the stored procedure inside a function. I can now make a call to the function. I suggest you try doing the same thing on your end.

[karthikeyan]

Hi abhijit,

So r u trying to say should i need to call the stored procedure inside the function or should i write my logic in function what ever i have written the procedure . could you please

[abhijit]

Hi abhijit,

So r u trying to say should i need to call the stored procedure inside the function or should i write my logic in function what ever i have written the procedure . could you please

You could do either. Before you do that, however, could you check if you're able to call a simple function from SQL Server. That will help you establish if that's an option available to you.

[leinad31]

Please note that post #2 used select func from dual... due to data type conversion along with other issues, passing only SQL resultsets (using ANSI compliant data types) instead of function return types (DB specific) is the way to go. Also, SQL can't handle procedures with OUT parameters, in which case wrap procedure as a function.