Skip to Main Content
Forums

Java Development Tools

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

random JBO-27122 and ORA-01890 errors

SNikiforovJan 20 2009 — edited Jan 21 2009
hi All,

I am randomly getting this error. In Toad this query works always fine, but in web application raises this error from time to time (seldom). What can be a reason.
Thanks,
SNikiforov

JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT WfsSignIn.SSN, BhsSatLoc.LOC_DESC SAT_LOC, WFS.PK_WFS_SIGNOUT.sf_pft_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) PftY, WFS.PK_WFS_SIGNOUT.sf_h_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) HearingY, WFS.PK_WFS_SIGNOUT.sf_vis_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) VisionY, WFS.PK_WFS_SIGNOUT.sf_v_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) VitalY, WFS.PK_WFS_SIGNOUT.sf_f_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) FitnesslY, WFS.PK_WFS_SIGNOUT.sf_MM_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) MentorY, WFS.PK_WFS_SIGNOUT.sf_Consent_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE) Consent, WFS.PK_WFS_SIGNOUT.sf_D_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) DISY, WFS.PK_WFS_SIGNOUT.sf_B_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) BloodY, WFS.PK_WFS_SIGNOUT.sf_SA_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) SAMHY, FDNY_APP_DATE -TO_DATE('25-JUL-02', 'DD-MON-RR') wtc, trunc(:P_DATE) today, :P_DOC doc, substr(WfsSignIn.SSN,6,4) SSN4, WfsSignIn.EXAM_DATE, WfsSignIn.SIGN_IN_ID, WfsSignIn.USERID, WfsSignIn.FNAME, WfsSignIn.LNAME, ComFdemp.GENDER, WfsSignIn.RANK_CODE, WfsSignIn.UNIT_CODE, ComFdemp.WORK_LOCATION, WfsSignIn.WALK_IN, ComFdemp.BIRTH_DATE, PK_WFS_SIGNOUT.sf_sign_out(WfsSignIn.SSN) Can_Sign_Out, trunc(nvl(sf_empall_age(WfsSignIn.ssn),0)) Age, case when WfsSignIn.PHYSICIAN_CHECK ='Y' then 'Yes' else 'No' end PHYSICIAN_CHECK, sf_Emp_Cand_Name (WfsSignIn.ssn) Fullname, sf_get_title_wfs (WfsSignIn.ssn) title, WfsSignIn.SIGN_OUT, WfsSignIn.ANNUAL_YR ANNUALYR, 'Y' SIGNED_IN, pk_wfs.SF_GET_STATUS(WfsSignIn.ssn) Duty, rownum FROM HRIS.WFS_SIGN_IN WfsSignIn,EMP_CAND_V ComFdemp,bhs_sat_loc BhsSatLoc WHERE (WfsSignIn.SAT_LOC=:P_SAT_LOC or :P_SAT_LOC ='AA') and WfsSignIn.SAT_LOC = BhsSatLoc.SAT_LOC_CODE(+) and ComFdemp.Ssn=WfsSignIn.Ssn and not (nvl((WfsSignIn.SIGN_OUT),'N') in (decode(:P_Table,'Signed In','Y','N'),decode(:P_Table,'Signed In','A','N'))) and ( ( ((:P_FIELD is null or :P_FIELD='SSN') and WfsSignIn.SSN like '%'||:P_PARAM)and (ComFdemp.LNAME) like '%'||UPPER(:P_FNAME) ||'%') or (:P_FIELD='Name' and (ComFdemp.FNAME) like '%'||UPPER(:P_FNAME) ||'%' ) ) and WfsSignIn.EXAM_DATE=trunc(TO_DATE(:P_DATE)) and (:P_Table='Signed In' or :P_Table='Signed Out') union select ComFdemp.SSN, '00' SAT_LOC, 'No' PftY, 'No' HearingY, 'No' VisionY, 'No' VitalY, 'No' FitnessY, 'No' MentorY, 'No' Consent, 'No' DISY, 'No' BloodY, 'No' SAMHY, FDNY_APP_DATE -TO_DATE('25-JUL-02', 'DD-MON-RR') wtc, trunc(:P_DATE) today, :P_DOC doc, substr(ComFdemp.SSN,6,4) SSN4, trunc(TO_DATE(:P_DATE)) EXAM_DATE, 0 SIGN_IN_ID, to_char(null) USERID, ComFdemp.FNAME, ComFdemp.LNAME, ComFdemp.GENDER, ComFdemp.RANK_CODE, ComFdemp.UNIT_CODE, ComFdemp.WORK_LOCATION, 'No' WALK_IN, ComFdemp.BIRTH_DATE, null Can_Sign_Out, ROUND(nvl(sf_empall_age(ComFdemp.ssn),0)) Age, 'No' PHYSICIAN_CHECK, sf_Emp_Cand_Name (ComFdemp.ssn) Fullname, sf_get_title_wfs (ComFdemp.ssn) title, 'Z' SIGN_OUT, to_number(substr(to_char(TO_DATE(:P_DATE),'MM/DD/YYYY'),7,4)) ANNUALYR, decode((select count(*) from HRIS.WFS_SIGN_IN where ssn=ComFdemp.ssn and EXAM_DATE>=trunc(TO_DATE(:P_DATE))),0,'N','Y') SIGNED_IN, pk_wfs.SF_GET_STATUS(ComFdemp.ssn) Duty, rownum from EMP_CAND_V ComFdemp WHERE (((ComFdemp.SSN like '%'||:P_PARAM and (ComFdemp.LNAME) like '%'||UPPER(:P_FNAME) ||'%') ) ) and (:P_Table<>'Signed In' and :P_Table<>'Signed Out')) QRSLT ORDER BY SAT_LOC DESC
ORA-01890: NLS error detected
----------------------------
THIS IS THE VO
SELECT WfsSignIn.SSN,
BhsSatLoc.LOC_DESC SAT_LOC,
WFS.PK_WFS_SIGNOUT.sf_pft_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) PftY,
WFS.PK_WFS_SIGNOUT.sf_h_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) HearingY,
WFS.PK_WFS_SIGNOUT.sf_vis_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) VisionY,
WFS.PK_WFS_SIGNOUT.sf_v_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) VitalY,
WFS.PK_WFS_SIGNOUT.sf_f_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) FitnesslY,
WFS.PK_WFS_SIGNOUT.sf_MM_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) MentorY,
WFS.PK_WFS_SIGNOUT.sf_Consent_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE) Consent,
WFS.PK_WFS_SIGNOUT.sf_D_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) DISY,
WFS.PK_WFS_SIGNOUT.sf_B_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) BloodY,
WFS.PK_WFS_SIGNOUT.sf_SA_check(WfsSignIn.SSN, WfsSignIn.EXAM_DATE ) SAMHY,
FDNY_APP_DATE -TO_DATE('25-JUL-02', 'DD-MON-RR') wtc,
trunc(:P_DATE) today,
:P_DOC doc,
substr(WfsSignIn.SSN,6,4) SSN4,
WfsSignIn.EXAM_DATE,
WfsSignIn.SIGN_IN_ID,
WfsSignIn.USERID,
WfsSignIn.FNAME,
WfsSignIn.LNAME,
ComFdemp.GENDER,
WfsSignIn.RANK_CODE,
WfsSignIn.UNIT_CODE,
ComFdemp.WORK_LOCATION,
WfsSignIn.WALK_IN,
ComFdemp.BIRTH_DATE,
PK_WFS_SIGNOUT.sf_sign_out(WfsSignIn.SSN) Can_Sign_Out,
trunc(nvl(sf_empall_age(WfsSignIn.ssn),0)) Age,
case when WfsSignIn.PHYSICIAN_CHECK ='Y' then 'Yes'
else 'No' end PHYSICIAN_CHECK,
sf_Emp_Cand_Name (WfsSignIn.ssn) Fullname,
sf_get_title_wfs (WfsSignIn.ssn) title,
WfsSignIn.SIGN_OUT,
WfsSignIn.ANNUAL_YR ANNUALYR,
'Y' SIGNED_IN,
pk_wfs.SF_GET_STATUS(WfsSignIn.ssn) Duty,
rownum
FROM HRIS.WFS_SIGN_IN WfsSignIn,EMP_CAND_V ComFdemp,bhs_sat_loc BhsSatLoc
WHERE (WfsSignIn.SAT_LOC=:P_SAT_LOC or :P_SAT_LOC ='AA') and WfsSignIn.SAT_LOC = BhsSatLoc.SAT_LOC_CODE(+) and ComFdemp.Ssn=WfsSignIn.Ssn and not (nvl((WfsSignIn.SIGN_OUT),'N') in (decode(:P_Table,'Signed In','Y','N'),decode(:P_Table,'Signed In','A','N'))) and
(
(
((:P_FIELD is null or :P_FIELD='SSN') and WfsSignIn.SSN like '%'||:P_PARAM)and (ComFdemp.LNAME) like '%'||UPPER(:P_FNAME) ||'%') or
(:P_FIELD='Name' and (ComFdemp.FNAME) like '%'||UPPER(:P_FNAME) ||'%' )
)
and
WfsSignIn.EXAM_DATE=trunc(TO_DATE(:P_DATE)) and (:P_Table='Signed In' or :P_Table='Signed Out')

union
select
ComFdemp.SSN,
'00' SAT_LOC,
'No' PftY,
'No' HearingY,
'No' VisionY,
'No' VitalY,
'No' FitnessY,
'No' MentorY,
'No' Consent,
'No' DISY,
'No' BloodY,
'No' SAMHY,
FDNY_APP_DATE -TO_DATE('25-JUL-02', 'DD-MON-RR') wtc,
trunc(:P_DATE) today,
:P_DOC doc,
substr(ComFdemp.SSN,6,4) SSN4,
trunc(TO_DATE(:P_DATE)) EXAM_DATE,
0 SIGN_IN_ID,
to_char(null) USERID,
ComFdemp.FNAME,
ComFdemp.LNAME,
ComFdemp.GENDER,
ComFdemp.RANK_CODE,
ComFdemp.UNIT_CODE,
ComFdemp.WORK_LOCATION,
'No' WALK_IN,
ComFdemp.BIRTH_DATE,
null Can_Sign_Out,
ROUND(nvl(sf_empall_age(ComFdemp.ssn),0)) Age,
'No' PHYSICIAN_CHECK,
sf_Emp_Cand_Name (ComFdemp.ssn) Fullname,
sf_get_title_wfs (ComFdemp.ssn) title,
'Z' SIGN_OUT,
to_number(substr(to_char(TO_DATE(:P_DATE),'MM/DD/YYYY'),7,4)) ANNUALYR,
decode((select count(*) from HRIS.WFS_SIGN_IN where ssn=ComFdemp.ssn and EXAM_DATE>=trunc(TO_DATE(:P_DATE))),0,'N','Y') SIGNED_IN,
pk_wfs.SF_GET_STATUS(ComFdemp.ssn) Duty,
rownum
from EMP_CAND_V ComFdemp
WHERE (((ComFdemp.SSN like '%'||:P_PARAM and (ComFdemp.LNAME) like '%'||UPPER(:P_FNAME) ||'%') )
) and
(:P_Table<>'Signed In' and :P_Table<>'Signed Out')
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 18 2009
Added on Jan 20 2009
#application-development-in-java, #jdeveloper-and-adf
1 comment
494 views