sql - Rows inserted in Oracle procedure not available until after the procedure completes - Stack Overflow

admin管理员组

文章数量:1133719

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:

1. Find connected nodes downstream
2. Call TRAVERSE_TRACE for each of the downstream nodes in the network
3. Optionally update the output table entries below it
4. Insert the current node into the output table

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!

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:

1. Find connected nodes downstream
2. Call TRAVERSE_TRACE for each of the downstream nodes in the network
3. Optionally update the output table entries below it
4. Insert the current node into the output table

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!

• sql
• oracle-database
• plsql
• sql-update
• sql-insert

Share Improve this question Follow edited Jan 8 at 4:07 David Klein Ovink asked Jan 8 at 2:24 David Klein OvinkDavid Klein Ovink 311 silver badge33 bronze badges New contributor David Klein Ovink is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 4

• You are using a lot of language that is specific to your business case, none of which we'd understand. If the update is affecting 0 rows, then focus on the where clause you have: WHERE IA.UPSTREAM_ISO_FID = to_number(G3E_FID) and think about what that's doing and when it's firing in your recursion logic. The code you've shown above does not help us because there is no obvious relationship between the value you originally inserted into the table and what you're looking for it to have in your later update where clause. It might make sense programmatically elsewhere, but that's hidden to us. – Paul W Commented Jan 8 at 3:25
• Hi Paul, thanks for you comments. I have simplified the language in the post and removed much of the business jargon. The same WHERE clause outside of the procedure successfully returns the entries expected so I believe it is correct. The values as inserted initially are mostly null. The row should already exist due to the order of the recursion; this is also confirmed by the DBMS output showing the rows inserted before the where/update clauses. – David Klein Ovink Commented Jan 8 at 4:16
• So I know nothing about Oracle, but from what you describe, it sounds like you are using a cursor or similar method to iteration through records? And I wonder whether this uses a snapshot of the table which doesn't get updated while you are working with the dataset? Or along a similar line, the transaction hasn't been committed and therefore the new rows are showing? If you are 100% sure the logic is correct, then these are the sorts of things you'd need to consider. – Dale K Commented Jan 8 at 6:07
• Can you knock up a test case that demonstrates the behaviour you're seeing, that we can run ourselves? (db<>fiddle would be a good place to create it.) That way we should be able to help you identify where the issue is occurring... that is, if you don't spot it yourself as you're creating the test case! – Boneist Commented Jan 8 at 9:26

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 4

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.

本文标签： sqlRows inserted in Oracle procedure not available until after the procedure completesStack Overflow