July 31, 2011 at 3:17 pm
Hi,
I have to call an Oracle function from SQL Server 2005/2008.
This Oracle function has an input parameter that is varchar and 1 output parameter, number,
I tried to call it in various ways but it always gives some or other syntax error.
Could you please see what I am doing wrong, what is the correct way to call the function?.
I tried these ways-->
declare @InputPara varchar(100)
set @InputPara=’Hello’
declare @OutputPara bigint
EXECUTE ( ‘BEGIN ? := packagename.functionname(?,?); END;’, @InputPara, @OutputPara OUTPUT )at linkedservername;
select * from linkedservername..packagename.functionname('Hello')
select * from openquery
(
LinkedServerName,
‘SELECT * FROM packagename.functionname(”Hello”);’
)
When I call it using the openquery it gives me syntax error near Hello.
I tried enclosing hello in single quote, double quote and 3 single quotes, everytime a syntax error.
Thanks! -Janki
July 31, 2011 at 3:27 pm
Is RPC enabled on the linked server?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 31, 2011 at 6:27 pm
Yes, RPC is enabled.
Looks like it is the syntax issue.
Which one do you think is the right way to call the function? Is it using the Openquery ?
August 1, 2011 at 7:24 am
SJanki (7/31/2011)
Yes, RPC is enabled.Looks like it is the syntax issue.
Which one do you think is the right way to call the function? Is it using the Openquery ?
When I use
select * from openquery
(linkedservername,
'SELECT * FROM packagename.functionname(''Hello'') from dual;'
)
I get this error
OLE DB provider "MSDAORA" for linked server "***" returned message "ORA-00933: SQL command not properly ended.
Any idea?
August 1, 2011 at 7:37 am
i don't think you are supposed to include the packagename, are you?
for example, i've got a package that has a suite of 10 date functions and 6 string functions inside it, but when i need them, i just use the function names.
i haven't done it via openquery, but in regular cmds sent from a .net connection string.
Lowell
August 1, 2011 at 8:41 am
I am also able to call it from .NET application using System.Data.OracleClient and the connection string.
But due to some limitations I have to call it from the SQL Server only.
I was using the packagename while calling from the .net code.
I think if the function is in a package, package name can be given.
Removed the packagename -->still gives the same/syntax error.
August 2, 2011 at 6:12 am
SJanki (8/1/2011)
I am also able to call it from .NET application using System.Data.OracleClient and the connection string.But due to some limitations I have to call it from the SQL Server only.
I was using the packagename while calling from the .net code.
I think if the function is in a package, package name can be given.
Removed the packagename -->still gives the same/syntax error.
1 - You are correct, package name has to be included. Moreover, including schema name wouldn't hurt and certainly will ensure Oracle knows where to find the package.
2 - I'll go with the...
EXECUTE (Query, Parameters) AT LinkedServerName
...syntax.
I understand you have no problems calling the function from SQLPLUS, is that correct?
Would you mind in copy/pasting actual error stack?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 2, 2011 at 9:42 am
Hi Paul,
I tried this
declare @InputPara varchar(100)
set @InputPara='Hello'
declare @OutputPara bigint
EXECUTE ( 'BEGIN ? := packagename.functionname(?,?); END;', @InputPara, @OutputPara OUTPUT )at linkedservername;
Its giving me this error
Msg 7215, Level 17, State 1, Line 5
Could not execute statement on remote server 'linkedservername'.
I am not using SQLPlus
I am executing this statement from SQL Server 2005 Management Studio.
The linkedserver has been tested and is worknig fine. It lists all the tables and views from the Oracle DB and also when i do a select openquery on ant table it gives the result.
Not sure why for the function it says -->Could not execute statement on remote server 'linkedservername'.
I also rebooted the machine.
Thanks - Janki
August 2, 2011 at 9:53 am
SJanki (8/2/2011)
Hi Paul,I tried this
declare @InputPara varchar(100)
set @InputPara='Hello'
declare @OutputPara bigint
EXECUTE ( 'BEGIN ? := packagename.functionname(?,?); END;', @InputPara, @OutputPara OUTPUT )at linkedservername;
Its giving me this error
Msg 7215, Level 17, State 1, Line 5
Could not execute statement on remote server 'linkedservername'.
I am not using SQLPlus
I am executing this statement from SQL Server 2005 Management Studio.
The linkedserver has been tested and is worknig fine. It lists all the tables and views from the Oracle DB and also when i do a select openquery on ant table it gives the result.
Not sure why for the function it says -->Could not execute statement on remote server 'linkedservername'.
I also rebooted the machine.
'linkedservername' should be the actual name of your Linked Server - is it?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 2, 2011 at 10:51 am
['linkedservername' should be the actual name of your Linked Server - is it? ]
Yes, in the actual query, I have just replaced the actual linked-server-name with linkedservername and the actual package name with packagename and the actual function name with functionname.
and when I execute this-->
select * from openquery
(
linkedservername,
'SELECT * FROM packagename.functionname(''hello'');'
)
I get this error--> (Sorry, i have changed/replaced the linkedservername , packagename and the function name in the error message)
OLE DB provider "MSDAORA" for linked server "linkedservername" returned message "ORA-00933: SQL command not properly ended
".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT * FROM packagename.functionname('hello');" for execution against OLE DB provider "MSDAORA" for linked server "linkedservername".
April 17, 2012 at 9:16 am
SELECT *
FROM OPENQUERY(ORA_PRD,
'SELECT NAME, DIMORA.PK_TEST.TEST(ID, ''Hello'') FROM DUAL'
);
This works for me.
Maybe you should put the semicolon out of the query string.
With me it gave error.
Also, try using the Oracle Driver (OraOLEDB.Oracle) instead of MSDAORA.
And the latest Driver, even if it's an Oracle 8i you're connecting to.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply