By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table ISOLATION_AREA_ASSETS (
G3E_FID number,
UPSTREAM_ISO_FID number,
DOWNSTREAM_ISO_FIDS number
)
create PROCEDURE TRAVERSE_TRACE(
v_G3E_FID IN NUMBER
) IS
v_downstreamIsolators VARCHAR2(2048);
v_row_count NUMBER;
BEGIN
dbms_output.enable(null);
Insert Into ISOLATION_AREA_ASSETS (G3E_FID, UPSTREAM_ISO_FID, DOWNSTREAM_ISO_FIDS)
VALUES (16312200, 16309677, null);
Insert Into ISOLATION_AREA_ASSETS (G3E_FID, UPSTREAM_ISO_FID, DOWNSTREAM_ISO_FIDS)
VALUES (16309676, 16309677, null);
v_downstreamIsolators := '16312200';
SELECT COUNT(*)
INTO v_row_count
FROM ISOLATION_AREA_ASSETS
WHERE UPSTREAM_ISO_FID = v_G3E_FID;
DBMS_OUTPUT.PUT_LINE('Number of rows where UPSTREAM_ISO_FID = ' || v_G3E_FID || ' is: ' || v_row_count);
DBMS_OUTPUT.PUT_LINE('Updating DOWNSTREAM_ISO_FIDS to ' || v_downstreamIsolators || ' where upstream ISO FID = ' || v_G3E_FID);
UPDATE ISOLATION_AREA_ASSETS
SET DOWNSTREAM_ISO_FIDS = to_char(v_downstreamIsolators)
WHERE UPSTREAM_ISO_FID = v_G3E_FID;
DBMS_OUTPUT.PUT_LINE('Number of rows updated = ' || SQL%ROWCOUNT);
END TRAVERSE_TRACE;
/
select * from user_errors
begin
dbms_output.enable;
TRAVERSE_TRACE(16309677);
end;
/
1 rows affected
dbms_output:
Number of rows where UPSTREAM_ISO_FID = 16309677 is: 2
Updating DOWNSTREAM_ISO_FIDS to 16312200 where upstream ISO FID = 16309677
Number of rows updated = 2
select * from ISOLATION_AREA_ASSETS;
| G3E_FID | UPSTREAM_ISO_FID | DOWNSTREAM_ISO_FIDS |
|---|---|---|
| 16312200 | 16309677 | 16312200 |
| 16309676 | 16309677 | 16312200 |