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