I'm trying to ingest 3 csv into 3 three tables A,B,C respectively. Primary key of A is generated using SCHEMEA.SEQ_TAB_REC_ID.NEXTVAL and B, C has foreign key column which is referring to A's primary key I tried to put the direct sql query
select SEQ_TAB_REC_ID.CURRVAL FROM dual;
in the ctl. But it does not work Then I tried to put it like this
TAB_REC_ID "TAB_STG.SEQ_TAB_REC_ID.CURRVAL",
It would be greatly appreciated if you could come up with a solution for getting the current value of sequence in the ctl either using the raw sql or by an expression inside the ctl template
Simply put, you can not. Sequence value should first be fetched (in this session) so that you could check its current value.
For example:
If you try to find its current value:
So: first fetch it ...
... and then you can check its current value:
As of this:
If I understood it correctly, you're talking about CTL file which is the Oracle SQL*Loader's control file. Is it? Syntax you tried (the 2nd one) looks like that. If that's so, then it should work - it does for me. Here's an example (I'll reuse sequence I already created):
Sample table:
Control file:
ID values will be discarded and replaced by sequence's values.
Loading session:
Result:
So ... it works.
Finally:
I don't think that SQL*Loader is capable of doing that (looking up into another table's column to find the parent key value). Perhaps you should switch to something else, something which is more flexible in terms of programming. That would be an external table.
Basically, your CSV file would act as if it were an ordinary Oracle table. You'd then be able to write SQL (or PL/SQL, i.e. procedures, not only queries) against it. Doing so, I guess that you'd accomplish your task much easier. Certainly, YMMV (as @JL Peyret commented).