function Period1Formula return VARCHAR2 is
beginDECLARE
l_period_name VARCHAR2(15);
l_period_POD DATE;
l_period_PCD DATE;
l_period_closed VARCHAR2(4);
l_period_PC NUMBER(15);
l_period_FY NUMBER(15);
BEGIN
IF upper(:p_mrcsobtype) = 'R'
THEN
BEGIN
SELECT period_name,
period_counter,
period_open_date,
nvl(period_close_date, sysdate),
decode(period_close_date, null, 'NO', 'YES'),
fiscal_year
INTO l_period_name,
l_period_PC,
l_period_POD,
l_period_PCD,
l_period_closed,
l_period_FY
FROM FA_DEPRN_PERIODS_MRC_V
WHERE book_type_code = :P_FROM_BOOK
AND period_name = :P_PERIOD;
exception
when NO_DATA_FOUND then
begin
l_period_name:=NULL;
l_period_PC:=NULL;
l_period_POD:=NULL;
l_period_PCD:=NULL;
l_period_closed:=NULL;
l_period_FY:=NULL;
end;
end;
ELSE
BEGIN
SELECT period_name,
period_counter,
period_open_date,
nvl(period_close_date, sysdate),
decode(period_close_date, null, 'NO', 'YES'),
fiscal_year
INTO l_period_name,
l_period_PC,
l_period_POD,
l_period_PCD,
l_period_closed,
l_period_FY
FROM FA_DEPRN_PERIODS
WHERE book_type_code = :P_FROM_BOOK
AND period_name = :P_PERIOD;
exception
when NO_DATA_FOUND then
begin
l_period_name:=NULL;
l_period_PC:=NULL;
l_period_POD:=NULL;
l_period_PCD:=NULL;
l_period_closed:=NULL;
l_period_FY:=NULL;
end;
END;
END IF;
:Period1_PC := l_period_PC;
:Period1_POD := l_period_POD;
:Period1_PCD := l_period_PCD;
:Period_Closed := l_period_closed;
:Period1_FY := l_period_FY;
return(l_period_name);
END;
RETURN NULL; end;这是我报表中一自定义公式。但运行报表时从view LOG里看到这样的错误
REP-1401: 'period1formula': Fatal PL/SQL error occurred.
ORA-01403: no data found
我传的参数确定是没有找到数据,但没找到数据时给赋予空值了
beginDECLARE
l_period_name VARCHAR2(15);
l_period_POD DATE;
l_period_PCD DATE;
l_period_closed VARCHAR2(4);
l_period_PC NUMBER(15);
l_period_FY NUMBER(15);
BEGIN
IF upper(:p_mrcsobtype) = 'R'
THEN
BEGIN
SELECT period_name,
period_counter,
period_open_date,
nvl(period_close_date, sysdate),
decode(period_close_date, null, 'NO', 'YES'),
fiscal_year
INTO l_period_name,
l_period_PC,
l_period_POD,
l_period_PCD,
l_period_closed,
l_period_FY
FROM FA_DEPRN_PERIODS_MRC_V
WHERE book_type_code = :P_FROM_BOOK
AND period_name = :P_PERIOD;
exception
when NO_DATA_FOUND then
begin
l_period_name:=NULL;
l_period_PC:=NULL;
l_period_POD:=NULL;
l_period_PCD:=NULL;
l_period_closed:=NULL;
l_period_FY:=NULL;
end;
end;
ELSE
BEGIN
SELECT period_name,
period_counter,
period_open_date,
nvl(period_close_date, sysdate),
decode(period_close_date, null, 'NO', 'YES'),
fiscal_year
INTO l_period_name,
l_period_PC,
l_period_POD,
l_period_PCD,
l_period_closed,
l_period_FY
FROM FA_DEPRN_PERIODS
WHERE book_type_code = :P_FROM_BOOK
AND period_name = :P_PERIOD;
exception
when NO_DATA_FOUND then
begin
l_period_name:=NULL;
l_period_PC:=NULL;
l_period_POD:=NULL;
l_period_PCD:=NULL;
l_period_closed:=NULL;
l_period_FY:=NULL;
end;
END;
END IF;
:Period1_PC := l_period_PC;
:Period1_POD := l_period_POD;
:Period1_PCD := l_period_PCD;
:Period_Closed := l_period_closed;
:Period1_FY := l_period_FY;
return(l_period_name);
END;
RETURN NULL; end;这是我报表中一自定义公式。但运行报表时从view LOG里看到这样的错误
REP-1401: 'period1formula': Fatal PL/SQL error occurred.
ORA-01403: no data found
我传的参数确定是没有找到数据,但没找到数据时给赋予空值了
when NO_DATA_FOUND then
return NULL;