Oracle SQL loader issue

Question

I have a table TABLE1 with columns R_ROW_ID, NAME, MARKS, TIME, NUMBER,LAST_UPD_TIME,USER_ID... All column values are not null.

TABLE1 has unique index on column R_ROW_ID.

I have a file which has only NAME, MARKS data and load this data into TABLE1 using SQL Loader.

File1.txt has below data:

JOHN|83
DON|54
LONIS|91

How to populate ROLLNO with unique value , NUMBER with default value 0 and LAST_UPD_TIME with current timestamp

This file loading is one time and I have created a sequencer SEQ as mentioned below:

 CREATE SEQUENCE SEQ START WITH 90000 INCREMENT BY 1;

Grant on this sequencer SEQ is:

  GRANT SELECT ON SEQ TO LOAD_USER; 

This is the control file code I have written:

 LOAD DATA APPEND
 INTO TABLE TABLE1 FIELDS TERMINATED BY "|"
 TRAILING NULLCOLS
 (
   R_ROW_ID  "SEQ.NEXTVAL",
   NAME      CHAR "NVL(TRIM(:NAME),' ')",
   MARKS      CHAR "NVL(TRIM(:MARKS),' ')",
   TIME       CHAR "NVL(TRIM(:TIME),' ')",
   NUMBER    CONSTANT "0",
   LAST_UPD_TIME EXPRESSION "CURRENT_TIMESTAMP(3)",
   USER_ID   CHAR "NVL(TRIM(:USER_ID),' ')"
   )

This is the error it is showing :

  SQL*Loader-951: Error calling once/load initialization
  ORA-02373: Error parsing insert statement for table TABLE1.
  ORA-02289: sequence does not exist

Let me know is there anything wrong in above one.

Is there any other way we can populate R_ROW_ID value (which should be unique) apart from using sequences ?

Answer 1

Well, who knows what's wrong if you aren't telling the truth.

• You want to set ROLLNO to something, but that column doesn't exist in column list you specified in the 1st sentence.

• Then, you want to set column named NUMBER to something else; you probably don't, because number can't be column's name as it is keyword, reserved for datatype (true, there can be exceptions, but I doubt this is the issue here)

  SQL> create table test (number varchar2(10));
  create table test (number varchar2(10))
                     *
  ERROR at line 1:
  ORA-00904: : invalid identifier
  

Why did you create sequence in one schema (we don't know which one) and granted it to another user? Why don't you do everything in the same schema? Or did you just get a wrong idea that you should grant privilege on sequence you created to yourself?

Here's an example; see if it helps. Everything is being done in the same schema.

SQL> create table table1
  2    (r_row_id      number       not null,
  3     name          varchar2(10) not null,
  4     marks         number       not null,
  5     time          varchar2(10) not null,
  6     c_number      number       not null,
  7     last_upd_time timestamp    not null,
  8     user_id       varchar2(10) not null
  9    );

Table created.

SQL> create sequence seq start with 90000 increment by 1;

Sequence created.

Control file:

load data
infile *
replace
into table table1
fields terminated by '|'
trailing nullcols
(
  name,
  marks,
  r_row_id      "seq.nextval",
  time          "nvl(:time, ' ')",
  c_number      constant "0",
  last_upd_time "systimestamp",
  user_id       "user"
)

begindata
JOHN|83
DON|54
LONIS|91

Loading session:

SQL> $sqlldr scott/tiger@pdb1 control=test13.ctl log=test13.log

SQL*Loader: Release 21.0.0.0.0 - Production on Mon May 1 20:05:07 2023
Version 21.3.0.0.0

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

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

Table TABLE1:
  3 Rows successfully loaded.

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

Result:

SQL> select * from table1;

  R_ROW_ID NAME            MARKS TIME    C_NUMBER LAST_UPD_TIME              USER_ID
---------- ---------- ---------- ----- ---------- -------------------------- ----------
     90000 JOHN               83                0 01.05.2023 20:05:07:394000 SCOTT
     90001 DON                54                0 01.05.2023 20:05:07:394000 SCOTT
     90002 LONIS              91                0 01.05.2023 20:05:07:397000 SCOTT

SQL>

---

If you really are loading data using another user, then - in my example - "this" user is scott and he grants select on seq sequence to mike. Mike is doing the rest:

SQL> show user
USER is "SCOTT"
SQL> grant select on seq to mike;

Grant succeeded.

SQL> connect mike/lion@pdb1
Connected.
SQL> create table table1
  2    (r_row_id      number       not null,
  3     name          varchar2(10) not null,
  4     marks         number       not null,
  5     time          varchar2(10) not null,
  6     c_number      number       not null,
  7     last_upd_time timestamp    not null,
  8     user_id       varchar2(10) not null
  9    );

Table created.

SQL>

Control file; note modification for r_row_id (uppercase owner name!):

load data
infile *
replace
into table table1
fields terminated by '|'
trailing nullcols
(
  name,
  marks,
  r_row_id      "SCOTT.seq.nextval",
  time          "nvl(:time, ' ')",
  c_number      constant "0",
  last_upd_time "systimestamp",
  user_id       "user"
)

begindata
JOHN|83
DON|54
LONIS|91

Loading session:

SQL> $sqlldr mike/lion@pdb1 control=test13.ctl log=test13.log

SQL*Loader: Release 21.0.0.0.0 - Production on Mon May 1 20:10:39 2023
Version 21.3.0.0.0

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

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

Table TABLE1:
  3 Rows successfully loaded.

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

Result:

SQL> select * From table1;

  R_ROW_ID NAME            MARKS TIME         C_NUMBER LAST_UPD_TIME              USER_ID
---------- ---------- ---------- ---------- ---------- -------------------------- ----------
     90009 JOHN               83                     0 01.05.2023 20:10:39.885000 MIKE
     90010 DON                54                     0 01.05.2023 20:10:39.885000 MIKE
     90011 LONIS              91                     0 01.05.2023 20:10:39.911000 MIKE

SQL>

Alternatively, you could create a synonym for owner's sequence (still connected as mike):

SQL> create synonym seq for scott.seq;

Synonym created.

Then you don't have to modify the 1st control file I posted at all, and loading does the job:

SQL> $sqlldr mike/lion@pdb1 control=test13.ctl log=test13.log

SQL*Loader: Release 21.0.0.0.0 - Production on Mon May 1 20:13:15 2023
Version 21.3.0.0.0

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

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

Table TABLE1:
  3 Rows successfully loaded.

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