Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Martin.

Asked: July 19, 2004 - 11:59 am UTC

Last updated: March 01, 2008 - 10:33 am UTC

Version: 9.1

Viewed 1000+ times

You Asked

Tom,

I ran into something quite strange:

SQL> desc oe_order_lines_All
Name Null? Type
------------------------------- -------- ----
LINE_ID NOT NULL NUMBER
ORG_ID NUMBER
HEADER_ID NOT NULL NUMBER
..
ORDERED_QUANTITY NUMBER
..
UNIT_SELLING_PRICE NUMBER
..
..

SQL> select unit_selling_price * ordered_quantity
2 from oe_order_lines_all oel
3 where header_id = 1930
4 and line_id = 2158;
select unit_selling_price * ordered_quantity
*
ERROR at line 1:
ORA-01426: numeric overflow


SQL> select unit_selling_price
2 from oe_order_lines_all oel
3 where header_id = 1930
4 and line_id = 2158
5 ;

UNIT_SELLING_PRICE
------------------
-~




What the heck? This is a Number column. How can it ever contain '-~'?
The issues can be easily fixed by just updating the value but I'm confused how it could have gotten in there? I think the column is filled by an external program, does this mean external programs (C, JAVA) could lead to data corruption if you don't code them well? Shouldn't oracle always validate the value that's been issued to be inserted?


Regards,

Martin


and Tom said...

if the external program uses the native types -- the Oracle number type -- we just load whatever bytes are sent to us.

If you use the native Number type - no verification (there is no need, you said "here, take these bytes")

if you use int, double, char[] -- anything like that, we safely convert the int/double/char[] to a well formed Oracle Number type and insert it.


My suspicion would be the developer used the native number type -- at which point, we rely on them to give us "the right stuff" and they did not.

I see this with dates very often -- people try to use the DATE type directly and load 7 bytes of garbage in -- in pro*c, it might look like this:


tatic void process()
{

typedef char DATE_TYPE[7];

EXEC SQL TYPE DATE_TYPE is DATE;



typedef struct mystruct
{
DATE_TYPE x;
int y;
}
mystruct_type;

mystruct_type data = { {120,100,4,3,100,123,123}, 1 };

EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL DROP TABLE TEST_DATE_TBL;

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL CREATE TABLE TEST_DATE_TBL ( X DATE, Y INT );

EXEC SQL INSERT INTO TEST_DATE_TBL (x,y) VALUES ( :data );

EXEC SQL COMMIT WORK;
}


we'll just take whatever is sent to us. the applications should pretty much avoid these internal types -- number and date -- because they always mess them up.

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Tim, July 13, 2006 - 9:13 am UTC

Tom,
I am not a programer. can your example be done on plsql also?

Tim

Tom Kyte
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 -

Tom Kyte
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

Tom Kyte
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.

Tom Kyte
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. 

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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|
Tom Kyte
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.