autonomous trans with EXCEPTION fails - why?

[pilnyj]

My quest is to create a function that returns a string value from a lookup table given the ID.
If no match occurs, then I want to load that value into the lookup table.
So here's my lookup table...

 
SQL> select * from bios_lookup;
 
   BIOS_ID BIOS
---------- ----------------------------------------------------------------
         1 Phoenix - AwardBIOS v6.00PG
         2 Phoenix ROM BIOS PLUS Version 1.10 A14
         3 Phoenix ROM BIOS PLUS Version 1.10 A20
         4 Phoenix ROM BIOS PLUS Version 1.10 A19
         5 Phoenix ROM BIOS PLUS Version 1.10 A08
         6 Phoenix ROM BIOS PLUS Version 1.10 A11
         7 Phoenix ROM BIOS PLUS Version 1.10 A17
         8 Phoenix NoteBIOS 4.0 Release 6.0
         9 Default System BIOS
        10 786B2 v1.11
        11 Phoenix ROM BIOS PLUS Version 1.10 A02
        12 Phoenix ROM BIOS PLUS Version 1.10 A05
        13 SE7520JR23 Production BIOS Version 07.20, Build 0074
        14 Phoenix ROM BIOS PLUS Version 1.10 A09
        15 Phoenix ROM BIOS PLUS Version 1.10 A06
        16 Phoenix ROM BIOS PLUS Version 1.10 A04
        17 &Phoenix NoteBIOS 4.0 Release 6.0
        18 PhoenixBIOS 4.0 Release 6.0
        19 )Phoenix - Award WorkstationBIOS v6.00PG
        20 Phoenix ROM BIOS PLUS Version 1.10 A03
        21 Phoenix FirstBIOS(tm) Notebook Pro Version 2.0 for IBM ThinkPad
        22 Phoenix ROM BIOS PLUS Version 1.10 A18
        23 OCPRF100- PhoenixBIOS 4.0 Release 6.0
 
23 rows selected.
 
--And here's my function...
SQL> create or replace function bios_no(bios_in in varchar2) return number is
  2  pragma autonomous_transaction;
  3  bnum number;
  4  begin
  5  select bios_id into bnum from bios_lookup where bios_in = bios;
  6   EXCEPTION
  7       WHEN OTHERS THEN
  8       begin
  9          select bios_seq.nextval into bnum from dual;
 10          insert into bios_lookup (bios_id,bios) values (bnum,bios_in);
 11          commit;
 12       end;
 13  return bnum;
 14  end;
 15  /
 
Function created.
 
SQL> select bios_no('test') from dual;
 
BIOS_NO('TEST')
---------------
             43
 
--looks good.....             
SQL> select * from bios_lookup;
 
   BIOS_ID BIOS
---------- ----------------------------------------------------------------
         1 Phoenix - AwardBIOS v6.00PG
         2 Phoenix ROM BIOS PLUS Version 1.10 A14
         3 Phoenix ROM BIOS PLUS Version 1.10 A20
         4 Phoenix ROM BIOS PLUS Version 1.10 A19
         5 Phoenix ROM BIOS PLUS Version 1.10 A08
         6 Phoenix ROM BIOS PLUS Version 1.10 A11
         7 Phoenix ROM BIOS PLUS Version 1.10 A17
         8 Phoenix NoteBIOS 4.0 Release 6.0
         9 Default System BIOS
        10 786B2 v1.11
        11 Phoenix ROM BIOS PLUS Version 1.10 A02
        12 Phoenix ROM BIOS PLUS Version 1.10 A05
        13 SE7520JR23 Production BIOS Version 07.20, Build 0074
        14 Phoenix ROM BIOS PLUS Version 1.10 A09
        15 Phoenix ROM BIOS PLUS Version 1.10 A06
        16 Phoenix ROM BIOS PLUS Version 1.10 A04
        17 &Phoenix NoteBIOS 4.0 Release 6.0
        18 PhoenixBIOS 4.0 Release 6.0
        19 )Phoenix - Award WorkstationBIOS v6.00PG
        20 Phoenix ROM BIOS PLUS Version 1.10 A03
        21 Phoenix FirstBIOS(tm) Notebook Pro Version 2.0 for IBM ThinkPad
        22 Phoenix ROM BIOS PLUS Version 1.10 A18
        23 OCPRF100- PhoenixBIOS 4.0 Release 6.0
        43 test
 
24 rows selected.
 
--YES - it works, now create a view with the function embedded...
 
create or replace view sms_host as
  SELECT csd."MachineID", csd."Name0", csd."Model0", csd."Domain0",
         osd."Caption0",osd."Version0",
   bios_no(pbd."SoftwareElementID00") as bios,
         sia."IP_Addresses0",
         sd."SystemRole0",
         sdi."User_Name0"
  FROM  TEMP_CSD csd, TEMP_OSD osd, TEMP_PBD pbd, TEMP_SIA sia, TEMP_SD sd, TEMP_SDI sdi   
  WHERE csd."MachineID" = osd."MachineID"
    AND osd."MachineID" = pbd."MachineID"  
    AND pbd."MachineID" = sia."ItemKey" 
    AND sia."ItemKey" = sd."MachineID"
    AND sd."MachineID" = sdi."ItemKey";
 
SQL> select * from sms_host where rownum < 2;
select * from sms_host where rownum < 2
                                      *
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "NETTEST.BIOS_NO", line 14
 

Why doesn't this work?????

(I resolved the issue below, but still am curious why the original failed)

 
 create or replace function bios_no(bios_in in varchar2) return number is
    pragma autonomous_transaction;
    bnum number;
    begin
    select nvl(bios_id,0) into bnum from bios_lookup where bios_in = bios;
    if bnum = 0 then
         begin
            select bios_seq.nextval into bnum from dual;
            insert into bios_lookup (bios_id,bios) values (bnum,bios_in);
           commit;
        end;
   end if;
   return bnum;
   end;
   /
 

[Dave44]

Your function only returns a value if it gets into the exception block. when it finds the passed in value in the lookup table nothing is returned, thats why your getting the no value returned error i believe.
try testing the function with a value that exists and see what happens.

[amitpatel66]

Yes Dave is right. You have used RETURN statement in the EXCEPTION block. You need to use the return statement before your exception block,ie in the main begin block itself.

[Saii]

In addition to above solution, I just want to point out that try using no_data_found exception instead of generic when others exception to populate a new value, unless you want to populate new row on every exception thrown by the code.