Home » RDBMS Server » Server Utilities » sqlloader detect invisible characters (Oracle 11.2.0.3 OS: Windows 7)
sqlloader detect invisible characters [message #593731] Tue, 20 August 2013 07:28 Go to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Is there a way to detect bogus characters in the datafile?
SQLLoader on original file
Record 1: Rejected - Error on table DP, column STARTTIME.
ORA-01858: a non-numeric character was found where a numeric was expected

Copy the data in the controlfile using notepad++: no errors

Report message to a moderator

Re: sqlloader detect invisible characters [message #593733 is a reply to message #593731] Tue, 20 August 2013 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can find hexa editors on the web.
But first check your decimal character match the expected one.

Regards
Michel

Report message to a moderator

Re: sqlloader detect invisible characters [message #593738 is a reply to message #593733] Tue, 20 August 2013 09:21 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Time to find a second pair of eyes. In mismatch NLS numeric character I get a different error.
Target table:
CREATE TABLE DP
(
  SESSIONTYPE    VARCHAR2(20 CHAR),
  SPECIFICATION  VARCHAR2(50 CHAR),
  STATUS         VARCHAR2(20 CHAR),
  BACKUPMODE     VARCHAR2(10 CHAR),
  STARTTIME      DATE,
  STARTTIMET     NUMBER(12),
  ENDTIME        DATE,
  ENDTIMET       NUMBER(12),
  QUEUEING       VARCHAR2(10 BYTE),
  DURATION       VARCHAR2(10 BYTE),
  GBWRITTEN      NUMBER(10,2),
  MEDIA_COUNT    NUMBER(5),
  ERROR_COUNT    NUMBER(10),
  WARNING_COUNT  NUMBER(10),
  DA_PENDING     NUMBER(3),
  DA_RUNNING     NUMBER(3),
  DA_FAILED      NUMBER(3),
  DA_COMPLETED   NUMBER(3),
  OBJECT_COUNT   NUMBER(10),
  FILE_COUNT     NUMBER(10),
  SUCCES_PCT     VARCHAR2(4 CHAR),
  SESSION_OWNER  VARCHAR2(50 CHAR),
  SESSION_ID     VARCHAR2(13 CHAR)
)
TABLESPACE USERS
;

Working control file with begindata (copied from test.txt)
OPTIONS (ERRORS=0,SKIP=8)
load data
 infile * 
 APPEND
 into table DP
 fields terminated by "|"
 TRAILING NULLCOLS
 ( SESSIONTYPE
 , SPECIFICATION
 ,STATUS
 ,BACKUPMODE
 ,STARTTIME DATE "dd-mm-yyyy hh24:mi:ss"
 ,STARTTIMET DECIMAL EXTERNAL
 ,ENDTIME DATE "dd-mm-yyyy hh24:mi:ss" NULLIF ENDTIME="-"
 ,ENDTIMET DECIMAL EXTERNAL NULLIF ENDTIMET="0"
 ,QUEUEING
 ,DURATION
 ,GBWRITTEN DECIMAL EXTERNAL
 ,MEDIA_COUNT DECIMAL EXTERNAL
 ,ERROR_COUNT DECIMAL EXTERNAL
 ,WARNING_COUNT DECIMAL EXTERNAL
 ,DA_PENDING DECIMAL EXTERNAL
 ,DA_RUNNING DECIMAL EXTERNAL
 ,DA_FAILED DECIMAL EXTERNAL
 ,DA_COMPLETED DECIMAL EXTERNAL
 ,OBJECT_COUNT DECIMAL EXTERNAL
 ,FILE_COUNT DECIMAL EXTERNAL
 ,SUCCES_PCT
 ,SESSION_OWNER
 ,SESSION_ID
 )
 begindata
#List of Sessions
#Cell Manager: in-hp-01.local.dgz.be
#Creation Date: 19/08/2013 15:00:06
# Headers
# Report Selection Parameters|Value
Timeframe|maandag 1 juli 2013, 0:00:01 maandag 12 augustus 2013, 23:59:59
# Headers
# Session Type|Specification|Status|Mode|Start Time|Start Time_t|End Time|End Time_t|Queuing|Duration|GB Written|# Media|# Errors|# Warnings|# Pending DA|# Running DA|# Failed DA|# Completed DA|# Objects|# Files|Success|Session Owner|Session ID
Backup|ABC_Daily_Full_1300|Completed|full|1/07/2013 13:00:05|1372676405|1/07/2013 13:34:13|1372678453|0:00|0:34|25.43|1|0|0|0|0|0|2|2|10020|100%|abc\abc@SERV1|2013/07/01-2
Backup|EFG-01_Daily_Tape_1800|Completed|incr1|1/07/2013 19:00:06|1372698006|1/07/2013 19:48:43|1372700923|0:00|0:48|31.47|1|0|42|0|0|0|5|5|40255|100%|ABC\ABC@SERV2|2013/07/01-3

With incorrect NLS numeric characters
Record 1: Rejected - Error on table DP, column GBWRITTEN. ORA-01722: invalid number
set nls_numeric_characters=,.
sqlldr myuser/mypassword@myservice control=sl.txt

With correct NLS numeric characters: no error
set nls_numeric_characters=.,
sqlldr myuser/mypassword@myservice control=sl.txt


Controlfile to import attached file test.txt:
OPTIONS (ERRORS=0,SKIP=8)
load data
 infile test.txt 
 APPEND
 into table DP
 fields terminated by "|"
 TRAILING NULLCOLS
 ( SESSIONTYPE
 , SPECIFICATION
 ,STATUS
 ,BACKUPMODE
 ,STARTTIME DATE "dd-mm-yyyy hh24:mi:ss"
 ,STARTTIMET DECIMAL EXTERNAL
 ,ENDTIME DATE "dd-mm-yyyy hh24:mi:ss" NULLIF ENDTIME="-"
 ,ENDTIMET DECIMAL EXTERNAL NULLIF ENDTIMET="0"
 ,QUEUEING
 ,DURATION
 ,GBWRITTEN DECIMAL EXTERNAL
 ,MEDIA_COUNT DECIMAL EXTERNAL
 ,ERROR_COUNT DECIMAL EXTERNAL
 ,WARNING_COUNT DECIMAL EXTERNAL
 ,DA_PENDING DECIMAL EXTERNAL
 ,DA_RUNNING DECIMAL EXTERNAL
 ,DA_FAILED DECIMAL EXTERNAL
 ,DA_COMPLETED DECIMAL EXTERNAL
 ,OBJECT_COUNT DECIMAL EXTERNAL
 ,FILE_COUNT DECIMAL EXTERNAL
 ,SUCCES_PCT
 ,SESSION_OWNER
 ,SESSION_ID
 )

Error:
Record 1: Rejected - Error on table DP, column STARTTIME. ORA-01858: a non-numeric character was found where a numeric was expected
  • Attachment: test.txt
    (Size: 1.64KB, Downloaded 2239 times)

Report message to a moderator

Re: sqlloader detect invisible characters [message #593742 is a reply to message #593738] Tue, 20 August 2013 09:58 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Reason: unicode text file instead of ascii. Noticed when bad-file was like character null character null

Report message to a moderator

Re: sqlloader detect invisible characters [message #593747 is a reply to message #593742] Tue, 20 August 2013 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You'd see it immediately if you search for an hexa editor as I advised you. Wink

Regards
Michel

[Updated on: Tue, 20 August 2013 10:44]

Report message to a moderator

Re: sqlloader detect invisible characters [message #593748 is a reply to message #593747] Tue, 20 August 2013 10:56 Go to previous messageGo to next message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
If you're using vi, the "set list" command will enable you to see unprintable characters. "set nolist" switches it off.

HTH
-g

Report message to a moderator

Re: sqlloader detect invisible characters [message #593749 is a reply to message #593748] Tue, 20 August 2013 11:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
gazzag wrote on Tue, 20 August 2013 08:56
If you're using vi, the "set list" command will enable you to see unprintable characters. "set nolist" switches it off.

HTH
-g


vi does not exist by default on Windows!

Report message to a moderator

Re: sqlloader detect invisible characters [message #593750 is a reply to message #593749] Tue, 20 August 2013 11:07 Go to previous messageGo to next message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
Ah, my mistake, sorry. I had just been reading another thread where the OP was on RHEL. Anyway, what's wrong with WinVi? Razz

Report message to a moderator

Re: sqlloader detect invisible characters [message #593751 is a reply to message #593750] Tue, 20 August 2013 11:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Anyway, what's wrong with WinVi?

C:\Windows\System32>winvi
'winvi' is not recognized as an internal or external command,
operable program or batch file.

C:\Windows\System32>


other than it does not exist?

Report message to a moderator

Re: sqlloader detect invisible characters [message #593754 is a reply to message #593751] Tue, 20 August 2013 11:17 Go to previous messageGo to next message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
WinVi: http://www.winvi.de/en/

Report message to a moderator

Re: sqlloader detect invisible characters [message #593756 is a reply to message #593754] Tue, 20 August 2013 11:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
www.vim.org/

Report message to a moderator

Re: sqlloader detect invisible characters [message #593757 is a reply to message #593756] Tue, 20 August 2013 11:25 Go to previous messageGo to next message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
Thanks BlackSwan. I'll check it out.

Report message to a moderator

Re: sqlloader detect invisible characters [message #593810 is a reply to message #593757] Wed, 21 August 2013 03:53 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
After adding the characterset, it works Smile .
Resulting controlfile
OPTIONS (ERRORS=0,SKIP=8)
load data
 CHARACTERSET  UTF16 
 infile "test.txt" 
 APPEND
 into table DP
 fields terminated by "|"
 TRAILING NULLCOLS
 ( SESSIONTYPE
 , SPECIFICATION
 ,STATUS
 ,BACKUPMODE
 ,STARTTIME DATE "dd-mm-yyyy hh24:mi:ss"
 ,STARTTIMET DECIMAL EXTERNAL
 ,ENDTIME DATE "dd-mm-yyyy hh24:mi:ss" NULLIF ENDTIME="-"
 ,ENDTIMET DECIMAL EXTERNAL NULLIF ENDTIMET="0"
 ,QUEUEING
 ,DURATION
 ,GBWRITTEN DECIMAL EXTERNAL
 ,MEDIA_COUNT DECIMAL EXTERNAL
 ,ERROR_COUNT DECIMAL EXTERNAL
 ,WARNING_COUNT DECIMAL EXTERNAL
 ,DA_PENDING DECIMAL EXTERNAL
 ,DA_RUNNING DECIMAL EXTERNAL
 ,DA_FAILED DECIMAL EXTERNAL
 ,DA_COMPLETED DECIMAL EXTERNAL
 ,OBJECT_COUNT DECIMAL EXTERNAL
 ,FILE_COUNT DECIMAL EXTERNAL
 ,SUCCES_PCT
 ,SESSION_OWNER
 ,SESSION_ID
 )

Report message to a moderator

Re: sqlloader detect invisible characters [message #593821 is a reply to message #593810] Wed, 21 August 2013 04:37 Go to previous message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thanks for the feedback, it is indeed the correct solution.

Regards
Michel

Report message to a moderator

Previous Topic: SQL loader
Next Topic: Loading String has double quotes in it
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 10 13:47:40 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.