Tim, July 13, 2006 - 9:13 am UTC
Tom,
I am not a programer. can your example be done on plsql also?
Tim
July 13, 2006 - 9:25 am UTC
nope, plsql is very safe.
Other "known" path(s) to get corrupt data into the DB?
Martin Trappel, August 07, 2006 - 3:21 am UTC
I am currently trying to track down an issue where we got some very incorrect data in a 9i2 table.
a) Is it possible to get such corrupt NUMBER or DATE data into Oracle by just issuing a SQL-Query (i.e. sending a query string w/o any bind variable to the DB)? (Namly using a C++ Application and OO4O.)
b) Is it possible for such corrupt data in one column to mess up another column?
many thanks, great work!
- Martin -
August 07, 2006 - 8:05 am UTC
a) not under normal operating circumstances
b) sure, an assigment could do that.
:)
Martin Trappel, August 07, 2006 - 8:56 am UTC
is it possible in java ?
Amit Poddar, December 01, 2006 - 12:25 pm UTC
Tom,
I am not a programer. can your example be done on Java also?
Amit
December 01, 2006 - 12:49 pm UTC
yes, but why bother - it demonstrates that
a) there is a bad way to do this
b) you should not use this bad way
I'm not going to generate "more bad ways"
reason for asking
amit poddar, December 01, 2006 - 1:29 pm UTC
Tom,
Reason I ask is because we have a database with such corrupt dates. The application using this table is written in java. The developers are saying that this cannot happen and there is some database corruption.
If I show them a way this can happen in java then they can start looking at the code and try to figure out where this problem lies.
December 01, 2006 - 9:41 pm UTC
how about you ask them for their code, then we can look at how they bind...
Maybe the year 0 bug ?
Pierre Forstmann, December 02, 2006 - 4:09 am UTC
There is a way to get some "inconsistent" data (date with year 0) in the database with plain SQL.
Example:
SQL> ALTER session SET nls_date_format='DD/MM/YYYY';
Session altered.
SQL> DESC td;
Name NULL? Type
----------------------------------------- -------- ----------------------------
C DATE
SQL> INSERT INTO td VALUES(to_date('00/00/0000'));
INSERT INTO td VALUES(to_date('00/00/0000'))
*
ERROR at line 1:
ORA-01847: day of month must be BETWEEN 1 AND last day of month
SQL> INSERT INTO td VALUES('01/00/0000');
INSERT INTO td VALUES('01/00/0000')
*
ERROR at line 1:
ORA-01843: NOT a valid month
SQL> INSERT INTO td VALUES('01/01/0000');
INSERT INTO td VALUES('01/01/0000')
*
ERROR at line 1:
ORA-01841: (full) year must be BETWEEN -4713 AND +9999, AND NOT be 0
SQL> INSERT INTO td VALUES(TO_DATE('01/01/0001','DD/MM/YYYY')-1)
2 ;
1 row created.
SQL> SELECT * FROM td;
C
----------
31/12/0000
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS FOR 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
SQL> SELECT to_char(c,'DD/MM/YYYY') FROM td;
TO_CHAR(C,
----------
00/00/0000
SQL> SELECT * FROM nls_session_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE FRENCH
NLS_TERRITORY FRANCE
NLS_CURRENCY Ç
NLS_ISO_CURRENCY FRANCE
NLS_NUMERIC_CHARACTERS ,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD/MM/YYYY
NLS_DATE_LANGUAGE FRENCH
NLS_SORT FRENCH
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY Ç
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
December 02, 2006 - 1:11 pm UTC
there is a year zero when you do date arithmetic - it is "just so"
Data Rejection Problem
Girish, December 05, 2006 - 3:48 am UTC
Hi Tom,
In Data Warhouse while doing an ETL some data gets rejected due to problem with data(and this problem could be different for each data that is rejected).If we want to know why this data is rejected and take corrective action based on nature of problem, how to handle this and provide a solution to this in Oracle PL/SQL
Any hint would be of great help
Thanks,
Girish
December 05, 2006 - 9:58 pm UTC
sort of a "broad" question
search site for
"save exceptions"
"log errors"
in quotes like that for 9i and 10g solutions.
Reader, February 29, 2008 - 7:21 am UTC
Tom,
I am getting the below error while loading a pipe delimited file using SQL*Loader. I checked the format of date fields. All are in YYYYMMDD format and the control file has the format of YYYYMMDD.
Record 41: Rejected - Error on table test, column dt.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
There are some rows with character data as shown below:
|d'Aviation'|20080228|. Does this cause any problem while loading?
Thank You
March 01, 2008 - 10:33 am UTC
be neat to have an example wouldn't it.
then we could reproduce it and diagnose it.
for you see, without a control file - no one could say anything.
Reader, February 29, 2008 - 7:23 am UTC
Sorry for the type in my previous question. Some rows has the character fields as shown below:
|d'Aviation|20080228|
March 01, 2008 - 10:33 am UTC
*need control file*
need create table statement
need at least one complete record to go with control file to diagnose.