This discussion is archived
1 Reply Latest reply: Dec 2, 2012 3:50 PM by rp0428 RSS

JDBC mutiple Inserts from java side (want to keep the session same)

scottjhn Newbie
Currently Being Moderated
Oracle 11 g r2 on Linux.

TABLES:
parent_table (primary key id)
child_table (foreign key id)

PACKAGES:
pkgParent inserts into parent_table
pkgChild inserts into child_table


Java Side:

general_func_execute{

call function 1()

if success, commit()

}


function 1{
call1 = conn.prepareCall to execute pkgParent.sp_insert(to update parent_table)
call1.execute();

populate bean's id (reference key)

--> call function 2(pass the bean)

}


function 2{
call2 = conn.prepareCall to execute pkgChild.sp_insert(to update child table)
call2.execute();
}

ERROR: foreign key violation. the key does not exist in parent_table.

I believe this is because the call1 and call2 were executed in two separate sessions so that the session one's sql result can not be seen by session two.

So, I intended to keep both call1 and call2 in the same session by initiating the conn

conn.prepareCall("begin " +
                    "dbms_session.set_identifier(?);" +
                    "dbms_application_info.set_client_info(?);" +
                    "pkg_vpd.sp_set_context(?);" +
"end;");

//all ids are set to '0'. pkg_vpd.sp_set_context --> dbms_session.set_context(...)

But still, ERROR: foreign key violation. the key does not exist in parent_table.

How can I keep the two separate connection in the same session?


Thanks to help

P.S. All packages are separately tested on the Oracle side and all are successful. That is, when I logged in as the existing user and executed pkgParent
pkgChild in the same session, it worked.

More info on this link
maintain the same session identifier for multiple sessions
  • 1. Re: JDBC mutiple Inserts from java side (want to keep the session same)
    rp0428 Guru
    Currently Being Moderated
    >
    I believe this is because the call1 and call2 were executed in two separate sessions so that the session one's sql result can not be seen by session two.
    >
    Since you don't show any actual code there is no way for anyone but you to knonw for sure. You posted this
    general_func_execute{
    call function 1()
    if success, commit()
    }
    Was the INSERT of the parent record committed or not? If it was it doesn't matter if a different session was used for the child record.
    >
    So, I intended to keep both call1 and call2 in the same session by initiating the conn

    conn.prepareCall("begin " +
    "dbms_session.set_identifier(?);" +
    "dbms_application_info.set_client_info(?);" +
    "pkg_vpd.sp_set_context(?);" +
    "end;");
    >
    How does is that 'initiating the conn'? What does 'initiating the conn' eve mean? I've never heard of that.
    >
    How can I keep the two separate connection in the same session?
    >
    You can't - each connection to the server has its own session. Either the same session needs to insert the parent and child records or you need to commit after the parent record is inserted (which you appear to show that you intend).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points