Home » RDBMS Server » Server Utilities » External Table INTEGER Conversion to NUMBER is wrong (Oracle, 12.2, Unix)
 External Table INTEGER Conversion to NUMBER is wrong [message #686254] |
Thu, 14 July 2022 10:48  |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
Just seeing if anyone has come across this before.
When I create the external table the NUMBER column of the table has random numbers that are not in the file being converted. As you can see, the ID value is at the end of the record along with an CRLF.
So, I'm guessing the conversion of ID is also adding in the actual ID number plus the CRLF and that's why the ID column is not the number in the file.
The file can't be changed so how do I work around this and get the actual ID value that is in the file?
CREATE TABLE EXTERNAL_TBL
(
FIRST_NAME VARCHAR2(40 CHAR),
LAST_NAME VARCHAR2(40 CHAR),
DEPARTMENT VARCHAR2(60 CHAR),
ID NUMBER
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY TEMP
ACCESS PARAMETERS
(
records delimited by newline
skip 1
fields terminated by ','
optionally enclosed by '"'
missing field values are null
(first_name char(40),
last_name char(40),
department char(40),
id integer)
)
LOCATION (TEMP:'duane-excel-example-2.csv')
)
REJECT LIMIT 0;
The file shows the ID column has a value of 123456
The Table shows the ID column has a value of 875770417
|
|
|
|
|
|
|
|
|
|
|
|
| Re: External Table INTEGER Conversion to NUMBER is wrong [message #686260 is a reply to message #686259] |
Fri, 15 July 2022 10:02  |
 |
Michel Cadot
Messages: 68764
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Your table, not correct:
SQL> host type c:\excel-example-1.csv
First,Last,Department,ID
First1,Last1,IT,123456
First2,Last2,HR,9876540
First3,Last3,IT,87653256
SQL> CREATE TABLE EXTERNAL_TBL
2 (
3 FIRST_NAME VARCHAR2(10 CHAR),
4 LAST_NAME VARCHAR2(10 CHAR),
5 DEPARTMENT VARCHAR2(10 CHAR),
6 ID NUMBER
7 )
8 ORGANIZATION EXTERNAL
9 ( TYPE ORACLE_LOADER
10 DEFAULT DIRECTORY MY_DIR
11 ACCESS PARAMETERS
12 (
13 records delimited by newline
14 skip 1
15 fields terminated by ','
16 optionally enclosed by '"'
17 missing field values are null
18 (first_name char(10),
19 last_name char(10),
20 department char(10),
21 id integer)
22 )
23 LOCATION (MY_DIR:'excel-example-1.csv')
24 )
25 REJECT LIMIT 0
26 /
Table created.
SQL> select * from EXTERNAL_TBL;
FIRST_NAME LAST_NAME DEPARTMENT ID
---------- ---------- ---------- ----------
First1 Last1 IT 875770417
First2 Last2 HR 909588537
First3 Last3 IT 892745528
3 rows selected.
SQL> drop table EXTERNAL_TBL
2 /
Table dropped.
SQL> CREATE TABLE EXTERNAL_TBL
2 (
3 FIRST_NAME VARCHAR2(10 CHAR),
4 LAST_NAME VARCHAR2(10 CHAR),
5 DEPARTMENT VARCHAR2(10 CHAR),
6 ID NUMBER
7 )
8 ORGANIZATION EXTERNAL
9 ( TYPE ORACLE_LOADER
10 DEFAULT DIRECTORY MY_DIR
11 ACCESS PARAMETERS
12 (
13 records delimited by newline
14 skip 1
15 fields terminated by ','
16 optionally enclosed by '"'
17 missing field values are null
18 (first_name char(10),
19 last_name char(10),
20 department char(10),
21 id integer EXTERNAL(10))
22 )
23 LOCATION (MY_DIR:'excel-example-1.csv')
24 )
25 REJECT LIMIT 0
26 /
Table created.
SQL> select * from EXTERNAL_TBL;
FIRST_NAME LAST_NAME DEPARTMENT ID
---------- ---------- ---------- ----------
First1 Last1 IT 123456
First2 Last2 HR 9876540
First3 Last3 IT 87653256
3 rows selected.
SQL> drop table EXTERNAL_TBL
2 /
Table dropped.
SQL> CREATE TABLE EXTERNAL_TBL
2 (
3 FIRST_NAME VARCHAR2(10 CHAR),
4 LAST_NAME VARCHAR2(10 CHAR),
5 DEPARTMENT VARCHAR2(10 CHAR),
6 ID NUMBER
7 )
8 ORGANIZATION EXTERNAL
9 ( TYPE ORACLE_LOADER
10 DEFAULT DIRECTORY MY_DIR
11 ACCESS PARAMETERS
12 (
13 records delimited by X'0D0A'
14 skip 1
15 fields terminated by ','
16 optionally enclosed by '"'
17 missing field values are null
18 (first_name char(10),
19 last_name char(10),
20 department char(10),
21 id integer EXTERNAL(10))
22 )
23 LOCATION (MY_DIR:'excel-example-1.csv')
24 )
25 REJECT LIMIT 0
26 /
Table created.
SQL> select * from EXTERNAL_TBL;
FIRST_NAME LAST_NAME DEPARTMENT ID
---------- ---------- ---------- ----------
First1 Last1 IT 123456
First2 Last2 HR 9876540
First3 Last3 IT 87653256
3 rows selected. [Updated on: Fri, 15 July 2022 10:03] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: External Table INTEGER Conversion to NUMBER is wrong [message #686266 is a reply to message #686265] |
Fri, 15 July 2022 12:43 |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
It's a challenge sometimes when you can't show production data so I was just using data I made up. I just went longer on the ID when entering that data into the spreadsheet. I didn't think about it when you showed me the "integer external(x)" reference and my ID data was longer than actual production data. After I posted my reply, it dawned on me that production data only had a length of 8.
Thanks again.
|
|
|
|
Goto Forum:
Current Time: Wed Jul 30 17:21:51 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
