pythonoracle-databasectlcurrval

How to get current value of oracle sequence in ctl template

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

Solution

  • Simply put, you can not. Sequence value should first be fetched (in this session) so that you could check its current value.

    For example:

    SQL> create sequence seq_tab_rec_id;

Sequence created.

    If you try to find its current value:

    SQL> select seq_tab_rec_id.currval from dual;
select seq_tab_rec_id.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence SEQ_TAB_REC_ID.CURRVAL is not yet defined in this session

    So: first fetch it ...

    SQL> select seq_tab_rec_id.nextval from dual;

   NEXTVAL
----------
         1

    ... and then you can check its current value:

    SQL> select seq_tab_rec_id.currval from dual;

   CURRVAL
----------
         1

SQL>

    As of this:

    I'm trying to ingest a csv into a table using the oracle ctl template

    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:

    SQL> create table test (id number, name varchar2(20));

Table created.

    Control file:

    LOAD DATA
INFILE *
REPLACE
INTO TABLE test
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
id "seq_tab_rec_id.nextval",
name
)

begindata
1,Little
2,Foot

    ID values will be discarded and replaced by sequence's values.

    Loading session:

    SQL> $sqlldr scott/tiger@orcl control=test46.ctl log=test46.log

SQL*Loader: Release 18.0.0.0.0 - Production on Sri Stu 2 07:15:16 2022
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2

Table TEST:
  2 Rows successfully loaded.

Check the log file:
  test46.log
for more information about the load.

    Result:

    SQL> select * from test;

        ID NAME
---------- --------------------
         2 Little
         3 Foot

SQL>

    So ... it works.

    Finally:

    column is referring to another table as foreign key

    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).