Home » SQL & PL/SQL » SQL & PL/SQL » sql loader
sql loader [message #196498] Thu, 05 October 2006 11:30 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Below is the control file:

LOAD DATA
TRUNCATE INTO TABLE STAGING_OWNER.PC2MSTRA_STAGING_STCP
(
  STCP_Id                       		"STCP_ID_S.nextval",
  Audit_Insert_Dt               		SYSDATE,
  Audit_Insert_user_Name_Tx     	"USER",
  CPLNO                        	 	POSITION(1:4),
  CPLST                         		POSITION(179:179),
  PLTYP                         		POSITION(392:392),
  CPDOMI                        		POSITION(850:851),
  CPNAIC                        		POSITION(853:857),
  BESTN                         		POSITION(155:160)
)



When I execute the loader script from one of the UNIX box, the data from datafile is getting loaded.

But the same when I do it in another unix box, its throwing below error

Record 1: Rejected - Error on table STAGING_OWNER.MKTSECA_STAGING_STSE,
column AUDIT_INSERT_DT.
ORA-01858: a non-numeric character was found where a numeric was expected


The datatype of AUDIT_INSERT_DT is timestamp(6) in table
STAGING_OWNER.MKTSECA_STAGING_STSE.

Don’t know what could be the problem? Do anyone have faced such type of
scenario?

[Updated on: Thu, 05 October 2006 11:31]

Report message to a moderator

Re: sql loader [message #196501 is a reply to message #196498] Thu, 05 October 2006 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
The two systems have different DATE/NLS defaults
Re: sql loader [message #196515 is a reply to message #196498] Thu, 05 October 2006 12:55 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

How to check that in UNIX?

is it echo $NLS_DATE_FORMAT?

Naveen

[Updated on: Thu, 05 October 2006 13:19]

Report message to a moderator

Re: sql loader [message #196570 is a reply to message #196515] Fri, 06 October 2006 00:44 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You cannot fill a timestamp(6) column using sysdate.
From the docs:
SYSDATE Parameter

The combination of column name and the SYSDATE parameter is a complete column specification.

column_name  SYSDATE 

The database column must be of type CHAR or DATE. 
If the column is of type CHAR, then the date is loaded in the form 'dd-mon-yy.'
After the load, it can be loaded only in that form. 
If the system date is loaded into a DATE column, then it can be loaded in a variety 
of forms that include the time and the date.

[Updated on: Fri, 06 October 2006 00:45]

Report message to a moderator

Previous Topic: Leading spaces in spool files
Next Topic: query result between 2 constaint time BUT vary Date
Goto Forum:
  


Current Time: Sat Dec 03 06:06:28 CST 2016

Total time taken to generate the page: 0.08606 seconds