Hi,
I have one system running Oracle DB 11g XE with HS configured and working.
My local table "SUBORDER_EVENT" contains
suborder_code number(12)
amount number(6)
The remote DB is mysql and required dblink (OMS1) and connections configured
Remote table "SUBORDER"
code varchar(15)
amount integer(10)
I already have my suborder_code populated and need to fetch AMOUNT from remotedb OMS1 against each suborder_code in local DB. I am using the below to do that, but it is working for only one input suborder_code and giving error for more than 1
declare
val2 varchar2(15);
p_c integer;
nr integer;
begin
p_c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@oms1;
DBMS_HS_PASSTHROUGH.PARSE@oms1 (
p_c,
'select selling_price from suborder
where code = ?'
);
FOR rec in (select distinct SUBORDER_CODE as suborder_code
from SUBORDER_EVENT
where AGG_STATUS = '0' and SUBORDER_CODE = 17600900000)
LOOP
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@oms1 (p_c, 1, rec.suborder_code);
nr := DBMS_HS_PASSTHROUGH.FETCH_ROW@oms1(p_c);
DBMS_HS_PASSTHROUGH.GET_VALUE@oms1(p_c, 1, val2);
update suborder_event set amount = val2 where suborder_code = rec.suborder_code and agg_status = '0';
END LOOP;
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@oms1(p_c);
END;
In above code if I remove the clause "and SUBORDER_CODE = 17600900000", it fetches multiple suborders and I get error
ORA-01403: no data found
ORA-06512: at line 21
01403. 00000 - "no data found"
*Cause: No data was found from the objects.
*Action: There was no data from the objects which may be due to end of fetch.
Please advise on the resolution of this.