SQL*Loader Date error (ORA-01858)

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

SQL*Loader Date error (ORA-01858)

SQL*Loader Date error (ORA-01858)

2005-12-07 - By Madhavi Kanugo

Reply: 1 2 3 4 5

Hello All,

I'm trying to load data using SQL Loader and ran into the ORA-01858 (See ORA-01858.ora-code.com): a
non-numeric character was found where a numeric was expected error.

Below is my control file. As you can see, the timestamp is a constant field
and I am loading its value from the filename of the data files. I am
executing the sql loader from a shell script and creating the control file
on the fly. The filename is in the form of: 20051206130101.txt So
basically, the shell script replaces the when_changed value with
20051206130101.

I created a temp table with just a date field and tried to insert a value
into it. Insert into temp values
(to_date('20051207160752','YYYYMMDDHH24MISS')); and there is no error and
date conversion is implict.

But SQLLOADER is erring out on ORA-01858 (See ORA-01858.ora-code.com).

LOAD DATA

INFILE FILENAME

APPEND

INTO TABLE RE_STATS_STAGING

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

RE_NAME,

TG_TYPE,

TRUNKGROUP,

CONNECTS,

HANGUPS,

DECLINES,

LOOPS,

SECONDS,

CC_CONNECTED,

CC_SETUP,

TIMESTAMP CONSTANT
"to_date('_when_changed_','YYYYMMDDHH24MISS')"

)

Any ideas on where I am going wrong? Any thoughts or help will be
appreciated.

Thanks in advance,

Madhavi

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft
-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http:
//www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">

<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<style>

</style>

</head>

<body lang=EN-US link=blue vlink=purple>

<div class=Section1>

Hello  All,<o:p></o:p>

<o:p> </o:p>

I'm trying to load data using SQL Loader and ran into
the ORA-01858 (See ORA-01858.ora-code.com): a non-numeric character was found where a numeric
was expected  error.<o:p></o:p>

<o:p> </o:p>

Below is my control file. As you can see, the timestamp is a
constant field and I am loading its value from the filename of the data files.
I am executing the sql loader from a shell script and creating the control file
on the fly. The filename is in the form of: 20051206130101.txt  So
basically, the shell script replaces the when_changed value with 20051206130101
.<o:p></o:p>

<o:p> </o:p>

I created a temp table with just a date field and tried to
insert a value into it. Insert into temp values (to_date
('20051207160752',
'YYYYMMDDHH24MISS')
);
and there is no error and date conversion is implict.<o:p></o:p>

<o:p> </o:p>

But SQLLOADER is
erring out on ORA-01858 (See ORA-01858.ora-code.com). <o:p></o:p>

<o:p> </o:p>

LOAD DATA <o:p></o:p>

INFILE FILENAME <o:p></o:p>

APPEND <o:p></o:p>

INTO TABLE RE_STATS_STAGING<o:p></o:p>

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' <o
:p></o:p>

    TRAILING NULLCOLS <o:p></o:p>

( <o:p></o:p>

  RE_NAME,<o:p></o:p>

  TG_TYPE,<o:p></o:p>

  TRUNKGROUP,<o:p></o:p>

  CONNECTS,<o:p></o:p>

  HANGUPS,<o:p></o:p>

  DECLINES,<o:p></o:p>

          
  LOOPS,<o:p></o:p>

          
  SECONDS,<o:p></o:p>

          
  CC_CONNECTED,<o:p></o:p>

          
  CC_SETUP,<o:p></o:p>

          
  TIMESTAMP
CONSTANT "to_date('_when_changed_','YYYYMMDDHH24MISS')"<o:p></o:p>

        )<o:p></o:p>

<o:p> </o:p>

Any ideas on where I am going wrong? Any thoughts or help
will be appreciated.<o:p></o:p>

 
Thanks in advance,<o:p></o:p>

Madhavi<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

</div>

</body>

</html>