大家好,我写了以下存储过程,用水晶报表加载的时候会有"ORA-00904 invalid identifier"这样的错误提示,我去掉"SELECT_YOUR_LANGUAGE IN CHAR"这个参数的时候就不会有错误,我主要是想定义一个选择语言的参数,在水晶报表中把这个参数设成静态类型并赋予多个语种值。IN类型的参数是可以用来传递数值进入存储过程的,我不知道我定义这个参数的时候哪里不对。
多谢指点了!
CREATE OR REPLACE PROCEDURE Filling_Ger_Expurge (
SELECT_YOUR_LANGUAGE IN CHAR,--把这个参数去掉就不会报错
SELECT_OPENED_DATE_START IN DATE,
SELECT_OPENED_DATE_END IN DATE,
O_REFCURSOR OUT Pkg_Report.RCURSOR)IS
tmpVar varchar2(4000);BEGIN
tmpVar :='SELECT * FROM
(
SELECT
........................
FROM
......................
WHERE
h.LANG = SELECT_YOUR_LANGUAGE66--把这个参数去掉就不会报错
AND g.LANG = SELECT_YOUR_LANGUAGE--把这个参数去掉就不会报错
AND vw_svx_qtyunitlang.LANG = SELECT_YOUR_LANGUAGE--把这个参数去掉就不会报错
';
tmpVar := tmpVar || ' AND trunc(a.OPENED_DATE, ''DDD'') >= (to_date(''' || SELECT_OPENED_DATE_START || '''))';
tmpVar := tmpVar || ' AND trunc(a.OPENED_DATE, ''DDD'') <= (to_date(''' || SELECT_OPENED_DATE_END || '''))';
tmpVar := tmpVar || '
GROUP BY
f.OWNERORG_FK,f.bizid,f.caption_alt,trunc(a.OPENED_DATE, ''DDD''),c.QTY,j.BIZID,
j.CAPTION_alt, h.CAPTION, vw_svx_qtyunitlang.caption,
IBS_ORG.ORG_PK,IBS_ORG.bizid, IBS_ORG.CAPTION
UNION SELECT
................................ FROM
..............................
WHERE h.LANG = SELECT_YOUR_LANGUAGE--把这个参数去掉就不会报错
AND i.LANG = SELECT_YOUR_LANGUAGE--把这个参数去掉就不会报错
AND vw_svx_qtyunitlang.LANG = SELECT_YOUR_LANGUAGE--把这个参数去掉就不会报错
';
tmpVar := tmpVar || ' AND trunc(a.OPENED_DATE, ''DDD'') >= (to_date(''' || SELECT_OPENED_DATE_START || '''))';
tmpVar := tmpVar || ' AND trunc(a.OPENED_DATE, ''DDD'') <= (to_date(''' || SELECT_OPENED_DATE_END || '''))';
tmpVar := tmpVar || '
GROUP BY
..................................
)
ORDER BY 4, 6 asc, 10';OPEN O_REFCURSOR FOR tmpVar;
END Filling_Ger_Expurge;/
多谢指点了!
CREATE OR REPLACE PROCEDURE Filling_Ger_Expurge (
SELECT_YOUR_LANGUAGE IN CHAR,--把这个参数去掉就不会报错
SELECT_OPENED_DATE_START IN DATE,
SELECT_OPENED_DATE_END IN DATE,
O_REFCURSOR OUT Pkg_Report.RCURSOR)IS
tmpVar varchar2(4000);BEGIN
tmpVar :='SELECT * FROM
(
SELECT
........................
FROM
......................
WHERE
h.LANG = SELECT_YOUR_LANGUAGE66--把这个参数去掉就不会报错
AND g.LANG = SELECT_YOUR_LANGUAGE--把这个参数去掉就不会报错
AND vw_svx_qtyunitlang.LANG = SELECT_YOUR_LANGUAGE--把这个参数去掉就不会报错
';
tmpVar := tmpVar || ' AND trunc(a.OPENED_DATE, ''DDD'') >= (to_date(''' || SELECT_OPENED_DATE_START || '''))';
tmpVar := tmpVar || ' AND trunc(a.OPENED_DATE, ''DDD'') <= (to_date(''' || SELECT_OPENED_DATE_END || '''))';
tmpVar := tmpVar || '
GROUP BY
f.OWNERORG_FK,f.bizid,f.caption_alt,trunc(a.OPENED_DATE, ''DDD''),c.QTY,j.BIZID,
j.CAPTION_alt, h.CAPTION, vw_svx_qtyunitlang.caption,
IBS_ORG.ORG_PK,IBS_ORG.bizid, IBS_ORG.CAPTION
UNION SELECT
................................ FROM
..............................
WHERE h.LANG = SELECT_YOUR_LANGUAGE--把这个参数去掉就不会报错
AND i.LANG = SELECT_YOUR_LANGUAGE--把这个参数去掉就不会报错
AND vw_svx_qtyunitlang.LANG = SELECT_YOUR_LANGUAGE--把这个参数去掉就不会报错
';
tmpVar := tmpVar || ' AND trunc(a.OPENED_DATE, ''DDD'') >= (to_date(''' || SELECT_OPENED_DATE_START || '''))';
tmpVar := tmpVar || ' AND trunc(a.OPENED_DATE, ''DDD'') <= (to_date(''' || SELECT_OPENED_DATE_END || '''))';
tmpVar := tmpVar || '
GROUP BY
..................................
)
ORDER BY 4, 6 asc, 10';OPEN O_REFCURSOR FOR tmpVar;
END Filling_Ger_Expurge;/
h.LANG
g.LANG
vw_svx_qtyunitlang.LANG
你的程序传入的是字符串,一般双引号表示字符串,单引号表是字符。