我编写了以下代码来根据用户NLS_LANGUAGE变量获取日期。我正在Oracle Fusion中内置的BI Publisher中编写此代码，但我怀疑这是否重要。我已经看到它适用于美国、西班牙等，但同样不适用于法语或巴西葡萄牙语。start_time字段的类型为 TIMESTAMP 。当我硬编码巴西葡萄牙语而不是动态代码时，它可以很好地处理tr.start_time。但是当我使用动态代码时，它会给出这个问题。此外，如果我用 sysdate 替换tr.start_time，那么它也在工作。我收到以下错误：
ORA-01877 string too long for the internal buffer
请让我知道我哪里出错了。
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
我也尝试过：
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
下面的代码也不起作用：
SELECT TO_CHAR(systimestamp ,'Day,DD Mon YYYY', 'NLS_DATE_LANGUAGE = '''||value||'''') date_from FROM v$nls_parameters WHERE parameter = 'NLS_LANGUAGE'
以下两个代码工作正常：
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||'''')
完整代码如下：
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
提前感谢，
阿诺普
也许可以这样尝试：
TO_CHAR(tr.start_time,'Day,DD Mon YYYY', 'NLS_DATE_LANGUAGE = '''||SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE')||'''')
TO_CHAR(tr.start_time,'Day,DD Mon YYYY', 'NLS_DATE_LANGUAGE = '''||SYS_CONTEXT('USERENV', 'LANGUAGE')||'''')
不知道你的问题是什么，对我来说工作正常。
BEGIN
FOR aLang IN (SELECT * FROM V$NLS_VALID_VALUES WHERE parameter = 'LANGUAGE' ORDER BY VALUE) LOOP
BEGIN
DBMS_OUTPUT.PUT_LINE(aLang.VALUE || ' -> ' || TO_CHAR(SYSDATE,'fmDay, DD Mon YYYY', 'NLS_DATE_LANGUAGE='''||aLang.VALUE||'''') );
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(aLang.VALUE || ' -> ' || SQLERRM);
end;
end loop;
end;
结果：
ALBANIAN -> E Premte, 5 Jan 2018
AMERICAN -> Friday, 5 Jan 2018
ARABIC -> الجمعة, 5 يناير 2018
ASSAMESE -> শুক্ৰবাৰ, 5 জানুৱাৰী 2018
AZERBAIJANI -> Cümə, 5 Yan 2018
BANGLA -> শুক্রবার, 5 জানুয়ারী 2018
BELARUSIAN -> Пятніца, 5 Сту 2018
BENGALI -> শুকবার, 5 জানু 2018
BRAZILIAN PORTUGUESE -> Sexta-Feira, 5 Jan 2018
BULGARIAN -> Петък, 5 Ян. 2018
CANADIAN FRENCH -> Vendredi, 5 Janv. 2018
CATALAN -> Divendres, 5 Gen. 2018
CROATIAN -> Petak, 5 Sij 2018
CYRILLIC KAZAKH -> Жұма, 5 Қаң 2018
CYRILLIC SERBIAN -> Петак, 5 Јан 2018
CYRILLIC UZBEK -> Жума, 5 Янв 2018
CZECH -> Pátek, 5 Led 2018
DANISH -> Fredag, 5 Jan 2018
DUTCH -> Vrijdag, 5 Jan 2018
EGYPTIAN -> الجمعة, 5 يناير 2018
ENGLISH -> Friday, 5 Jan 2018
ESTONIAN -> Reede, 5 Jaan 2018
FINNISH -> Perjantai, 5 Tammi 2018
FRENCH -> Vendredi, 5 Janv. 2018
GERMAN -> Freitag, 5 Jan 2018
GERMAN DIN -> Freitag, 5 Jan 2018
GREEK -> Παρασκευή, 5 Ιαν 2018
GUJARATI -> શુક્રવાર, 5 જાન્યુવારી 2018
HEBREW -> יום שישי, 5 ינואר 2018
HINDI -> शुक्रवार, 5 जनवरी 2018
HUNGARIAN -> Péntek, 5 Jan. 2018
ICELANDIC -> Föstudagur, 5 Jan 2018
INDONESIAN -> Jumat, 5 Jan 2018
IRISH -> Dé Haoine, 5 Ean 2018
ITALIAN -> Venerdì, 5 Gen 2018
JAPANESE -> 金曜日, 5 1月 2018
KANNADA -> ಶುಕ್ರವಾರ, 5 ಜನವರಿ 2018
KOREAN -> 금요일, 5 1월 2018
LATIN AMERICAN SPANISH -> Viernes, 5 Ene 2018
LATIN SERBIAN -> Petak, 5 Jan 2018
LATIN UZBEK -> Juma, 5 Yanvar 2018
LATVIAN -> Piektdiena, 5 Jan 2018
LITHUANIAN -> Penktadienis, 5 Sau 2018
MACEDONIAN -> Петок, 5 Јан 2018
MALAY -> Jumaat, 5 Jan 2018
MALAYALAM -> വെള്ളിആഴ്ച, 5 ജനവരി 2018
MARATHI -> शुक्रवार, 5 जानेवारी 2018
MEXICAN SPANISH -> Viernes, 5 Ene 2018
NORWEGIAN -> Fredag, 5 Jan 2018
ORIYA -> ଶୁକ୍ରବାର, 5 ଜାନୁଆରୀ 2018
POLISH -> Piątek, 5 Sty 2018
PORTUGUESE -> Sexta-Feira, 5 Jan 2018
PUNJABI -> ਸ਼ੁੱਕਰਵਾਰ, 5 ਜਨਵਰੀ 2018
ROMANIAN -> Vineri, 5 Ian 2018
RUSSIAN -> Пятница, 5 Янв 2018
SIMPLIFIED CHINESE -> 星期五, 5 1月 2018
SLOVAK -> Piatok, 5 Jan 2018
SLOVENIAN -> Petek, 5 Jan 2018
SPANISH -> Viernes, 5 Ene 2018
SWEDISH -> Fredag, 5 Jan 2018
TAMIL -> வெள்ளி, 5 ஜன. 2018
TELUGU -> శుక్రవారం, 5 ಜనవరి 2018
THAI -> ศุกร์, 5 ม.ค. 2018
TRADITIONAL CHINESE -> 星期五, 5 1月 2018
TURKISH -> Cuma, 5 Oca 2018
UKRAINIAN -> П'Ятниця, 5 Січ 2018
VIETNAMESE -> Thứ Sáu, 5 Tháng MộT 2018
更新：
它也在手动工作(尽管它没用(：
ALTER SESSION SET NLS_LANGUAGE = 'BRAZILIAN PORTUGUESE';
Session altered.
SELECT TO_CHAR(SYSTIMESTAMP ,'Day,DD Mon YYYY', 'NLS_DATE_LANGUAGE = '''||VALUE||'''') date_from
FROM v$nls_parameters
WHERE parameter = 'NLS_LANGUAGE';
DATE_FROM
---------------------------------------------------------------------------
Segunda-Feira,08 Jan 2018
1 row selected.