I have Oracle Database 11g connected to MS Sql Server 2008 via dg4msql, and need to execute procedure on MS Sql with parameters from Oracle, and get dataset (table) as a result.
I'm not sure is it possible to call procedure directly from Oracle; One solution would be make function on MS SQL, and put select on procedure from Oracle, but what with parameters?
Is there some possibility to call this procedure from Oracle and get this dataset as a cursor?
Thanks in advance!
Edited by: mihaelradovan on 2012.04.26 14:35
You can call remote SQL*Server stored procedures using the 11g DG4MSQL.
This is detailed in the documentation -
Oracle® Database Gateway for SQL Server
11g Release 2 (11.2)
and there is also an example provided in the gateway install directory $ORACLE_HOME/dg4msql/demo which is also detailed in the documentation.
Yes, you are right, there is possibility to call remote SQL Server stored procedures, I took a look on documentation and examples.
I added HS_FDS_PROC_IS_FUNCT and HS_FDS_RESULTSET_SUPPORT parameters in my init***.ora file.
Anyway, when I try to execute example 7, get error:
PLS-00201: identifier 'GetDept@GTWLINK' must be declared
This line is : "GetDept"@GTWLINK(10, NAME);
Obviously, this line is not recognized as a procedure call. What I'm doing wrong?
Yes, of course I have DB Link in Oracle and procedure in SQL Server.
Btw, I can select data from SQL Server table.
I made one function in SQL Server, and I can not make select on this function, but in SQL Server I made view as select * from function_name, and select on this view from Oracle works. But problem is I have to call this procedure or function with parameters, and with view I can not do this.
So, I must call procedure or function with parameters directly. I made all by the book (Oracle® Database Gateway for SQL Server User’s Guide), but probably I miss something...
On SQL*Server can you make sure the user defined in the database link has the privileges on the GetDept procedure and what happens if you execute it as -
INSERT INTO LOCAL_GTW_DEPT VALUES (10, DNAME);
- where "owner" is the SQL*server owner of the procedure.
Thanks for letting us know it works now.
When I tested a trace showed it looked for the procedure first as the user in the database link and then as PUBLIC so adding the owner makes sure it finds the correct procedure.