Home » Developer & Programmer » Reports & Discoverer » REP-1401: and ORA-06503: Error (Report 6i,database version 8i)
REP-1401: and ORA-06503: Error [message #394752] Mon, 30 March 2009 02:38 Go to next message
mahantesh
Messages: 28
Registered: March 2008
Location: Mumbai ,India
Junior Member
Hi,
In my report there is a function CF_Master_accounting_line to which i pass :CCID as parameter based on which it return some value,for some CCID it returns nothing and goes in to no data found exception, to avoid this i used NVL in select statment, but still it giving error as
REP-1401: 'cf_master_accounting_lineformu': Fatal PL/SQL error occurred.
ORA-06503: PL/SQL: Function returned without value


My function look like

function CF_Master_accounting_lineFormu return varchar2 is
master_accounting_line varchar2(70);
begin
SELECT
distinct nvl((glcc.segment1||'-'||glcc.segment2||'-'||glcc.segment6),'Invalid GL')
INTO master_accounting_line
FROM HZ_CUST_SITE_USES_all hcsu,
-- HZ_CUST_ACCT_SITES_ALL hcas,
-- hz_cust_accounts_all hca,
-- hz_parties hp,
gl_code_combinations glcc
WHERE hcsu.GL_ID_REC=glcc.CODE_COMBINATION_ID
--AND hcsu.CUST_ACCT_SITE_ID=hcas.CUST_ACCT_SITE_ID
--AND hcas.CUST_ACCOUNT_ID=hca.CUST_ACCOUNT_ID
--AND hca.PARTY_ID=hp.PARTY_ID
--AND hp.PARTY_ID=:PARTY_ID
AND glcc.CODE_COMBINATION_ID=:CCID ;

--srw.message(100001,'Party_id'||:PARTY_ID);
srw.message(100002,'Ccid'||:CCID);
srw.message(100005,'Document Number'||:document_number);
srw.message(100006,'GL Date'||:GL_DATE);
srw.message(100006,'Master Accounting Line'||master_accounting_line);

return master_accounting_line;

exception
when no_data_found then
srw.message(100003,'No data for this GL');
when others then
srw.message(100004,'Invalid GL');
end;

can any one help me ?
Re: REP-1401: and ORA-06503: Error [message #394759 is a reply to message #394752] Mon, 30 March 2009 03:49 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
nvl is not a way to get around no_data_found.

nvl fires if the select statement finds a where the columns you're looking at are null.
You get no_data_found if the select statement doesn't find a row.

Why not put a return statement in the no_data_found handler?
Previous Topic: Passing value across worksheets is possible ?
Next Topic: Images in Reports - Error
Goto Forum:
  


Current Time: Thu Dec 08 10:03:07 CST 2016

Total time taken to generate the page: 0.25702 seconds