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: 581
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 #686256 is a reply to message #686255] |
Thu, 14 July 2022 20:44  |
Duane
Messages: 581
Registered: December 2002
|
Senior Member |
|
|
What I meant was that the ID field is at end of the table definition and then the CRLF follows that in the file.
(first_name char(40),
last_name char(40),
department char(40),
id integer)
It appears the fix is to change the Integer to be Char and add RTRIM to the field.
(first_name char(40),
last_name char(40),
department char(40),
id char(20) rtrim)
|
|
|
|
|
|
| Re: External Table INTEGER Conversion to NUMBER is wrong [message #686259 is a reply to message #686257] |
Fri, 15 July 2022 08:31 |
Duane
Messages: 581
Registered: December 2002
|
Senior Member |
|
|
Then I don't.
Here's some data.
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:'excel-example-1.csv')
)
REJECT LIMIT 0;
Viewing the attached file in Notepad++ will show the CRLF at the end of each record.
|
|
|
|
| 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: 68729
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 #686261 is a reply to message #686260] |
Fri, 15 July 2022 11:04 |
Duane
Messages: 581
Registered: December 2002
|
Senior Member |
|
|
Thank you.
Very interesting. My last employment was a Windows shop so I never saw this behavior before. Now with a different place that runs Unix and I can see how things need to change based on the platform.
I did try different combinations of "records delimited by" but I can see by your example that my syntax was way off.
Thanks again.
|
|
|
|
| Re: External Table INTEGER Conversion to NUMBER is wrong [message #686262 is a reply to message #686260] |
Fri, 15 July 2022 11:25 |
Duane
Messages: 581
Registered: December 2002
|
Senior Member |
|
|
Well.....I tried your code and it doesn't work on Unix.
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 X'0D0A'
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 EXTERNAL(10))
)
LOCATION (TEMP:'duane-excel-example-2.csv')
)
REJECT LIMIT 0;
|
|
|
|
|
|
|
|
|
|
| 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: 581
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 Jan 08 18:29:32 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
