Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > oracle > answers

autonomous trans with EXCEPTION fails - why?

Newbie
  
Join Date: Mar 2008
Posts: 1
#1: Mar 28 '08
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...
Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> select * from bios_lookup;
  3.  
  4.    BIOS_ID BIOS
  5. ---------- ----------------------------------------------------------------
  6.          1 Phoenix - AwardBIOS v6.00PG
  7.          2 Phoenix ROM BIOS PLUS Version 1.10 A14
  8.          3 Phoenix ROM BIOS PLUS Version 1.10 A20
  9.          4 Phoenix ROM BIOS PLUS Version 1.10 A19
  10.          5 Phoenix ROM BIOS PLUS Version 1.10 A08
  11.          6 Phoenix ROM BIOS PLUS Version 1.10 A11
  12.          7 Phoenix ROM BIOS PLUS Version 1.10 A17
  13.          8 Phoenix NoteBIOS 4.0 Release 6.0
  14.          9 Default System BIOS
  15.         10 786B2 v1.11
  16.         11 Phoenix ROM BIOS PLUS Version 1.10 A02
  17.         12 Phoenix ROM BIOS PLUS Version 1.10 A05
  18.         13 SE7520JR23 Production BIOS Version 07.20, Build 0074
  19.         14 Phoenix ROM BIOS PLUS Version 1.10 A09
  20.         15 Phoenix ROM BIOS PLUS Version 1.10 A06
  21.         16 Phoenix ROM BIOS PLUS Version 1.10 A04
  22.         17 &Phoenix NoteBIOS 4.0 Release 6.0
  23.         18 PhoenixBIOS 4.0 Release 6.0
  24.         19 )Phoenix - Award WorkstationBIOS v6.00PG
  25.         20 Phoenix ROM BIOS PLUS Version 1.10 A03
  26.         21 Phoenix FirstBIOS(tm) Notebook Pro Version 2.0 for IBM ThinkPad
  27.         22 Phoenix ROM BIOS PLUS Version 1.10 A18
  28.         23 OCPRF100- PhoenixBIOS 4.0 Release 6.0
  29.  
  30. 23 rows selected.
  31.  
  32. --And here's my function...
  33. SQL> create or replace function bios_no(bios_in in varchar2) return number is
  34.   2  pragma autonomous_transaction;
  35.   3  bnum number;
  36.   4  begin
  37.   5  select bios_id into bnum from bios_lookup where bios_in = bios;
  38.   6   EXCEPTION
  39.   7       WHEN OTHERS THEN
  40.   8       begin
  41.   9          select bios_seq.nextval into bnum from dual;
  42.  10          insert into bios_lookup (bios_id,bios) values (bnum,bios_in);
  43.  11          commit;
  44.  12       end;
  45.  13  return bnum;
  46.  14  end;
  47.  15  /
  48.  
  49. Function created.
  50.  
  51. SQL> select bios_no('test') from dual;
  52.  
  53. BIOS_NO('TEST')
  54. ---------------
  55.              43
  56.  
  57. --looks good.....             
  58. SQL> select * from bios_lookup;
  59.  
  60.    BIOS_ID BIOS
  61. ---------- ----------------------------------------------------------------
  62.          1 Phoenix - AwardBIOS v6.00PG
  63.          2 Phoenix ROM BIOS PLUS Version 1.10 A14
  64.          3 Phoenix ROM BIOS PLUS Version 1.10 A20
  65.          4 Phoenix ROM BIOS PLUS Version 1.10 A19
  66.          5 Phoenix ROM BIOS PLUS Version 1.10 A08
  67.          6 Phoenix ROM BIOS PLUS Version 1.10 A11
  68.          7 Phoenix ROM BIOS PLUS Version 1.10 A17
  69.          8 Phoenix NoteBIOS 4.0 Release 6.0
  70.          9 Default System BIOS
  71.         10 786B2 v1.11
  72.         11 Phoenix ROM BIOS PLUS Version 1.10 A02
  73.         12 Phoenix ROM BIOS PLUS Version 1.10 A05
  74.         13 SE7520JR23 Production BIOS Version 07.20, Build 0074
  75.         14 Phoenix ROM BIOS PLUS Version 1.10 A09
  76.         15 Phoenix ROM BIOS PLUS Version 1.10 A06
  77.         16 Phoenix ROM BIOS PLUS Version 1.10 A04
  78.         17 &Phoenix NoteBIOS 4.0 Release 6.0
  79.         18 PhoenixBIOS 4.0 Release 6.0
  80.         19 )Phoenix - Award WorkstationBIOS v6.00PG
  81.         20 Phoenix ROM BIOS PLUS Version 1.10 A03
  82.         21 Phoenix FirstBIOS(tm) Notebook Pro Version 2.0 for IBM ThinkPad
  83.         22 Phoenix ROM BIOS PLUS Version 1.10 A18
  84.         23 OCPRF100- PhoenixBIOS 4.0 Release 6.0
  85.         43 test
  86.  
  87. 24 rows selected.
  88.  
  89. --YES - it works, now create a view with the function embedded...
  90.  
  91. create or replace view sms_host as
  92.   SELECT csd."MachineID", csd."Name0", csd."Model0", csd."Domain0",
  93.          osd."Caption0",osd."Version0",
  94.    bios_no(pbd."SoftwareElementID00") as bios,
  95.          sia."IP_Addresses0",
  96.          sd."SystemRole0",
  97.          sdi."User_Name0"
  98.   FROM  TEMP_CSD csd, TEMP_OSD osd, TEMP_PBD pbd, TEMP_SIA sia, TEMP_SD sd, TEMP_SDI sdi   
  99.   WHERE csd."MachineID" = osd."MachineID"
  100.     AND osd."MachineID" = pbd."MachineID"  
  101.     AND pbd."MachineID" = sia."ItemKey" 
  102.     AND sia."ItemKey" = sd."MachineID"
  103.     AND sd."MachineID" = sdi."ItemKey";
  104.  
  105. SQL> select * from sms_host where rownum < 2;
  106. select * from sms_host where rownum < 2
  107.                                       *
  108. ERROR at line 1:
  109. ORA-06503: PL/SQL: Function returned without value
  110. ORA-06512: at "NETTEST.BIOS_NO", line 14
  111.  

Why doesn't this work?????

(I resolved the issue below, but still am curious why the original failed)
Expand|Select|Wrap|Line Numbers
  1.  
  2.  create or replace function bios_no(bios_in in varchar2) return number is
  3.     pragma autonomous_transaction;
  4.     bnum number;
  5.     begin
  6.     select nvl(bios_id,0) into bnum from bios_lookup where bios_in = bios;
  7.     if bnum = 0 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.    end if;
  14.    return bnum;
  15.    end;
  16.    /
  17.  

Familiar Sight
  
Join Date: Feb 2007
Location: Calgary AB Canada
Posts: 153
#2: Mar 31 '08

re: autonomous trans with EXCEPTION fails - why?

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's Avatar
Moderator
  
Join Date: Mar 2007
Location: Hyderabad, India
Posts: 2,192
#3: Mar 31 '08

re: autonomous trans with EXCEPTION fails - why?

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's Avatar
Expert
  
Join Date: Apr 2007
Posts: 141
#4: Mar 31 '08

re: autonomous trans with EXCEPTION fails - why?

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.
Reply

« previous question | next question »

Similar Oracle Database bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,510 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved