Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
I am using following code to create external table.
V_SQL := 'CREATE TABLE ' || L_EXT_TABLE || ' (' || L_CNAMES || ')
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ' || P_DIR || '
ACCESS PARAMETERS(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE ''' || L_EXT_TABLE || '.bad''
' || CASE WHEN P_NOLOG THEN ' NOLOGFILE ' ELSE 'LOGFILE '''||L_EXT_TABLE||'.log''' END || '
SKIP ' || P_IGNORE_HEADERLINES || ' FIELDS TERMINATED BY ''' || P_DELIMITER || ''' LDRTRIM
MISSING FIELD VALUES ARE NULL
)
LOCATION( ''' || P_FILENAME || ''')
)
REJECT LIMIT UNLIMITED;
File contains 13080 records. 13070 records are loaded into external table.
Only one rejected record is loaded into bad file of external table. Why ? I was expecting 10.
Also the data looks good and matches with the datatype and length. How to identify the exact reason ?
I appreciate any suggestions/help from all the experts.
I added some missing values so we could have a *test case* and can't reproduce your findings, eg
SQL> host cat c:\temp\xxx.dat
1,2,3
1,2,3
1,2,3
1,2,3
z,z,z
w,s,s
q,q,q
s,s,s
SQL> set serverout on
SQL> declare
2 l_ext_table varchar2(10) := 'mytab';
3 L_CNAMES varchar2(100) := 'c1 number,c2 number,c3 number';
4 P_DIR varchar2(10) := 'TEMP';
5 P_NOLOG boolean := false;
6 P_IGNORE_HEADERLINES varchar2(10) := '1';
7 P_DELIMITER varchar2(10) := ',';
8 P_FILENAME varchar2(10) := 'xxx.dat';
9 v_sql varchar2(1000);
10 begin
11
12 V_SQL := 'CREATE TABLE ' || L_EXT_TABLE || ' (' || L_CNAMES || ')
13 ORGANIZATION EXTERNAL (
14 TYPE ORACLE_LOADER
15 DEFAULT DIRECTORY ' || P_DIR || '
16 ACCESS PARAMETERS(
17 RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
18 BADFILE ''' || L_EXT_TABLE || '.bad''
19 ' || CASE WHEN P_NOLOG THEN ' NOLOGFILE ' ELSE 'LOGFILE '''||L_EXT_TABLE||'.log''' END || '
20 SKIP ' || P_IGNORE_HEADERLINES || ' FIELDS TERMINATED BY ''' || P_DELIMITER || ''' LDRTRIM
21 MISSING FIELD VALUES ARE NULL
22 )
23 LOCATION( ''' || P_FILENAME || ''')
24 )
25 REJECT LIMIT UNLIMITED';
26
27 dbms_output.put_line(v_sql);
28 end;
29 /
CREATE TABLE mytab (c1 number,c2 number,c3 number)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY
TEMP
ACCESS PARAMETERS(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'mytab.bad'
LOGFILE 'mytab.log'
SKIP 1 FIELDS TERMINATED BY ',' LDRTRIM
MISSING FIELD VALUES ARE NULL
)
LOCATION( 'xxx.dat')
)
REJECT LIMIT UNLIMITED
PL/SQL procedure successfully completed.
SQL> CREATE TABLE mytab (c1 number,c2 number,c3 number)
2 ORGANIZATION EXTERNAL (
3 TYPE ORACLE_LOADER
4 DEFAULT DIRECTORY TEMP
5 ACCESS PARAMETERS(
6 RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
7 BADFILE 'mytab.bad'
8 LOGFILE 'mytab.log'
9 SKIP 1 FIELDS TERMINATED BY ',' LDRTRIM
10 MISSING FIELD VALUES ARE NULL
11 )
12 LOCATION( 'xxx.dat')
13 )
14 REJECT LIMIT UNLIMITED
15 /
Table created.
SQL> select * from mytab;
C1 C2 C3
---------- ---------- ----------
1 2 3
1 2 3
1 2 3
3 rows selected.
SQL> select * from mytab;
C1 C2 C3
---------- ---------- ----------
1 2 3
1 2 3
1 2 3
3 rows selected.
SQL> host cat c:\temp\mytab.bad
z,z,z
w,s,s
q,q,q
s,s,s
so we'd need to see a top to bottom test case.