Using .CURRVAL in Additional Columns to Set Foreign Keys...

[Anonymous]

I have found similar questions posed on the forum, however, I have not found any consistent or reasonable solutions to the question; how do you persist the .CURRVAL of a an oracle database sequence, so that it can be used in all child tables, where a foreign key relationship is required?
As the picture demonstrates, I am moving data from one table to multiple tables. There is a parent child relationship between 'to_person' and 'to_person_additional_id' using the PRSN_RK.
I am using the tOracleOutput "Advanced Settings" "Addtional columns" to set the PRSN_RK and the PRSN_ID sequences, using .NEXTVAL in the parent table; that is why you can not see them in the screen capture. This works as anticipated.
However, in the child table, I simply want to use the value of the PRSN_RK_SEQ.CURRVAL. This does not work, even though I have activated the sequence using the .NEXTVAL. I recieve a ORA-08002: sequence PRSN_RK_SEQ.CURRVAL is not yet defined in this session error.
Thank you...

[Anonymous]

You will need to make sure all your inserts are running in the same session. Every DB component (input and output) will create its own connection to the DB unless you use a tOracleConnection and configure the input/output components to use it.
Also remember that if you use a tOracleConnection component, you must have a tOracleCommit to commit your changes, as commits are not done automatically when the tOracleConnection is in use.

[Anonymous]

You will need to make sure all your inserts are running in the same session.

Thanks JGM. By 'running in the same session', I am assuming that you mean that all source and destination tables are within the same schema? In my case the IPAS_PERSON table is in a different instance.
I will explore your suggestion, however, and I appreciate your help.

[Anonymous]

No, I mean the same "session"
if you open a connection (session #1) to Oracle and insert 1000 rows into a table and do not issue a commit, then
you open another connection (session #2) and query the table, you will not see any rows that session #1 inserted.
to fix your job add a tOracleConnection component as the first component in the flow. in all your DB output components, check the "use existing connection" and pick the tOracleConnection component as the source. Then add a tOracleCommit component as a final component in your job.
What this will do is make all the inserts and calls to your sequence share the same session. this means that you will be able to query the currentval of the sequence in a different component that you are incrementing it in.

[Anonymous]

to fix your job add a tOracleConnection component as the first component in the flow. in all your DB output components, check the "use existing connection" and pick the tOracleConnection component as the source. Then add a tOracleCommit component as a final component in your job.

Thanks JGM...I implemented your solution and it works fine. The problem that I have is that I have two destination environments (2 different schemas - the IPAS_PERSON and PERSON_ADDITIONAL_ID ("A") are in one schema, and the RMS_TRANSLATION_INFORMATION ("B") is in another). When I add another tOracleConnection to manage schema "B" connection, I experience the same problem of not being able to access the .CURRVAL.

[Anonymous]

correct. each connection starts a new session. if you configure your DB to allow users inside one schema to insert into the other table, everything should work. (i.e. GRANT INSERT,UPDATE ON SCHEMA_B.TABLE_B TO USER_A; )

[Anonymous]

TalendHooligan,
What you can do is you can retrieve the NEXTVAL (or CURRVAL) value, store it in a global map, and access the global map to use it wherever you need. In this case you may use it in both DB instances.
Regards,
Rabih