SQLLoader + Timestamp προβλημα

[bnvdarklord]

Προσπαθώ να κανω insert σε εναν πίνακα μεσω της εντολής sqlldr και μου βγάζει αυτα τα error:

 

Εγγραφή 2: Απορρίφθηκε - Σφάλμα στον πίνακα CUSTOMERS, στήλη REGISTRATIONDATE.

ORA-01830: πρότυπο μορφής ημερομηνίας τελειώνει πριν να γίνει μετατροπή όλου του αλφαριθμητικού εισόδου

 

Εγγραφή 3: Απορρίφθηκε - Σφάλμα στον πίνακα CUSTOMERS, στήλη REGISTRATIONDATE.

ORA-01850: η ώρα πρέπει να είναι μεταξύ 0 και 23

 

Οι ημερομηνίες στο αρχειο ειναι ετσι 2004-11-09 00:00:00 (ολες οι ωρες 00:00:00) και στον πίνακα η στηλη RegistrationDate ειναι Timestamp.

[Evgenios1]

Μηπως σου εχει διαφυγει αυτο;

[bnvdarklord]

Οχι, το εχω δεί αυτό, αλλα δεν γινεται να το εφαρμόσω με το sql loader.

[lefterhs]

Για κοίτα και αυτό:

Problem Summary:

================

ORA-1830 ORA-1401 ORA ORA-2359 USING SQL*LOADER

 

 

Problem Description:

====================

 

You are using SQL*Loader and receive any of the following errors:

 

ORA-01830: date format picture ends before converting entire input string

 

--or--

 

ORA-01401: inserted value too large for column

 

--or--

 

ORA-02359: Field in data file exceeded maximum specified length

 

 

 

Problem Explanation:

====================

The error occurs when the date data (ORA-01830) or the character data

(ORA-01401 or ORA-02359) is longer than the date or character datatype

of the table you're loading into.

 

 

The following SQL*Loader control file and log file demonstrate the problem.

 

 

Example #1:

 

CREATE TABLE DATE_TEST

(

TESTDATE DATE

);

 

CONTROL FILE:

-------------

LOAD DATA

INFILE *

TRUNCATE

INTO TABLE DATE_TEST

FIELDS TERMINATED BY ","

(

TESTDATE DATE(28) "YYYY-mm-dd hh24:mi:ss"

)

BEGINDATA

1996-11-21.15.07.10.00000

 

 

===> This will give an ORA-1830

 

 

Example #2:

 

CREATE TABLE TEST

(

TESTCHAR CHAR(10)

);

 

CONTROL FILE:

--------------

LOAD DATA

INFILE "TEST.TXT"

TRUNCATE

INTO TABLE TEST

FIELDS TERMINATED BY ','

(

TESTCHAR

)

BEGINDATA

1234567890The quick brown fox jumps over the lazy dog.

 

 

==> This will give an ORA-1401 OR ORA-2359

 

 

Search Words:

=============

sql*loader, sqlloader, load, loader, substr, substring, sqlldr

 

 

Solution Description:

=====================

 

Use the SQL "SUBSTR" function to truncate the data being read in. The SUBSTR

function will work, if the load is done via conventional method (in versions

until 9i).

 

Example #1:

 

CONTROL FILE:

-------------

LOAD DATA

INFILE *

TRUNCATE

INTO TABLE DATE_TEST

FIELDS TERMINATED BY ","

(

TESTDATE DATE(28) "YYYY-mm-dd hh24:mi:ss" "SUBSTR (:TESTDATE, 1, 19)"

)

BEGINDATA

1996-11-21.15.07.10.00000

 

 

Example #2:

 

CONTROL FILE:

-------------

LOAD DATA

INFILE *

TRUNCATE

INTO TABLE TEST

FIELDS TERMINATED BY ','

(

TESTCHAR "SUBSTR (:TESTCHAR, 1, 10)"

)

BEGINDATA

1234567890The quick brown fox jumps over the lazy dog.

 

 

Note that you don't specify any size for column TESTCHAR, so it defaults to

CHAR(255).

 

 

Solution Explanation:

=====================

 

SUBSTR allows you to input a portion of a character string avoiding errors

relating to field too long.

[bnvdarklord]

Βάζοντας στο control file αυτό

registrationDate date "to_date(:registrationDate, 'yyyy-mm-dd hh24:mi:ss')"

πέρνω αυτό ORA-01821: η μορφή ημερομηνίας δεν αναγνωρίζεται

 

βάζοντας αυτό

registrationDate date(28) "to_date(:registrationDate, 'yyyy-mm-dd hh24:mi:ss')"

πέρνω ORA-00907: λείπει δεξιά παρένθεση

 

Και τέλος βάζοντας registrationDate date "yyyy-mm-dd hh24:mi:ss"

πέρνω αυτό ORA-01841: το (πλήρες) έτος πρέπει να είναι μεταξύ -4713 και +9999 και όχι 0 και

ORA-01840: η τιμή που δόθηκε δεν έχει αρκετό μήκος για μορφή ημερομηνίας

 

Το "SUBSTR (:registrationDate, 1, 19)" δεν αλλάζει κάτι.

 

:wacko:

[lefterhs]

Δοκίμασε την to_timestamp, αντί για την to_date,

αφού η κολώνα σου είναι timestamp.

 

Το άλλο που πρέπει να δεις είναι αν έχεις βάλει την παράμετρο NLS_DATE_FORMAT ακριβώς ίδια με το format που επέλεξες να χρησιμοποιήσεις, δηλαδή 'yyyy-mm-dd hh24:mi:ss'.

 

This note describes known issues with Sql*Loader and NLS setting and how to solve these.

 

 

SCOPE & APPLICATION

-------------------

 

This document is useful for database administrators and operators who are using

SQL*Loader to load data into the Oracle database.

 

Most problems with Sqloader and date values are due to an incorrect setting of the

NLS session parameters NLS_DATE_FORMAT and NLS_LANG.

 

 

How to change the NLS_DATE_FORMAT and NLS_LANG

----------------------------------------------

 

- on UNIX:

 

Korn shell:

export NLS_DATE_FORMAT=<format>

export NLS_LANG=<language_territory.characterset>

 

Bourne shell:

set NLS_DATE_FORMAT=<format>

set NLS_LANG=<language_territory.characterset>

export NLS_DATE_FORMAT

export NLS_LANG

 

csh:

setenv NLS_DATE_FORMAT <format>

setenv NLS_LANG =<language_territory.characterset>

 

- on Windows NT:

 

set NLS_DATE_FORMAT=<format>

set NLS_LANG=<language_territory.characterset>

 

 

Common Sql*loader issues due to incorrect NLS settings

-----------------------------------------------------

 

1) ORA-01830 loading a date

2) ORA-01861 loading a date

3) ORA-01843 loading a date

4) Sql*Loader and Y2K

 

 

1) ORA-01830 loading a date

========================

 

ORA-01830: date format picture ends before converting entire input string

 

You are attempting to use SQL*Loader to insert a date and timestamp into every

record.

 

The format of the data does not match the NLS_DATE_FORMAT of the database(or the

session).

 

You will receive an ORA-01830 if the NLS_DATE_FORMAT is some subset of the data's

format.

 

For example, the format of the data is: yyyy-mon-dd:hh24:mi:ss

and NLS_DATE_FORMAT = yyyy-mon-dd.

 

Example:

 

A. Create a table:

 

create table loadme (

col1 varchar2(10),

col2 number(10),

col3 date not null);

 

B. Set up the Sql Loader control file as follows:

 

load data

infile *

insert into table loadme

fields terminated by "," optionally enclosed by '"'

(col1, col2 integer external, col3 constant "23-jun-98:01:34:06")

begindata

"rjohnson",1234

"mjohnson",5678

 

 

C. Conventional path load

 

C.1 Run SQL*Loader:

 

sqlldr scott/tiger control=loadme.ctl

 

C.2 Select from the table (no rows are selected):

 

SQL> select * from loadme;

 

no rows selected

 

C.3 Check the contents of the SQL*Loader log file and find

the following errors:

 

Insert option in effect for this table: INSERT

 

Column Name Position Len Term Encl Datatype

----------- --------- ----- ---- ---- ---------------------

COL1 FIRST * , O(") CHARACTER

COL2 NEXT * , O(") CHARACTER

COL3 CONSTANT ' 23-jun-98:01:34:06'

 

Record 1: Rejected - Error on table LOADME, column COL3.

ORA-01830: date format picture ends before converting entire

input string

 

Record 2: Rejected - Error on table LOADME, column COL3.

ORA-01830: date format picture ends before converting entire

input string

 

 

D. Direct path loading

 

D.1 Run SQL*Loader:

sqlldr scott/tiger control=loadme.ctl DIRECT=TRUE

 

you will get the errors on the command line:

 

Error calling once/load initialization

ORA-02355: Conversion error occurred on CONSTANT field COL3

ORA-02351: Record 0: Rejected - Error on table LOADME, column COL3

ORA-01830: date format picture ends before converting entire input string

 

 

Solution Description:

=====================

 

1. Conventional and Direct Path load:

 

At the UNIX OS evironment level, set "NLS_LANG" and "NLS_DATE_FORMAT".

 

An individual user's date format can be modified by setting "NLS_LANG" and

"NLS_DATE_FORMAT". Both parameters must be set. The "NLS_DATE_FORMAT"

parameter does not take effect unless the "NLS_LANG" parameter is set.

 

Setting these parameters in the unix environment overrides the settings in the

"init<sid>.ora" file.

 

The value for "NLS_LANG" should be the "NLS_LANGUAGE_NLS_TERRITORY" of the

database.

 

To find these values use the following query:

 

SQL> select * from v$nls_parameters;

 

PARAMETER VALUE

------------------------- ----------

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_DATE_FORMAT DD-MON-YY

NLS_DATE_LANGUAGE AMERICAN

NLS_CHARACTERSET US7ASCII

NLS_SORT BINARY

NLS_CALENDAR GREGORIAN

 

 

The value of "NLS_LANGUAGE" is "AMERICAN" and "NLS_TERRITORY" is "AMERICA".

"NLS_LANG" would be set to "AMERICAN_AMERICA.US7ASCII".

 

Using the appriopriate syntax for the current unix shell set the value for

"NLS_LANG".

 

Example:

 

setenv NLS_LANG AMERICAN_AMERICA.US7ASCII

 

Next set the "NLS_DATE_FORMAT" to the format of the date in the file to be

loaded.

 

Example:

 

setenv NLS_DATE_FORMAT 'dd-mon-yy:hh:mi:ss'

 

 

 

2. Conventional Path load only:

 

Change the default for the database in the "init<sid>.ora".

 

Set the nls_date_format in the init.ora file to the format of the date and

time stamp for the constant in the Sql*Loader control file.

Or, optionally set the date and time stamp for the constant to the nls_date_format of the

database.

 

Please note that setting the parameter in the init.ora file you change the

format for the entire database, whereas setting the NLS_DATE_FORMAT in your

unix environment you only change the format for your session, not the entire database.

 

A. Edit the init.ora file, entering a valid nls_date_format.

 

Example that matches the sample Sql*Loader control file follows:

 

nls_date_format="dd-mon-yy:hh:mi:ss"

 

B. Shutdown and restart the database to allow the new nls_date_format

parameter to take effect.

 

C. check the database nls_date_format parameter.

 

SVRMGR> show parameters nls_date_format;

 

NAME TYPE VALUE

--------------- ------ ------------------

nls_date_format string dd-mon-yy:hh:mi:ss

 

3. Conventional Path load only:

 

If the datafile actually contains a null placeholder for this date field, you

can use the NVL function to replace the null with a constant value. Note that if

the column is the last one in the data and does not have the null string enclosure,

the TRAILING NULLCOLS option will need to be used. Also, the NLS_DATE_FORMAT has

to be set as explained above to match the format of the constant.

 

Example:

 

load data

infile *

insert into table loadme

fields terminated by "," optionally enclosed by '"'

trailing nullcols

(col1, col2 integer external, col3 "nvl(:col3,'23-jun-98:01:34:06')")

begindata

"rjohnson",1234,

"mjohnson",5678,

 

 

Explanation:

============

 

The date and time format you have specified in the SQL*Loader control file

does not match the nls_date_format specified for the database. This can be

overridden at the session level.

 

 

2) ORA-01861 loading a date

========================

 

ORA-01861: literal does not match format string"

 

The format of the data does not match the NLS_DATE_FORMAT of the database(or the

session).

 

You will receive an ORA-01861 if the NLS_DATE_FORMAT is different and is not just

a subset of the data's format.

 

For example, the format of the data is:

 

yyyy-mon-dd:hh24:mi:ss

 

and NLS_DATE_FORMAT = dd-mon-yy.

 

You want to convert the data into the corresponding NLS_DATE_FORMAT.

 

 

Solution Description:

=====================

 

Use the to_date SQL function to specify the source data's format.

 

Example controlfile if the NLS_DATE_FORMAT='dd-mon-yy':

 

load data

infile *

insert into table loaddate

fields terminated by "," optionally enclosed by '"'

(col1,

col2 integer external,

col3 date 'dd-mon-yy' "to_date(:col3,'yyyy-mon-dd:hh24:mi:ss')" )

begindata

"rjohnson",1234,"2000-jun-23:01:00:00"

 

Explanation:

============

 

The format mask for the source data must explicitly be included if it does

not match the NLS_DATE_FORMAT exactly.

 

 

 

3) ORA-01843 loading a date

========================

 

ORA-01843: not a valid month

 

You are trying to load a data in a local setting (language and) that is not the same as the db one.

 

The format of the month does not match the NLS_LANG of the database(or the session).

 

If you do not change any settings regarding the date format in Oracle you

may see the following message "ORA-01843: not a valid month" when trying to

load a french date such as '29-Fev-2000 17:59:32'.

 

To avoid this you need to change the NLS_DATE_FORMAT in the database and

the NLS_LANG environment variable at the OS level.

 

Furthermore, you need to apply the SQL operator TO_DATE(<string>, <format>)"

to the field.

 

Example:

 

A. Control file

 

load data

infile *

into table emp

append

fields terminated by ',' optionally enclosed by '"'

(

empno,

ename char(16) "trim(:ename)",

job char(32) "trim(:job)",

sal "to_number(:sal, '9999.99')",

hiredate date 'dd-Mon-yyyy hh24:mi:ss'

)

begindata

9991, Legrand ,analyst,3205.16, "18-Jan-2000 11:15:30"

9992, "de laveine" ," Salesman ", 1524.50,"29-Fev-2000 17:59"

 

B. Change the NLS_LANG setting

 

Windows NT: set NLS_LANG=French

UNIX: export NLS_LANG=French

 

D. Load data

 

sqlldr scott/tiger to_date.ctl

 

C. Check the data loaded

 

select empno, ename, job, sal, hiredate from emp where empno > 9000;

 

EMPNO ENAME JOB SAL HIREDATE

------ ---------- --------- ---------- --------------------

9991 Legrand analyst 3205.16 18-Jan-2000 11:15:30

9992 de laveine Salesman 1524.5 29-Feb-2000 17:59:00

 

 

Explanation:

============

 

Set NLS_LANG to French because of the name of the month "Fev" for "Fevrier"

and the date format to 'dd-Mon-yyyy hh24:mi:ss': "Mon" for "Fev" and "hh24"

for "17".

 

 

4) Sql*Loader and Y2K

==================

 

After a SQL*Load containing date fields, year 2099 appears

instead of year 1999. Date format may or may not be indicated

in the controlfile.

 

For example:

A. From table nls_session_parameters:

NLS_DATE_FORMAT=DD-MON-YY

 

B. Table year2k is created with a date field in it:

create table year2k (ename varchar2(40), dates date);

 

C. Controlfile without specifying date format is created.

This will show how 2099 date gets into the table:

DATA

INFILE *

INTO TABLE YEAR2K

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

(dates)

BEGINDATA

09-JUNE-99

 

D. Load tha data

E. Set from sqlplus the full year format to see what's actually being stored

 

- alter session set nls_date_format='DD/MON/YYYY';

 

- select * from year2k;

 

DATES

-----------

09/JUN/2099

 

F. Create another table for second part of the test

 

create table year2k1 (ename varchar2(40), dates date);

 

G. Create a new controlfile specifying the date format.

 

LOAD DATA

INFILE *

INTO TABLE YEAR2K1 (DATES POSITION (1:40) DATE "DD-MON-RR")

BEGINDATA

09-MAY-99

 

H. Load the data

I. Set from sqlplus the full year format to see what's actually being stored

 

- alter session set nls_date_format='DD/MON/YYYY';

 

- select * from year2k1;

 

DATES

-----------

09/MAY/1999

 

 

Explanation:

============

 

The reason for that is RR format follows the following rule:

Years 0-49 will belong to 2000-2049

Years 50-99 will belong to 1950-1999

 

Date format in controlfile was indicated with wrong syntax.

If the RR format is not used, the YY format is assumed and 99 corresponds

to 2099 in YY format where it is 1999 in RR format.

[bnvdarklord]

Έτρεξα αυτό

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

 

και τώρα το

SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';

βγάζει YYYY-MM-DD HH24:MI:SS

 

αλλά ακόμα δεν τα περνάει...

 

Ανεβαζω τα αρχεια να δειτε.

[lefterhs]

Όχι, το να αλλάξεις την παράμετρο σε ένα sqlplus session που άνοιξες, δε σημαίνει κάτι.

Μιλάμε για environment variable στο λειτουργικό.

Αν είσαι σε windows, στο command prompt πριν δώσεις την sqlldr εντολή, γράψε:

set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS [χωρίς εισαγωγικά]

 

Αμέσως μετά δώσε την sqlldr εντολή.

 

Αν χρειαστεί να θέσεις και την NLS_LANG, εκτέλεσε το παρακάτω από sqlplus [login as sys or system]:

SELECT A.VALUE||'_'||B.VALUE||'.'||C.VALUE

FROM V$NLS_PARAMETERS A, V$NLS_PARAMETERS B, V$NLS_PARAMETERS C

WHERE A.PARAMETER='NLS_LANGUAGE'

AND B.PARAMETER='NLS_TERRITORY'

AND C.PARAMETER='NLS_CHARACTERSET';

 

Αυτό που θα σου επιστρέψει, κάνε το set στο command prompt όπως και την NLS_DATE_FORMAT, για παράδειγμα:

set NLS_LANG=AMERICAN_AMERICA.EL8ISO8859P7

 

και αφού κάνεις και set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS, τρέξε τον sqlloader.

[bnvdarklord]

Δεν αλλαξε τίποτα.

Δοκίμασα χωρίς to_date και εβγαλε αυτό αν βοηθαει

 

ORA-01841: το (πλήρες) έτος πρέπει να είναι μεταξύ -4713 και +9999 και όχι 0 αλλα οπως θα δεις στο attachment δεν εχει μηδενικές ημερομηνιες...

 

edit: εντωμεταξύ αυτό το

ORA-00907: λείπει δεξιά παρένθεση

στην περπτωση που εχω to_date/to_timestamp τι σημαινει;

[lefterhs]

Κάνε και μια αλλαγή:

LOAD DATA

INFILE 'customers.txt'

INTO TABLE customers

FIELDS TERMINATED BY ";" TRAILING NULLCOLS

(

custCode INTEGER,

custBirthYear INTEGER,

custOccupation CHAR,

custGender INTEGER,

custNumChildren INTEGER,

registrationDate DATE "YYYY-MM-DD HH24:MI:SS"

)

[bnvdarklord]

Το εχω δοκιμάσει

 

Δοκίμασα χωρίς to_date και εβγαλε αυτό αν βοηθαει

 

ORA-01841: το (πλήρες) έτος πρέπει να είναι μεταξύ -4713 και +9999 και όχι 0 αλλα οπως θα δεις στο attachment δεν εχει μηδενικές ημερομηνιες...

 

edit: Υπάρχει περίπτωση να το προκαλεί το occupation που ειναι varchar;

 

edit2: ΤΟ ΕΦΤΙΑΞΑ βγάζοντας τα integer απο το control file...

[lefterhs]

Ωραίος.

Ο sqlloader είναι δύστροπο εργαλείο.

Είδες, δε σου έβγαζε error στο σημείο που ήταν το λάθος...

[bnvdarklord]

To κατάλαβα γιατι μου εβγαζε τον seperator στο log οτι ειναι το ; αλλά μονο στα πεδία που δεν ηταν integer. Λογικα με το integer διαβαζε fixed size και αγνοούσε τον seperator.