I have a recursive procedure in a package in Oracle that traverses through a network. The recursive function runs down the network from the top and then returns values back up as the recursions close. On the way back up the network the function inserts the results of the step into an output table and will occasionally update records that should already exist (inserted in downstream recursions).
The logic for TRAVERSE_TRACE is roughly as follows:
Once the entire recursive procedure has finished running I am able to see the inserted data in the output table but none of values set with the UPDATE statement.
The full procedure can be found here, but here is the section with the UPDATE and INSERT operations.
-- if the current feature is isolating, use the current downstream ISOs as the downstream ISOs for this area. If not then leave as null
IF v_IsolatingFeature = 1 THEN
v_downstreamIsolators := v_downstreamIsolatorsStaging;
v_downstreamIsolatorsStaging := G3E_FID;
UPDATE HEDLPROD.ISOLATION_AREA_ASSETS IA
SET IA.DOWNSTREAM_ISO_FIDS = to_char(v_downstreamIsolators)
WHERE IA.UPSTREAM_ISO_FID = to_number(G3E_FID);
COMMIT;
END IF;
-- Insert the current entry into the results table
Insert Into HEDLPROD.ISOLATION_AREA_ASSETS (FEEDERHEAD_FID, FEEDER, G3E_FID, G3E_FNO, UPSTREAM_ISO_FID, UPSTREAM_ASSET_FID, UPSTREAM_ISOLATORS, DOWNSTREAM_ISO_FIDS, DEPTH)
VALUES (v_feederHeadFid, FEEDER, G3E_FID, G3E_FNO, v_currentIsolatorFid, upstreamAssetFid, v_upstreamIsolatorFids,v_downstreamIsolators, v_depthCount);
COMMIT;
By adding DBMS logging I have identified that the where clause in the UPDATE statement is returning 0 records even though the insert statements for those entries have occurred earlier in the procedure.
The DBMS logging shows that the rows to be updated are being inserted before the update statement but that the select statement returns no records.
Rows Inserted into output table = 1. ID: 16312200, UPSTREAM_ID: 16309677
Rows Inserted into output table = 1. ID: 16309676, UPSTREAM_ID: 16309677
Number of rows where UPSTREAM_ID = 16309677 is: 0
Updating DOWNSTREAM_ISO_FIDS to 16312200 where UPSTREAM_ID = 16309677
Rows updated = 0. ID: 16309677
If I execute the update statement manually outside of the procedure then it works as expected.
Why are the inserted rows not showing up immediately? What can I do to force the rows to insert or to delay the update? Please help!
This seems to be a scoping problem. According to your insert
Insert Into HEDLPROD.ISOLATION_AREA_ASSETS (FEEDERHEAD_FID, FEEDER, G3E_FID, G3E_FNO, UPSTREAM_ISO_FID, UPSTREAM_ASSET_FID, UPSTREAM_ISOLATORS, DOWNSTREAM_ISO_FIDS, DEPTH)
your ISOLATION_AREA_ASSETS table has a column called G3E_FID.
Your procedure definition also has a parameter called G3E_FID:
PROCEDURE TRAVERSE_TRACE(
ITER_COUNT IN NUMBER DEFAULT 0,
FEEDER IN VARCHAR2,
G3E_FID IN NUMBER,
...
In your update statement you do:
WHERE IA.UPSTREAM_ISO_FID = to_number(G3E_FID);
and it appears that you're expecting that to match IA.UPSTREAM_ISO_FID against the parameter G3E_FID; but that's not what will happen. The name resolution rules say:
If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.
Caution: When a variable or parameter name is interpreted as a column name, data can be deleted, changed, or inserted unintentionally.
So you can read that as (without the superfluous function call):
WHERE IA.UPSTREAM_ISO_FID = IA.G3E_FID;
which it will only count or update when those two columns have the same value. Hence finding no matches, in the count and in the update. ; as shown in this simplified fiddle. (When you do it manually later you hard-code the numeric value, so there is no confusion.)
You could specify the parameter version using the procedure name, with:
WHERE IA.UPSTREAM_ISO_FID = TRAVERSE_TRACE.G3E_FID;
(fiddle)
But it's generally better to avoid parameter names that match schema identifiers completely, which is often done by prefixing them.
In this case you already have a local variable v_G3E_FID you can use:
WHERE IA.UPSTREAM_ISO_FID = v_G3E_FID;
though it looks like you could rename for argument and all references to it to v_G3E_FID and remove the local variable (fiddle).
I would recommend you rename/prefix all of the procedure arguments to avoid similar issues or just confusion and ambiguity for others trying to understand the code.