Home
All Oracle Error Codes
Oracle DBA Forum

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

RE: Wrong results from date comaprisons

Ken Naim

2005-05-24

Replies:
I exported the table and imported it to a different schema, and the query
worked as it should against the new table. I then rebuilt the old table and
it works fine now as well. The table structure is

CREATE TABLE TS.AGENCY_HISTORY
(
LGL_ENTY_ID             NUMBER      NOT NULL,
LGL_ENTY_ROLE_TYP_CD       VARCHAR2(15 BYTE) NOT NULL,
AGY_HIST_NO             NUMBER      NOT NULL,
AGY_TYP_CD              VARCHAR2(15 BYTE),
AGY_HIST_EFF_DT           DATE       NOT NULL,
AGY_HIST_END_DT           DATE,
AGY_HIST_CNTC_NM          VARCHAR2(40 BYTE),
AGY_HIST_REF_NO           VARCHAR2(30 BYTE),
COMS_SCH_TYP_CD           VARCHAR2(15 BYTE),
AGY_HIST_COMS_SCH_OVRD_RT    NUMBER,
LA_AGY_HIST_ERR_COV_EXPR_DT   DATE,
NOTE_ID                NUMBER,
EMPL_ID_CREA             NUMBER      NOT NULL,
AGY_HIST_CREA_DTM         DATE       NOT NULL,
EMPL_ID_UPDT             NUMBER,
AGY_HIST_UPDT_DTM         DATE,
AGY_HIST_VOID_IND         CHAR(1 BYTE)  DEFAULT 'n'
NOT NULL,
LA_AGY_HIST_PRC_BIND_AUTH_IND CHAR(1 BYTE)  DEFAULT 'n'
NOT NULL
)
TABLESPACE TS
LOGGING
COMPRESS
NOCACHE
NOPARALLEL
MONITORING;


-----Original Message-----
From: Vlado Barun [mailto:vlado@(protected)]
Sent: Tuesday, May 24, 2005 12:07 PM
To: 'Ken Naim'; 'Igor Neyman'; oracle-l@(protected)
Subject: RE: Wrong results from date comaprisons

Please provide the structure of the table...

Vlado Barun, M.Sc.
Senior Data Architect, Cadre5
www.cadre5.com
Office: 865 690 4442
Mobile: 865 335 7652
e-mail: vlado@(protected)
AIM: vbarun2

-----Original Message-----
From: Ken Naim [mailto:kennaim@(protected)]
Sent: Tuesday, May 24, 2005 12:40 PM
To: 'Igor Neyman'; 'Vlado Barun'; oracle-l@(protected)
Subject: RE: Wrong results from date comaprisons

You are correct, the statement I pasted was the first one I ran but then
realized there could be a century difference so I reran it using the full
year. The data was from the second statement pasted below.

select to_char(agy_hist_eff_dt,'DD-Mon-YYYY HH24:mi:ss')
s,to_char(agy_hist_end_dt,'DD-Mon-YYYY HH24:mi:ss') e,
agy_hist_end_dt-agy_hist_eff_dt d from agency_history a where
agy_hist_void_ind='y'
and agy_hist_eff_dt!=agy_hist_end_dt

-----Original Message-----
From: Igor Neyman [mailto:ineyman@(protected)]
Sent: Tuesday, May 24, 2005 11:07 AM
To: kennaim@(protected)
Subject: RE: Wrong results from date comaprisons

Are you sure, that you are showing the exact statement you are running, or
that your output produced by the statement you showed?

to_char(agy_hist_eff_dt,'DD-Mon-YY HH24:mi:ss') should return something
like:

01-Jan-04 00:00:00

and not:

01-Jan-2004 00:00:00

as shown in your output.

Igor Neyman, OCP DBA
ineyman@(protected)



-----Original Message-----
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)
Sent: Tuesday, May 24, 2005 10:47 AM
To: 'Vlado Barun'; oracle-l@(protected)
Subject: RE: Wrong results from date comaprisons

The fields are date fields so AFAIK it cannot be any more precise. Also if
that were the case the diff should return some tiny fraction

-----Original Message-----
From: Vlado Barun [mailto:vlado@(protected)]
Sent: Tuesday, May 24, 2005 10:38 AM
To: kennaim@(protected)
Subject: RE: Wrong results from date comaprisons

Depends on how precise the date columns are... Are you going down to the
milliseconds? Your output only goes to the second...


Vlado Barun, M.Sc.
Senior Data Architect, Cadre5
www.cadre5.com
Office: 865 690 4442
Mobile: 865 335 7652
e-mail: vlado@(protected)
AIM: vbarun2
-----Original Message-----
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]
On Behalf Of Ken Naim
Sent: Tuesday, May 24, 2005 11:31 AM
To: oracle-l@(protected)
Subject: Wrong results from date comaprisons

Has anyone experienced this with 10g (10.1.0.4.0)? I am getting wrong
results from the following query. The start and end dates are equal yet,
they are returned by the query and the difference column shows again that
they should be equal although there are many rows that are filtered
correctly. There aren't any indexes on the fields in questions and there is
a constraint on the start date field


select to_char(agy_hist_eff_dt,'DD-Mon-YY HH24:mi:ss')
s,to_char(agy_hist_end_dt,'DD-Mon-YY HH24:mi:ss') e,
agy_hist_end_dt-agy_hist_eff_dt d from agency_history a where
agy_hist_void_ind='y'
and agy_hist_eff_dt!=agy_hist_end_dt


S        E        D
01-Jan-2004 00:00:00  01-Jan-2004 00:00:00  0
13-Oct-2003 00:00:00  13-Oct-2003 00:00:00  0
13-Jul-2004 00:00:00  13-Jul-2004 00:00:00  0
21-Oct-2004 00:00:00  21-Oct-2004 00:00:00  0
01-Jan-2005 00:00:00  01-Jan-2005 00:00:00  0
01-Jan-2004 00:00:00  01-Jan-2004 00:00:00  0
01-Jan-2003 00:00:00  01-Jan-2003 00:00:00  0
22-Jul-1999 00:00:00  22-Jul-1999 00:00:00  0
05-Jan-1998 00:00:00  05-Jan-1998 00:00:00  0
01-Jan-2004 00:00:00  01-Jan-2004 00:00:00  0
07-Jan-2004 00:00:00  07-Jan-2004 00:00:00  0
10-Jun-2004 00:00:00  10-Jun-2004 00:00:00  0
09-Jun-2004 00:00:00  09-Jun-2004 00:00:00  0
09-Nov-2004 00:00:00  09-Nov-2004 00:00:00  0
01-Jan-2005 00:00:00  01-Jan-2005 00:00:00  0
01-Jan-2003 00:00:00  01-Jan-2003 00:00:00  0
01-Jan-2005 00:00:00  01-Jan-2005 00:00:00  0
28-Jun-2002 00:00:00  28-Jun-2002 00:00:00  0
01-Jun-2001 00:00:00  01-Jun-2001 00:00:00  0
01-Jan-2003 00:00:00  01-Jan-2003 00:00:00  0
03-Apr-2000 00:00:00  03-Apr-2000 00:00:00  0
01-Apr-1999 00:00:00  01-Apr-1999 00:00:00  0
28-May-2002 00:00:00  28-May-2002 00:00:00  0
01-Apr-1999 00:00:00  01-Apr-1999 00:00:00  0
01-Jan-2003 00:00:00  01-Jan-2003 00:00:00  0
05-Jan-1998 00:00:00  05-Jan-1998 00:00:00  0
01-Jan-2003 00:00:00  01-Jan-2003 00:00:00  0
01-Jun-2001 00:00:00  01-Jun-2001 00:00:00  0
03-Apr-2000 00:00:00  03-Apr-2000 00:00:00  0
01-Jun-2001 00:00:00  01-Jun-2001 00:00:00  0
01-Jun-2001 00:00:00  01-Jun-2001 00:00:00  0
28-Jun-2002 00:00:00  28-Jun-2002 00:00:00  0
01-Jan-2003 00:00:00  01-Jan-2003 00:00:00  0
22-Jun-1999 00:00:00  22-Jun-1999 00:00:00  0
02-Jul-1999 00:00:00  02-Jul-1999 00:00:00  0
18-Apr-2000 00:00:00  18-Apr-2000 00:00:00  0

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l





--
http://www.freelists.org/webpage/oracle-l