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 Go to next message
Duane
Messages: 557
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 #686255 is a reply to message #686254] Thu, 14 July 2022 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
As you can see, the ID value is at the end of the record along with an CRLF.

Maybe should I take some LSD to see the same thing than you...

Re: External Table INTEGER Conversion to NUMBER is wrong [message #686256 is a reply to message #686255] Thu, 14 July 2022 20:44 Go to previous messageGo to next message
Duane
Messages: 557
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 that Oracle is taking the ID (integer) and the CRLF and converting that into a number for the table column. It's like Oracle doesn't know not to use the CRLF in the conversion.

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)

So, the question remains when can you have an Integer field at the end of the record and it correctly does the conversion? Unless someone has a way around that situation, you can't have an Integer value at the end of your data with a CRLF.
Re: External Table INTEGER Conversion to NUMBER is wrong [message #686257 is a reply to message #686256] Fri, 15 July 2022 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
It appears the fix is to change the Integer to be Char and add RTRIM to the field.

It appears you don't understand what INTEGER means for an Oracle external table and your analysis is wrong.
This is why we ALWAYS ask for a test case to reproduce and see what you have and show you where it is not correct.

Re: External Table INTEGER Conversion to NUMBER is wrong [message #686259 is a reply to message #686257] Fri, 15 July 2022 08:31 Go to previous messageGo to next message
Duane
Messages: 557
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.
Modification accordingly to the link I gave (read it to understand the modification I made):
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.
But I work on Windows so newline is x'0D0A' as in the file, for you, on Unix, where newline is x'0A', additional modification to match the file:
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 Go to previous messageGo to next message
Duane
Messages: 557
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 Go to previous messageGo to next message
Duane
Messages: 557
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 #686263 is a reply to message #686262] Fri, 15 July 2022 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How could we know what does not work if you don't post it?
So post the execution as I did and the content of the log file (remove it before selecting).

Re: External Table INTEGER Conversion to NUMBER is wrong [message #686264 is a reply to message #686263] Fri, 15 July 2022 11:55 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
It works. I was using test data that had an ID longer than 10. Normal production data for ID is only 8.

All good now.
Re: External Table INTEGER Conversion to NUMBER is wrong [message #686265 is a reply to message #686264] Fri, 15 July 2022 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Glad it works, thanks for the feedback.

Re: External Table INTEGER Conversion to NUMBER is wrong [message #686266 is a reply to message #686265] Fri, 15 July 2022 12:43 Go to previous message
Duane
Messages: 557
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.
Previous Topic: ORA-39083,ORA-29821,ORA-06598
Next Topic: SQLLDR ERROR
Goto Forum:
  


Current Time: Fri Mar 29 07:24:40 CDT 2024