Home » RDBMS Server » Networking and Gateways » Calling a SQL SERVER procedure from Oracle heterogeneous service
Calling a SQL SERVER procedure from Oracle heterogeneous service [message #210712] Thu, 21 December 2006 23:14 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi Experts,
I have setup a heterogenous service from Oracle database to SQL SERVER and want to call a procedure through the DB_LINK. I am able to directly query the table from sql server but when I try to use the procedure, it gives "INVALID IDENTIFIER".

CREATE PROCEDURE usera.proc_1
@FromDate datetime,  
@ToDate  datetime
AS
SET XACT_ABORT ON
select count(status) TotalOrders, country,
status = 
CASE status
    WHEN 'I' THEN 'Invalid' 
    WHEN 'S' THEN 'New' 
    WHEN 'P' THEN 'In Process'
    ELSE 'Other'
END 
from usera.table1 WITH (NOLOCK)
where DeliveredTime BETWEEN @FromDate AND @ToDate
AND status != 'F'
group by country, status
order by country, status


SQL> select count(0) from usera.table1@HET_LINK.WORLD;

This works

whereas

declare
pendord_rc        sys_refcursor;
lv_ord_cnt        NUMBER;
lv_country		  VARCHAR2(100);
lv_status	      VARCHAR2(100);
begin
	pendord_rc := "usera".proc_1@HET_LINK.WORLD;
	loop
		fetch pendord_rc into lv_ord_cnt, lv_country, lv_status;
	    EXIT WHEN pendord_rc%NOTFOUND;
	end loop;
end;
/	


ERROR at line 7:
ORA-06550: line 7, column 16:
PLS-00201: identifier 'usera.PROC_1@HET_LINK.WORLD' must be declared
ORA-06550: line 7, column 2:
PL/SQL: Statement ignored

Any idea what I might be doing wrong?

Regards
Himanshu

Re: Calling a SQL SERVER procedure from Oracle heterogeneous service [message #210724 is a reply to message #210712] Fri, 22 December 2006 01:13 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Is there any way you can verfy this with
DBMS_HS_PASSTHROUGH package for calling procedures

declare
v_rows;
begin
v_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@HET_LINK.WORLD('call usera.proc_1');
end;

Never done this with sqlserver, but have a read on the DBMS_HS_PASSTHROUGH package

Also check this link
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4743719073967

[Updated on: Fri, 22 December 2006 01:15]

Report message to a moderator

Previous Topic: install oracle database first and then AD
Next Topic: Network Connection via ODBC
Goto Forum:
  


Current Time: Fri Dec 02 13:46:52 CST 2016

Total time taken to generate the page: 0.07602 seconds