Spot on, thanks
Sam Weisneth, July 07, 2016 - 11:28 pm UTC
Hi Chris,
Sorry I did not get time to test your solution until today and it worked fine the very first time. Thanks a lot, really appreciate your help.
Best Regards,
Sam.
July 08, 2016 - 3:52 am UTC
glad we could help
neglecting the delimiter CHR(10)
suman jami, June 14, 2018 - 11:58 am UTC
Hello,
i am trying to upload a .csv file to oracle table using pl/sql, but when my last row of the file is a column of number field then i am getting "invalid number" ORA error as it is attached with the delimiter or new line charecter which is in my case CHR(10).
is there any way to remove/ignore the newline charecter while uploading.
thanks in advance.
regards,
June 15, 2018 - 1:20 am UTC
A rtrim should take care of it
SQL> declare
2 x varchar2(10) := '12345
3 ';
4 y int;
5 begin
6 y := to_number(x);
7 end;
8 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 6
SQL>
SQL> declare
2 x varchar2(10) := '12345
3 ';
4 y int;
5 begin
6 y := to_number(rtrim(x,chr(10)));
7 end;
8 /
PL/SQL procedure successfully completed.
It Worked!!!
Ei, November 10, 2018 - 3:47 am UTC
This is in response to the post,
Replacing special characters CR and LF while loading data using SQL Loader
Just to clarify, the header record should also contain the custom string delimiter, correct? Otherwise, sqlldr ignores the first data record.
#EOR#
This is my control file:
OPTIONS (DIRECT=TRUE, SKIP=1)
LOAD DATA
INFILE 'Open Req.csv' "str '#EOR#'"
BADFILE 'Open Req.bad'
TRUNCATE
INTO TABLE OPEN_REQ
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REQ_NAME CHAR(240),
REQ_NUM CHAR(240),
REQ_ID CHAR(240),
REQ_DESC CHAR(1000) "replace(:TBH_DESC, CHR(13)||CHR(10), ' ')",
DISPLAY_NAME CHAR(1000),
REQ_APPROVAL_DATE "TO_DATE(:REQ_APPROVAL_DATE, 'YYYY-MM-DD')",
EOR FILLER
)
November 12, 2018 - 9:57 am UTC
It lumps together all lines until there's an #EOR#. So yes, if your header DOESN'T have an #EOR#, then it'll get merged with the first data line. And you'll skip both.
It Worked!!! - Reply
ei, November 13, 2018 - 11:00 pm UTC
Thanks for the quick confirmation.