How should I keep a pointer to the parent record when I'm inserting child records in PL/SQL?

Question

I have a package that will be inserting a single parent record in one table, and dependent child records in another table with a FK on the parent_id. Each of these functions will be called by an external program, and all calls to insert children may not be contained within the same transaction.

I'm wondering if there is any way for me to avoid having to track the parent_id manually and pass it along in every procedure's parameter list. I've considered using the sys_context, but don't think that will work because it won't be in a single transaction.

Are there any other strategies for this or do I just need to suck it up and pass the parent_id to every method?

Answer 1

You could just use a package variable like this:

package body mypackage is

    g_parent_id integer;

    procedure insert_parent (...)
    is
    begin
        insert into parent (...) values (...)
        returning id into g_parent_id;
    end;

    procedure insert_child (...)
    is
    begin
        insert into child (parent_id, ...) values (g_parent_id, ...);
    end;
end;

The package variable persists as long as your database connection exists. This would not work in a stateless environment like a web application.

That said, I'd be in favour of keeping the procedure modular by passing the ID in each time. That way nothing unexpected can happen.

Answer 2

The key question is whether sessions in your calling application are pooled, or 'sticky'.

If the same connection / session is re-used by your external program for each transaction, then storing the parentId in a package variable will be fine.

If you have connection pooling then using package variables starts to become tricky / positively dangerous.

If the call to insert the children is in a different transaction, and you have connection pooling, I can see no way you can avoid telling the second transaction the required parentId.

Caveat : If you are more concerned with simplifying the API to the package than performance, and you have something along these lines . . .

For each database call in the external application, set something that uniquely identifies the calling process into the SYS_CONTEXT or a package variable (we have something like this, so that we can derive the calling method and 'real' rather than pooled d/b user).

Create a secondary key / index on the parent table, based on this unique identifier and time.

Create a function to retrieve the most recent ParentId for the current session (presuming that the unique identifier will be set correctly).

Use this function in your insert child.