| REP-1401: and ORA-06503: Error [message #394752] |
Mon, 30 March 2009 02:38  |
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  |
cookiemonster
Messages: 13920
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
