Quite suprised at this error i am facing right now ..A no data found exception is raised
even when the record is present
Seems something i understand is wrong ..
create or replace FUNCTION "MBID" (MCNAME varchar2 ,MBRANCH VARCHAR2) return number is
mcid number(10);
begin
begin
select TO_NUMBER(company_id) into mcid from insurance_company where UPPER(ltrim(rtrim(NAME))) = UPPER(LTRIM(RTRIM(MCNAME))) AND
UPPER(MBRANCH_CODE) = UPPER(LTRIM(RTRIM(MBRANCH)));
exception
when no_data_Found then
raise_application_error(-20111,MCNAME||'<COMPANMAENOTFOUNDBRCODE>'||MBRANCH);
end;
return mcid ;
end;
The table on which this is fired
1* select company,brcode from prod_300 where company = 'ICICI'
SQL> /
COMPANY BRCODE
-------------------- ----------
ICICI PB
ICICI PB
ICICI PB
select name,mbranch_code,company_id from insurance_Company where name ='ICICI';
NAME MBRANCH_CO COMPANY_ID
-------------------- ---------- ----------
ICICI BP 275
ICICI PB 276
ICICI 8
But when i fire the query i get the no data found exception is triggered even though the record is clearly present ..
SQL> START S
select MBID(COMPANY,BRCODE),company,brcode from prod_300 where company = 'ICICI'
*
ERROR at line 1:
ORA-20111: ICICI<COMPANMAENOTFOUNDBRCODE>PB
ORA-06512: at "INS100.MBID", line 9
How can this exception be fired can some one guide what am i doing wrong
SQL> L