I have written the following code to fetch a date based on the users NLS_LANGUAGE variable. I am writing this code in BI Publisher inbuilt in Oracle Fusion but i doubt that it will matter. I have seen that it works fine for AMERICAN, SPANISH, etc but the same is not working for FRENCH or BRAZILIAN PORTUGUESE. The start_time field is of type TIMESTAMP. When i hardcode BRAZILIAN PORTUGUESE instead of the dynamic code, it works fine with tr.start_time. But when i use the dynamic code, it is giving this issue. Also if i replace tr.start_time with sysdate, then also it is working. I am getting the following error:
ORA-01877 string too long for the internal buffer
Please let me know where i am going wrong.
WITH nls AS (
SELECT 'NLS_DATE_LANGUAGE = '''||value||'''' value
FROM v$nls_parameters
WHERE parameter = 'NLS_LANGUAGE')
SELECT TO_CHAR(tr.start_time,'Day,DD Mon YYYY',nls.value) date_from
FROM table_list
I have also tried:
WITH nls AS
(SELECT value
FROM v$nls_parameters
WHERE parameter = 'NLS_LANGUAGE')
select
TO_CHAR(tr.start_time,'Day,DD Mon YYYY','NLS_DATE_LANGUAGE='''||nls.value||'''') date_from
FROM table_list
The below code also doesnt work:
SELECT TO_CHAR(systimestamp ,'Day,DD Mon YYYY', 'NLS_DATE_LANGUAGE = '''||value||'''') date_from FROM v$nls_parameters WHERE parameter = 'NLS_LANGUAGE'
The below two codes are working fine:
TO_CHAR(tr.start_time,'Day,DD Mon YYYY', 'NLS_DATE_LANGUAGE = '''||'BRAZILIAN PORTUGUESE'||'''')
TO_CHAR(sysdate,'Day,DD Mon YYYY', 'NLS_DATE_LANGUAGE = '''||nls.value||'''')
Full code is given below:
WITH nls AS
(SELECT value FROM v$nls_parameters WHERE parameter = 'NLS_LANGUAGE'
)
SELECT trg.tm_rec_grp_id,
tr.tm_rec_id,
TRUNC(trg.start_time) start_time,
TRUNC(trg.stop_time) stop_time,
tr.measure,
tr.unit_of_measure,
TO_CHAR(tr.start_time,'HH24:MI') line_start_time,
TO_CHAR(tr.stop_time,'HH24:MI') line_stop_time,
(
SELECT mda.daily_comment
FROM hxt_tm_header th,
hxt_tm_mtrx tm,
hxt_tm_mtrx_dly_atrs mda,
hxt_tm_mtrx_tbb_usgs mtu
WHERE th.time_bldg_blk_id = :p_tm_rec_grp_id
AND th.tm_header_id = tm.tm_header_id
AND tm.tm_mtrx_row_id = mda.tm_mtrx_row_id
AND tm.tm_mtrx_row_id = mtu.tm_mtrx_row_id
AND tr.tm_rec_id =
CASE
WHEN TO_CHAR(tr.start_time,'Day') = 1
THEN mtu.TM_BLDG_BLK_DTL_ID1 -- Monday
WHEN TO_CHAR(tr.start_time,'Day') = 2
THEN mtu.TM_BLDG_BLK_DTL_ID2
WHEN TO_CHAR(tr.start_time,'Day') = 3
THEN mtu.TM_BLDG_BLK_DTL_ID3
WHEN TO_CHAR(tr.start_time,'Day') = 4
THEN mtu.TM_BLDG_BLK_DTL_ID4
WHEN TO_CHAR(tr.start_time,'Day') = 5
THEN mtu.TM_BLDG_BLK_DTL_ID5
WHEN TO_CHAR(tr.start_time,'Day') = 6
THEN mtu.TM_BLDG_BLK_DTL_ID6
WHEN TO_CHAR(tr.start_time,'Day') = 7
THEN mtu.TM_BLDG_BLK_DTL_ID7 -- Sunday
END
) comment_text,
(
SELECT comments
FROM hxt_tm_header th
WHERE th.time_bldg_blk_id = trg.tm_rec_grp_id
) header_comments,
tr.resource_id,
TO_CHAR(tr.start_time,'Day,DD Mon YYYY','NLS_DATE_LANGUAGE='''||nls.value||'''') date_from,
tr.start_time time_entry_start_time,
tr.date_to,
hl.meaning status,
hl.lookup_code status_value,
tr.layer_code record_type,
tra.tm_rep_atrb_id pjc_time_repos_atrb_id,
tra.attribute_category pjc_attribute_category,
tra.attribute_varchar1 pjc_billable_flag,
tra.attribute_number1 pjc_project_id,
pab.segment1
||'-'
||pat.name project_name,
tra.attribute_number2 pjc_task_id,
ptv.task_number
||'-'
||ptv.task_name task_name,
tra.attribute_number4 pjc_project_unit,
exp.pjc_expenditure_type expenditure_type_id,
expt.sl_expenditure_type_name expenditure_type_name,
tra2.attribute_varchar3 project_activities,
hg.geography_name
||'-'
||hg.geography_code tax_area,
tra.attribute_number7 pjc_work_type
FROM hwm_tm_rec_grp trg,
hwm_tm_rec_grp trg2,
hwm_tm_rec_grp_usages trgu,
hwm_tm_rec tr,
hwm_tm_statuses ts,
hcm_lookups hl,
hwm_tm_rep_atrbs tra,
hwm_tm_rep_atrbs tra2,
hwm_tm_rep_atrb_usages trau,
hwm_tm_rep_atrb_usages trau2,
pjf_projects_all_b pab,
pjf_projects_all_tl pat,
pjf_tasks_v ptv,
hz_geographies hg,
hwm_tm_rep_m_pjc_exp_atrbs_v exp,
pjc_exp_types_ccw_v expt,
HWM_TM_REP_M_PJC_DOC_ATRBS_V esys,
nls
WHERE trg.tm_rec_grp_id = :p_tm_rec_grp_id
AND trg2.parent_tm_rec_grp_id = trg.tm_rec_grp_id
AND trg.latest_version = 'Y'
AND trg2.latest_version = 'Y'
AND trg.tm_rec_grp_version = trg2.parent_tm_rec_grp_version
AND trg2.tm_rec_grp_id = trgu.tm_rec_grp_id
AND trg2.tm_rec_grp_version = trgu.tm_rec_grp_version
AND trgu.tm_rec_id = tr.tm_rec_id
AND trgu.tm_rec_version = tr.tm_rec_version
AND trgu.latest_version = 'Y'
AND tr.resource_id = :p_resource_id
AND tr.latest_version = 'Y'
AND COALESCE(tr.delete_flag,'N') = 'N'
AND tr.tm_rec_id = exp.usages_source_id
AND TO_CHAR(expt.expenditure_type_id) = TO_CHAR(exp.pjc_expenditure_type)
AND pab.project_unit_id = expt.project_unit_id
AND esys.usages_source_id = tr.tm_rec_id
AND esys.usages_source_version = tr.tm_rec_version
AND expt.system_linkage_function = esys.pjc_document_type
AND exp.usages_source_version =
(SELECT MAX(eav.usages_source_version )
FROM hwm_tm_rep_m_pjc_exp_atrbs_v eav
WHERE eav.usages_source_id = tr.tm_rec_id
)
AND ts.tm_bldg_blk_id = trg.tm_rec_grp_id
AND ts.tm_bldg_blk_version = trg.tm_rec_grp_version
AND sysdate BETWEEN ts.date_from AND ts.date_to
AND hl.lookup_code = ts.status_value
AND hl.lookup_type = 'HWM_UI_STATUS'
AND EXISTS
(SELECT 1
FROM hwm_tm_status_def_b sdb
WHERE ts.tm_status_def_id = sdb.tm_status_def_id
AND upper(sdb.status_def_cd) = 'D_TM_UI_STATUS'
)
AND trau.usages_source_id = tr.tm_rec_id
AND trau.latest_version = 'Y'
AND trau.usages_source_version = tr.tm_rec_version
AND trau2.usages_source_id = tr.tm_rec_id
AND trau2.latest_version = 'Y'
AND trau2.usages_source_version = tr.tm_rec_version
AND tra.tm_rep_atrb_id = trau.tm_rep_atrb_id
AND tra2.tm_rep_atrb_id = trau2.tm_rep_atrb_id
AND tra.attribute_category = 'Projects'
AND tra2.attribute_category = 'ORA_CUSTOM'
AND pab.project_id = to_number(tra.attribute_number1)
AND pab.project_id = pat.project_id
AND pat.language = USERENV('LANG')
AND ptv.project_id = pat.project_id
AND ptv.task_id = to_number(tra.attribute_number2)
AND hg.geography_id = to_number(tra2.attribute_varchar1)
ORDER BY tr.start_time
Thanks in advance,
Anoop
