PL/SQL try-catch-finally concept

Posted on október 9, 2009. Filed under: Uncategorized | Značky:, |

Taky maly pokus o koncept implementacie try-catch-finally v PL/SQL.

  • 1. priblizenie
  • + zachovava “strukturu”
  • – rozbija error_stack a chybovu hlasku

<<try_catch_finally_block>>
declare
  --some handled exception
  e exception;
  pragma exception_init(e, -20666);
  
  --some unhandled user defined exception
  e_some_user_defined exception; 
  pragma exception_init(e_some_user_defined, -20000);
  
  --some ora exception
  e_some_ora exception; 
  pragma exception_init(e_some_ora, -1);
  
  l_sqlcode pls_integer;
  l_sqlerrm varchar2(500);
begin

  <<try_block>>
  begin
    --try
    dbms_output.put_line('try');
    
    --dbms_output.put_line('handled exception raised');
    --raise e;    
    
    --dbms_output.put_line('some unhandled user defined exception raised');
    --raise e_some_user_defined;

    --dbms_output.put_line('some unhandled ora exception raised');    
    --raise e_some_ora;
    
  exception   
    when e then  
       --catch
      dbms_output.put_line('catch');
  
    when others then  
      --store unhandled exception
      l_sqlcode := sqlcode;
      l_sqlerrm := sqlerrm;      
      dbms_output.put_line('store unhandled exception');
      
  end try_block;  
  
  --finally
  dbms_output.put_line('finally');  
  
  --reraise unhandled  exception
  if (l_sqlcode is not null) then
    dbms_output.put_line('reraise unhandled exception');
    
    --if user defined exception
    if l_sqlcode between -20999 and -20000 then          
      --reraise user defined error
      raise_application_error(l_sqlcode, l_sqlerrm);
    else
      --reraise ora error
      <<reraise_ora_error>>
      declare        
      begin
        execute immediate '
          declare
            e exception;
            pragma exception_init(e, '||l_sqlcode||');
          begin
            raise e;
          end;
        ';
      end reraise_ora_error;      
    end if;
    
  end if;
  
end try_catch_finally_block;
/

  • 2. priblizenie
  • – nezachovava “strukturu”
  • + zachovava error_stack a chybovu hlasku
  • + cistejsi & prehladnejsi kod

<<try_catch_finally_block>>
declare
  --some handled exception
  e exception;
  pragma exception_init(e, -20666);
  
  --some unhandled user defined exception
  e_some_user_defined exception; 
  pragma exception_init(e_some_user_defined, -20000);
  
  --some ora exception
  e_some_ora exception; 
  pragma exception_init(e_some_ora, -1);
  
  --local procedure where finalization is implemented
  procedure finally is
  begin
    --finally
    dbms_output.put_line('finally');    
  end finally;
  
begin

  <<try_block>>
  begin
    --try
    dbms_output.put_line('try');
    
    --dbms_output.put_line('handled exception raised');
    --raise e;    
    
    --dbms_output.put_line('some unhandled user defined exception raised');
    --raise e_some_user_defined;

    --dbms_output.put_line('some unhandled ora exception raised');    
    --raise e_some_ora;
    
  exception   
    when e then  
       --catch
      dbms_output.put_line('catch');
  
    when others then  
      --finally
      finally;
      
      --raise unhandled
      raise;
      
  end try_block;  
  
  --finally
  finally;
  
end try_catch_finally_block;
/

Reklamy
Read Full Post | Make a Comment ( 1 so far )

user_views_definition

Posted on jún 4, 2009. Filed under: Uncategorized | Značky:, , |

Bud mi nieco uniklo, alebo ma Oracle este aj v 11g ulozene definicie view iba v long stlpcoch, ktore su uprimne na …, neda sa v nich hladat, rozumne pristupovat jedine z pl/sql, achjo. Tak si pre potesenie z moci sql vyrabam tabulku do ktorej si mergujem definicie pre take to domace dotazovanie … 😉

drop table user_views_definition;

create table user_views_definition (
  view_name       varchar2(30) not null,
  view_definition clob,
  constraint  user_views_definition_pk
  primary key (view_name)
  using index
)
;

DECLARE
   c CLOB;
BEGIN
   FOR ii IN (SELECT * FROM user_views)
   LOOP
      c := ii.text;
      
      MERGE INTO user_views_definition t
      USING (SELECT ii.view_name AS view_name, c AS view_definition FROM dual) s
      ON (t.view_name = s.view_name)
      WHEN MATCHED THEN UPDATE SET t.view_definition = s.view_definition 
      WHEN NOT MATCHED THEN INSERT (t.view_name, t.view_definition) VALUES (s.view_name, s.view_definition);
      
   END LOOP;
   COMMIT;
END;
/

Read Full Post | Make a Comment ( None so far )

Liked it here?
Why not try sites on the blogroll...