Using Cursor to update table...

[citychap26]

Hi Guys,

I'm actually a Sybase boy but doing a little Oracle at the moment. I'm trying to update table 2 based on the results of my cursor:

CREATE OR REPLACE PROCEDURE Step4a
IS
v_SYS_FAC_ID FACILITIES%ROWTYPE;
CURSOR lcur_name IS
SELECT *
FROM FACILITIES x
WHERE x.src_sys_code = 'RMP'
AND x.sys_facility_id IN
( SELECT /*+ all_rows materialize */
REPLACE( f.acbs_facility_reference, '/', '_' )
FROM FAC_LEVEL_MATCH f
INTERSECT
SELECT x.sys_facility_id
FROM FACILITIES x
WHERE x.src_sys_code = 'RMP');
BEGIN
OPEN lcur_name;
LOOP
FETCH lcur_name INTO v_SYS_FAC_ID;
EXIT WHEN lcur_name%NOTFOUND;
---DBMS_OUTPUT.PUT_LINE(v_SYS_FAC_ID.SYS_FACILITY_ID || v_SYS_FAC_ID.FACILITY_ID || v_SYS_FAC_ID.PROD_PD_ID);
DBMS_OUTPUT.PUT_LINE(lcur_name%ROWCOUNT);
UPDATE FAC_LEVEL_MATCH ff
SET ff.LEVEL1_SYS_FACILITY_ID = v_SYS_FAC_ID.SYS_FACILITY_ID
,ff.LEVEL1_FACILITY_ID = v_SYS_FAC_ID.FACILITY_ID
,ff.LEVEL1_PROD_PD_ID = v_SYS_FAC_ID.PROD_PD_ID
,ff.LEVEL1_AGREED_LIMIT = v_SYS_FAC_ID.AGREED_LIMIT_AMT
WHERE REPLACE(ff.acbs_facility_reference, '/', '_' ) = v_SYS_FAC_ID.SYS_FACILITY_ID
AND LEVEL1_SYS_FACILITY_ID IS NULL;
DBMS_OUTPUT.PUT_LINE('Updated');
END LOOP;
COMMIT;
END Step4a;
/

Can you guys tell me why this is not updating FAC_LEVEL_MATCH

Cheers

SK

 

[Dagon]

The most likely explanations would be that the cursor isn't returning any rows or that the update doesn't find anything to update.

Add a couple of diagnostic statements:

After cursor fetch:

if lcur_name%notfound then
dbms_output.put_line('Cursor returned no rows');
end if;
...

After update:

dbms_output.put_line('Number of rows updated: '||sql%rowcount);

 

[citychap26]

Hi Dagon,

The cursor returns fine however the number of rows update is zero.

I wonder if the problem is in this:

WHERE REPLACE(ff.acbs_facility_reference, '/', '_' ) = v_SYS_FAC_ID.SYS_FACILITY_ID

??

SK

 

[Dagon]

Quite likely. Dump out the value of your v_SYS_FAC_ID.SYS_FACILITY_ID variable to see what it returns, then run a query to see if it is matching correctly:

select * from FAC_LEVEL_MATCH ff
WHERE REPLACE(ff.acbs_facility_reference, '/', '_' ) = ???

Check you having got any trailing spaces on the end of v_SYS_FAC_ID.SYS_FACILITY_ID which aren't in acbs_facility_reference. You can see if you've got any trailing spaces by contenating something to the variable (or printing out its length) e.g.

dbms_output.put_line(v_SYS_FAC_ID.SYS_FACILITY_ID||';');