Cannot find data after commiting in PL/SQL procedure?

Question

Hello,

I have code snippet in my PL/SQL procedure that does the following:

INSERT INTO payment_operations (id, subscriber, amount, description) VALUES (payment_id, 1234, 5, 'Test');
COMMIT;

SELECT subscriber INTO test_subscriber FROM payment_operations_view WHERE id = payment_id;

After this I get an exception "no_data_found"! However, if I do the same SELECT statement myself after running the procedure, I see the record.

Note that I am selecting from a view, and not directly from the table. Why I cannot see this data right after insertion?

Answer 1

Explicitly put a commit after the insert statement and check.

To me it appears that select statement is running before the commit actually happens after insert.

Answer 2

Looks like the view you are referring to is a 'materialized' view. If yes, try this code snippet to refresh the view manually before you fetch the data:

...
    INSERT INTO payment_operations (id, subscriber, amount, description) VALUES (payment_id, 1234, 5, 'Test');

    COMMIT;

    DBMS_SNAPSHOT.REFRESH( 'payment_operations_view','c');
    SELECT subscriber INTO test_subscriber FROM payment_operations_view WHERE id = payment_id;

    DBMS_OUTPUT.PUT_LINE('--> ' || test_subscriber);

...

Hope this helps.

Answer 3

This is a hunch:

Does the payment_options table have a column payment_id?

I ask because in the following statement, within PL/SQL, if payment_id exists as a column, then the column is going to be used not the local PL/SQL variable:

SELECT subscriber 
INTO test_subscriber 
FROM payment_operations_view 
WHERE id = payment_id;

Since it is using the payment_id column, if it exists, and since it was not set in the insert you might be doing where id = null which never evaluates to true.

I use v_ to signify variables. So your snippet would become (with the rest of the procedure changed accordinly):

INSERT INTO payment_operations (id, subscriber, amount, description) 
VALUES (v_payment_id, 1234, 5, 'Test');    
COMMIT; 

SELECT subscriber 
INTO v_test_subscriber 
FROM payment_operations_view WHERE id = v_payment_id;

Answer 4

You're inserting into the base table, then selecting from a view. Does the view have any filter conditions? If so, it's possible that the data you've inserted simply doesn't match the view's conditions.

As others have pointed out, it's also possible that the view is a materialized view, in which case it may need to be refreshed.

I have to ask ... why does the logic require selecting out data you've just inserted anyway? Unless you expect something else to have modified the data between the INSERT and SELECT, why not just use the subscriber value that you just inserted instead of querying to get it again?