Skip to Main Content
  • Questions

  • External Table Error Loding 32Lahks records in to main table.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Partheeban.

Asked: December 11, 2015 - 9:19 pm UTC

Last updated: December 15, 2015 - 2:35 am UTC

Version: Oracle 11g

Viewed 1000+ times

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

Comments

External Table Error Loding 32Lahks records in to main table

Partheeban J, December 14, 2015 - 6:24 pm UTC

Thanks Connor,

I modified as per your suggesion,

CREATE TABLE SCOT.RXD32L1
(
SORT_CODE NUMBER(9),
ACCOUNT_NUM NUMBER(11),
BANK_NAME VARCHAR2(6),
TRAN_DEBIT NUMBER(9),
TRAN_CREDIT NUMBER(9),
SEQ_NUM NUMBER(11),
RXD_FLAG NUMBER(4),
REPORT_FLAG NUMBER(4),
FLAG VARCHAR2(4)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY GK3_F
ACCESS PARAMETERS
(
records delimited by newline
fields terminated by ','
optionally enclosed by '"'
missing field values are null
(
SORT_CODE, ACCOUNT_NUM, BANK_NAME, TRAN_DEBIT, TRAN_CREDIT, SEQ_NUM, RXD_FLAG, REPORT_FLAG, FLAG
)
)
LOCATION ('32LRecords.csv')
)
PARALLEL
REJECT LIMIT UNLIMITED;

Now the query was working and end up with memory space error, due to in sufficient memory it has given error, then i checked my tablespace in my database from the below query

SELECT TABLESPACE_NAME,
SUM(bytes)/1024/1024 "USED MEGABYTES",
SUM(maxbytes)/1024/1024 "MAX MEGABYTES"
FROM dba_data_files
WHERE tablespace_name IN ('SYSTEM', 'USERS')
GROUP BY tablespace_name;
-----
TABLESPACE_NAME USED MEGABYTES MAX MEGABYTES
USERS 100 11264
SYSTEM 600 600

my system is having more than 200GB FREE SPACE how I need to increase the tablespace please help me.

Partheeban J
Chris Saxon
December 15, 2015 - 2:35 am UTC

OK, for starters you NEVER want to put anything in the SYSTEM tablespace.

Create a new tablespace (or use USERS).

Then ensure the tablespace has enough space by checking DBA_FREE_SPACE

External Table Error Loding 32Lahks records in to main table.

Partheeban J, December 14, 2015 - 7:59 pm UTC


Hi Conner,

I am receiving the following error

ORA-29913: error is executing ODCIEXTTABLEFETCH callout
ORA-01653: unable to exten table SCOT.RXD32L by 128 in tablespace SYSTEM

Thanks
Partheeban.J
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.