This content has been marked as final. Show 8 replies
Mihael,1 person found this helpful
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?
Have you created a procedure in SQL*Server called GetDept and a database link called GTWLINK in the Oracle database ?
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...
Okay, thanks for the confirmation.
I'll check further and get back to you.
Hi,1 person found this helpful
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.
It seems now it works. I put "dbo". as prefix, and now it works.
Thanks a lot again.
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.