You Asked
Hi
I am having huge or big problem while loading the Oracle external table records to main table I am facing the error that I have described, thanks if giving any suggestion on my query
SQL> DROP TABLE SCOT.RXD32L;
Table dropped.
SQL> DROP TABLE SCOT.RXD32L1;
Table dropped.
--I have created the main table(SCOT.RXD32L)
SQL> CREATE TABLE SCOT.RXD32L
2 (
3 SORT_CODE NUMBER(6),
4 ACCOUNT_NUM NUMBER(8),
5 BANK_NAME VARCHAR2(3),
6 TRAN_DEBIT NUMBER(6),
7 TRAN_CREDIT NUMBER(6),
8 SEQ_NUM NUMBER(8),
9 RXD_FLAG NUMBER(1),
10 REPORT_FLAG NUMBER(1),
11 FLAG VARCHAR2(1)
12 ) ;
Table created.
--I have created the External Table (SCOT.RXD32LExt)
SQL> CREATE TABLE SCOT.RXD32LExt
2 (
3 SORT_CODE NUMBER(9),
4 ACCOUNT_NUM NUMBER(11),
5 BANK_NAME VARCHAR2(6),
6 TRAN_DEBIT NUMBER(9),
7 TRAN_CREDIT NUMBER(9),
8 SEQ_NUM NUMBER(11),
9 RXD_FLAG NUMBER(4),
10 REPORT_FLAG NUMBER(4),
11 FLAG VARCHAR2(4)
12 )
13 ORGANIZATION EXTERNAL
14 (
15 TYPE ORACLE_LOADER
16 DEFAULT DIRECTORY GK3_F
17 ACCESS PARAMETERS
18 (
19 records delimited by newline
20 fields terminated by ','
21 missing field values are null
22 (
23 SORT_CODE, ACCOUNT_NUM, BANK_NAME, TRAN_DEBIT, TRAN_CREDIT, SEQ_NUM, RXD_FLAG, REPORT_FLAG, FLAG
24 )
25 )
26 LOCATION ('32LRecords.csv')
27 )
28 PARALLEL
29 REJECT LIMIT UNLIMITED;
Table created.
"32LRecords.csv" file is having 32 Lakhs records
651000 1100002 SSS 160015 170711 1100002 1 1 C
651000 1100003 SSS 160015 170711 1100003 1 1 C
651000 1100004 SSS 160015 170711 1100004 1 1 C
651000 1100005 SSS 160015 170711 1100005 1 1 C
651000 1100006 SSS 160015 170711 1100006 1 1 C
.....
.....
.....
651000 4330000 AAK 160015 170711 4330000 1 1 C
651000 4330001 AAK 160015 170711 4330001 1 1 C
up to 32 Lakhs records...
Trying to load (SCOT.RXD32LExt) External table's 32Lakhs records in to REAL TABLE(SCOT.RXD32L)
SQL> INSERT INTO SCOT.RXD32L SELECT * FROM SCOT.RXD32LExt;
0 rows created.
SQL>
Ending with 0 rows created. but The same concepts is working for minimum records up to 1 to 1Lakhs records but unable to load more than 32Lakhs records. Can you please help me to load 32Lakhs records in the main tables.
When I am selecting the external table no records are displaying
SQL> SELECT * FROM SCOT.RXD32LExt;
0 rows created.
Both "LOG"(200MB) and "BAD"(431mb) files is generating up to 631MB size
"BAD" file details is displaying all 32Lahks records
BAD File Name = "RXD32L1_1788_5656.bad"
BAD File Length = 208,184KB
"651000","1100001","SSS","160015","170711","1100001","1","1","C"
"651000","1100002","SSS","160015","170711","1100002","1","1","C"
...
...
...
"651000","4330000","AAK","160015","170711","4330000","1","1","C"
"651000","4330001","AAK","160015","170711","4330001","1","1","C"
LOG file details is displaying all 32Lahks records
LOG File Name = "RXD32L1_1788_5656.log"
LOG File Length = 431,055KB
LOG file opened at 12/12/15 00:09:25
Field Definitions for table RXD32L1
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
SORT_CODE CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
ACCOUNT_NUM CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
BANK_NAME CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
TRAN_DEBIT CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
TRAN_CREDIT CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
SEQ_NUM CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
RXD_FLAG CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
REPORT_FLAG CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
FLAG CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
error processing column SORT_CODE in row 1 for datafile F:\data\download\uid_load\GK3_F\32LRecords.csv
ORA-01722: invalid number
error processing column SORT_CODE in row 2 for datafile F:\data\download\uid_load\GK3_F\32LRecords.csv
ORA-01722: invalid number
.....
.....
.....
error processing column SORT_CODE in row 3230000 for datafile F:\data\download\uid_load\GK3_F\32LRecords.csv
ORA-01722: invalid number
error processing column SORT_CODE in row 3230001 for datafile F:\data\download\uid_load\GK3_F\32LRecords.csv
ORA-01722: invalid number
Whether I need to increase the Table space in Oracle or any other code modification which needs to do, if table space is the issue means can you pls guide me how to increase the tables space my which my computer is having 1TB memory.
Thanks
Partheeban.J
and Connor said...
Its got nothing to do with space etc. Every record is being rejected (from what I can see) with "ORA-01722: invalid number" for the SORT_CODE column.
This is why you get "0 rows created". Every record in the external table is being scanned, but none of them can be loaded because you have non-numeric data in the SORT_CODE.
The cause is probably because you are not taking the quotes into account, so it is parsing: "651000" instead of 651000
Add the "optionally enclosed by" to your definition
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment