Date Format Errors ORA-1801 on 10.2.0.4 Database
(Doc ID 846080.1)
Last updated on AUGUST 04, 2018
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.4Information in this document applies to any platform.
Symptoms
- Getting ORA-01801- Had this error for over 2 years.
- Since upgraded the database to 10.2.0.4 didn't have any problems.
- Today changed the application servers and we faced the problem.
- Rebooting database servers or application servers seemed to trigger the problem.
Workarounds Used reflect a change of syntax in an example query. Substitute similar syntax for your query based on the example below:
Either use option 1 -
( (trunc(grv_bsl_trh) >= trunc(P_ILK_ATAMA) or trunc(grv_ayr_trh) >= trunc(P_ILK_ATAMA) ) )or use option 2 -
( (grv_bsl_trh >= P_ILK_ATAMA or grv_ayr_trh >= P_ILK_ATAMA ) )When facing the error changing query syntax from 1 to 2 or 2 to 1 will fix the issue
SELECT
coalesce(aile_hekimligi_birimi, ik_gorev_yeri, nvl(vk_gorev_yeri, nvl(gc_gorev_yeri,
nvl(td_gorev_yeri, birim_sira_no)))) BIRIM_SIRA_NO,
nvl(nvl(gecici_dis_kurum, iht_dis_kurum),g_kurum) g_kurum,
nvl(nvl(to_char(gecici_dis_birim),iht_dis_birim),dg_brm_kod) dg_brm_kod,
nvl(iht_unvan_kodu,unvan_kodu) ASIL_UNVAN, nvl(iht_brans_kodu, brans_kodu) ASIL_BRANS,
DECODE (ik_gorev_yeri, NULL, decode(vk_gorev_yeri, NULL ,
decode(nvl(to_char(gc_gorev_yeri),gecici_dis_kurum), NULL, decode(td_gorev_yeri, NULL,
decode(birim_sira_no, NULL,'D','A'),'T'), 'G'),'V'),'I') HIZMET_SEKLI,
coalesce(iht_unvan_kodu, ik_gorev_unv, nvl(vk_gorev_unv, nvl( GC_GOREV_UNV, nvl(TD_GOREV_UNV,
UNVAN_KODU)))) FIILI_UNVAN ,
HIZMET_TURU , HAREKET_TURU,ISLEM_TURU,GRV_BSL_TRH,GRV_AYR_TRH,BELGE_TRH, hareket_Tipi, dayanak_no
FROM HT_PER_HAREKETLERI
WHERE PSN=PRM_PSN
AND ISLEM_TURU IN ('1','3')
AND (ISLEM_TURU||HAREKET_TURU IN (SELECT ISLEM_TURU||HAREKET_TURU FROM HT_GNL_HAREKET_TURU
WHERE NVL(AYR_BAS,'E') IN ('A','B','H'))
OR ISLEM_TURU||HAREKET_TURU IN ('12','13','16','17', '18','19','34', '36'))
AND (ISLEM_TURU || HAREKET_TURU || HAREKET_TIPI not in
('361','362','3612','3622','3623','3613','3614','3615'))
AND NVL(IPTAL,0) != 1
and (ISLEM_TURU || HAREKET_TURU) not in ('115')
AND NVL(DUZELTME,0) != 1
and ( (trunc(grv_bsl_trh) >= trunc(P_ILK_ATAMA) or trunc(grv_ayr_trh) >= trunc(P_ILK_ATAMA)
) )
-- above line is option 1
-- and ( (grv_bsl_trh >= P_ILK_ATAMA or grv_ayr_trh >= P_ILK_ATAMA ) )
-- above line is option 2
ORDER BY DECODE(GRV_BSL_TRH, NULL, DECODE(GRV_AYR_TRH, NULL, BELGE_TRH, GRV_AYR_TRH), GRV_BSL_TRH)
,decode(grv_ayr_trh, NULL, decode(grv_bsl_trh, NULL, 0,1), 2) desc , nvl(gc_gorev_yeri,
nvl(td_gorev_yeri, nvl(birim_sirA_no,0))) ,UNVAN_KODU,ISLEM_TURU,HAREKET_TURU,HAREKET_TIPI ;- Used the workaound for the bug 5555683 but this did not resolve the problem
ERROR:
ORA-01801: date format is too long for internal buffer
----- Call Stack Trace -----
ksedmp ksddoa ksdpcg ksdpec ksfpec
kgesev ksesec1 ldxdts evadds evaopn2
evadcd qersoSORowP qerflFetchOutside qersoFetch opifch2
opifch opipls opiodr rpidrus skgmstack
rpidru rpiswu2 rpidrv psddr0 psdnal
pevm_BFTCHC pfrinstr_FTCHC pfrrun_no_tool pfrrun plsql_run
pricar pricbr prient2 prient kkxrpc
kporpc opiodr ttcpip opitsk opiino
opiodr opirip opidrv sou2o
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms
Cause
Solution
References