Team,
Was reading about SQL*Loader Express from documentation
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-sql-loader-express-mode.html#GUID-3C000956-3730-4D27-9A46-F7D12BC81540 D:\>sqlldr demo/demo@pdb1 table=EMP date_format='DD-MON-YYYY hh12:mi:ss am' optionally_enclosed_by='"' log=emp_log.txt data=emp.csv terminated_by='|'
SQL*Loader: Release 12.2.0.1.0 - Production on Thu Jul 25 12:45:00 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: EMP
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file emp.csv
ORA-01031: insufficient privileges
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
Express Mode Load, Table: EMP
Path used: Direct
Load completed - logical record count 14.
Table EMP:
0 Rows successfully loaded.
Check the log file:
emp_log.txt
for more information about the load.
D:\>type emp.csv
7369|"SMITH"|"CLERK"|7902|17-DEC-1980 12:00:00 am|800||20
7499|"ALLEN"|"SALESMAN"|7698|20-FEB-1981 12:00:00 am|1600|300|30
7521|"WARD"|"SALESMAN"|7698|22-FEB-1981 12:00:00 am|1250|500|30
7566|"JONES"|"MANAGER"|7839|02-APR-1981 12:00:00 am|2975||20
7654|"MARTIN"|"SALESMAN"|7698|28-SEP-1981 12:00:00 am|1250|1400|30
7698|"BLAKE"|"MANAGER"|7839|01-MAY-1981 12:00:00 am|2850||30
7782|"CLARK"|"MANAGER"|7839|09-JUN-1981 12:00:00 am|2450||10
7788|"SCOTT"|"ANALYST"|7566|19-APR-0087 12:00:00 am|3000||20
7839|"KING"|"PRESIDENT"||17-NOV-1981 12:00:00 am|5000||10
7844|"TURNER"|"SALESMAN"|7698|08-SEP-1981 12:00:00 am|1500|0|30
7876|"ADAMS"|"CLERK"|7788|23-MAY-0087 12:00:00 am|1100||20
7900|"JAMES"|"CLERK"|7698|03-DEC-1981 12:00:00 am|950||30
7902|"FORD"|"ANALYST"|7566|03-DEC-1981 12:00:00 am|3000||20
7934|"MILLER"|"CLERK"|7782|23-JAN-1982 12:00:00 am|1300||10
none of the records got loaded, looking into the log file it shows this.
Generated control file for possible reuse:
OPTIONS(DIRECT=TRUE)
LOAD DATA
INFILE 'emp.csv'
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY ""
DATE FORMAT "DD-MON-YYYY hh12:mi:ss am"
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE DATE,
SAL,
COMM,
DEPTNO
)
End of generated control file for possible reuse.
Record 1: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 2: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 3: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 4: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 5: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 6: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 7: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 8: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 9: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 10: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 11: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 12: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 13: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Record 14: Rejected - Error on table EMP, column EMPNO.
second enclosure string not present
Table EMP:
0 Rows successfully loaded.
14 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Questions:
a) though we have said Optionally_enclosed_by='"' - why does it looks for that enclose character to every field in the datafile, rather than just where it is mentioned?
b) how to specify "optionally_enclosed_by" clause if my data is enclosed by something like this {...} ( say for example, KING is represented as {KING} in the datafile ) ?
July 26, 2019 - 5:31 am UTC
It will be how the environment understands the command line, in particular, quotes and spaces etc. For example, on Unix I had to do this:
[oracle]$ sqlldr xxx/xxx@pdb1 table=EMP date_format='DD-MON-YYYY\ hh12:mi:ss\ am' optionally_enclosed_by='\"' log=emp_log.txt data=emp.csv terminated_by='|'
SQL*Loader: Release 19.0.0.0.0 - Production on Thu Jul 25 22:33:17 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: EMP
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
Table EMP:
14 Rows successfully loaded.
Check the log files:
emp_log.txt
emp_log.txt_xt
for more information about the load.