过程编译没错，调用时报错rep-1401.严重的PL/SQL错误

llm_leon

过程编译没错，调用时报错rep-1401.严重的PL/SQL错误
PROCEDURE INSERT_JIAOYI IS
CURSOR CU_1 IS
SELECT GHV.DEFAULT_EFFECTIVE_DATE DEFAULT_DATE -----栏位：有效时间
, SUBSTR(GHV.BATCH_NAME,1,12) TNAME -----栏位：总帐号码
, GHV.DOC_SEQUENCE_VALUE DOC -----栏位：传票号码
, GHV.DESCRIPTION DTION -----栏位：摘要
, GJV.ACCOUNTED_DR DR -----栏位：借项金额
, GJV.ACCOUNTED_CR CR -----栏位：贷项金额
, GJV.JE_LINE_NUM NUM -----栏位：分录NO
, GJV.CONTEXT3 CONTEXT3 -----栏位：科目
FROM GL_JE_HEADERS_V GHV
, GL_JE_LINES_V GJV
, GL_CODE_COMBINATIONS_V GCV
WHERE GHV.JE_HEADER_ID =GJV.JE_HEADER_ID
AND GJV.STATUS='P' ------条件：过账：STATUS=P
AND GJV.CODE_COMBINATION_ID= GCV.CODE_COMBINATION_ID
AND GCV.SEGMENT1 between SUBSTR(_FLEX_FM,1,3) AND substr(:P_FLEX_TO,1,3)
AND GCV.SEGMENT2 between SUBSTR(:P_FLEX_FM,5,6) AND substr(:P_FLEX_TO,5,6)
AND GCV.SEGMENT3 between SUBSTR(:P_FLEX_FM,12,2) AND substr(:P_FLEX_TO,12,2)
AND GCV.SEGMENT4 between SUBSTR(:P_FLEX_FM,15,5) AND substr(:P_FLEX_TO,15,5)
AND GCV.SEGMENT5 between SUBSTR(:P_FLEX_FM,21,6) AND substr(:P_FLEX_TO,21,6)
-- AND TO_CHAR(GHV.DEFAULT_EFFECTIVE_DATE,'YYYY/MM/DD HH24:MI:SS') between :M_YXDT_B AND :M_YXDT_E
ORDER BY CONTEXT3,DOC;

LR_1 CU_1%ROWTYPE;

BEGIN
DELETE FROM TEMP_LEON;
:M_NO:=1;
OPEN CU_1;
LOOP
FETCH CU_1 INTO LR_1;

EXIT WHEN CU_1%NOTFOUND;

:M_NO := :M_NO +1 ;

INSERT INTO TEMP_LEON
( TNO
,DEFAULT_EFFECTIVE_DATE
,TNAME
,DOC_SEQUENCE_VALUE
,DESCRIPTION
,ACCOUNTED_DR
,ACCOUNTED_CR
,JE_LINE_NUM
,CONTEXT3
)VALUES
(:M_NO
,LR_1.DEFAULT_DATE -----栏位：有效时间
,LR_1.TNAME -----栏位：总帐号码
,LR_1.DOC -----栏位：传票号码
,LR_1.DTION -----栏位：摘要
,LR_1.DR -----栏位：借项金额
,LR_1.CR -----栏位：贷项金额
,LR_1.NUM -----栏位：分录NO
,LR_1.CONTEXT3 -----栏位：科目
);
END LOOP;
commit;
CLOSE CU_1;
END;

通过PL/SQL测试过，CU_1里有数据，将其中一比数据INSERT到表里是可以的，可是在REPORT里就报错。
在BEFORE_REPORT里调用包错REP-1401有严重的PL/SQL错误。
function BeforeReport return boolean is

BEGIN
SELECT NVL(TO_CHAR(START_DATE,'YYYY/MM/DD HH24:MI:SS'),TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'))
,NVL(TO_CHAR(END_DATE,'YYYY/MM/DD HH24:MI:SS'),TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS'))
INTO :M_YXDT_B,:M_YXDT_E
FROM GL_PERIODS WHERE PERIOD_NAME=:P_KJQJ AND ROWNUM <2;

INSERT_JIAOYI();
--INSERT_YUE();

--COMPUTE_DATA();

RETURN (TRUE);
end;

请高手帮忙！！！

纵横四海

是不是和你用了：
:M_NO
_FLEX_FM

有关啊。看不出有什么问题啊。
在Report中可以用insert语句么？？？？

llm_leon

不能用INSERT吗？可是我用SRW.DO_SQL('INSERT ...');
还是报同样的错误啊！参数应该没问题，我把他该为定值测试还是同样啊！！
真是急啊！！！！

纵横四海

原帖由 llm_leon 于 2006-6-26 01:11 PM 发表
不能用INSERT吗？可是我用SRW.DO_SQL('INSERT ...');
还是报同样的错误啊！参数应该没问题，我把他该为定值测试还是同样啊！！
真是急啊！！！！

建议改为定值测试。

纵横四海

After a brief search on Metalink (no reports installation here), this error could be caused by one of several conditions - depending on the version of reports.

Apparently, under certain circumstances, it can be caused by a divide by zero, it could also be caused by a value that is too long for a column, and then I came across this note which may or may not be relevant (sorry about the formatting, its a paste)...,
---
Doc ID: Note:1042188.6
Subject: REP-01401 WHEN RUNNING A 2.5 REPORT WITH FORMULA COLUMN
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 04-FEB-1998
Last Revision Date: 18-MAR-1998



PROBLEM DESCRIPTION:
====================

Running a V2.5 report with a formula column causes REP-01401.
REP-01401 - '': Unhandled PL/SQL exception.

For example, the formula column code is:

Begin

If :JOB = 'MANAGER'
then :CP_1 := 'MANAGEMENT' (PLACEHOLDER COLUMN TO DISPLAY STRING)
else :CP_1 := 'OTHER'
END IF;
END;


Search words: REP-1401

Solution: CHANGE THE TRIGGER FOR THE FORMULA COLUMN AS FOLLOWS


SOLUTION DESCRIPTION:
=====================

This is a known issue (Bug 473584). Reports 2.5 uses PL/SQL Version 1 and
Reports 3.0 uses PL/SQL Version 2. In PL/SQL Version 2, a value must be
returned. You need to change the above mentioned example to:

Begin
If :JOB = 'MANAGER' then
return ('MANAGEMENT'); (MANAGEMENT WILL BE RETURNED FOR CF_1)
else
return ('OTHER'); (OTHER WILL BE RETURNED FOR CF_1)
end if;
end;

This will resolve the error and allow the report to be run.
.

---

HTH

David.

llm_leon

原帖由 纵横四海 于 2006-6-26 12:30 PM 发表
是不是和你用了：
:M_NO
_FLEX_FM

有关啊。看不出有什么问题啊。
在Report中可以用insert语句么？？？？

问题解决了，是可以INSERT/UPDATE/DELETE的，
把ORDER BY CONTEXT3,DOC;
改为ORDER BY GJV.CONTEXT3 ,GHV.DOC_SEQUENCE_VALUE ;不过还是有疑问为什么有别名不行呢？
还要请高手指点。
大家有兴趣的话，我可以把原程序发上来。