Hi. So for business rule reason,
INSERT'ing a new row is wrapped in a DEFINER function,
that returns the newly inserted row's OK (and integral ID).
And in the code calling that function, I was
SELECT * FROM table WHERE ID = insert_row_via_func(....)
to "save a round trip", combining the insertion and the select.
But of course, it didn't work, and in hindsight, the SCN for the
SELECT is chosen before the INSERT done inside the function,
so the new row is not seen, and the select returns nothing.
The work-around is easy, just make two statements.
But of course that's two round-trips.
I looked at DO blocks, to perform both statements in the same rtrip.
But DO block don't return anything. I guess I could have two functions,
the existing one just returning the ID, and another returning the table
row type, and the latter calling the former? That's just more code to
maintain.
Is my analysis correct? In terms of the problem?
And the solutions / work-arounds?
Thanks for any insights. --DD
