Adding an exception (calling function) into procedure - SQL Oracle

Question

Having some problems with adding function calling exception in the procedure. I hope it's just some syntax misstake. The exception unauthorised is checking if the correct owner of the account deposits money in to the account. To handle that I call function get_authorisation. The code works itself without the exception. Could someone check it? Thank you!

 create or replace procedure do_utt(
     p_radnr in utt.radnr%type,
     p_pnr in utt.pnr%type,
     p_knr in utt.knr%type,
     p_belopp in utt.belopp%type,
     p_datum in utt.datum%type)

     declare unauthorised exception;
     as
     begin
     insert into utt(radnr, pnr, knr, belopp, datum)
     values (radnr_seq.nextval, p_pnr, p_knr, 
             p_belopp, sysdate);

        if get_authorisation(p_knr) = 0 then
        raise unauthorised ;
        end if;

     commit;
     dbms_output.put_line('Balance '|| p_knr || ' is: 
                          '||get_saldo(p_knr));
     exception
     when unauthorised then
     dbms_output.put_line('You are not the owner of the account!');
  end;
  /

Errors: PROCEDURE DO_UTT Line/Col: 8/2 PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:

; is with default authid as cluster order using external deterministic parallel_enable pipelined result_cache accessible rewrite

The code for function:

  create or replace function get_authorisation (
    p_pnr in owner.pnr%type,
    p_knr in owner.knr%type)
    return number
    as
    v_authorised number;
    v_no_authorised exception;
    begin
    select count(*)      
    into v_authorised
    from owner
    where pnr = p_pnr 
    and knr = p_knr;
    return v_authorised;

    exception
    when v_no_authorised then
    return 0;
  end;

move "as" up before the delare. – OldProgrammer May 18 '21 at 13:56 — OldProgrammer, May 18 '21 at 13:56

score 0 · Answer 1 · answered May 18 '21 at 14:00

You should declare your own excepcion with the PRAGMA EXCEPTION_INIT clause and then throw the exception with RAISE_APPLICATION_ERROR as it's shown in this answer:

https://stackoverflow.com/a/6020523/518

Note: you should use an error code less than -20000 (in the answer -20001 is used)

score 0 · Answer 2 · answered May 18 '21 at 14:37

--I changed the code with PRAGMA EXCEPTION_INIT --

create or replace procedure do_utt(
p_radnr in utt.radnr%type,
p_pnr in utt.pnr%type,
p_knr in utt.knr%type,
p_belopp in utt.belopp%type,
p_datum in utt.datum%type)
as

declare 
unauthorised exception;
PRAGMA EXCEPTION_INIT( unauthorised, -20001 );

begin
insert into utt(radnr, pnr, knr, belopp, datum)
values (radnr_seq.nextval, p_pnr, p_knr, p_belopp, sysdate);
commit;
dbms_output.put_line('Balance '|| p_knr || ' is: '||get_saldo(p_knr));
raise_application_error (20001, 'You are not the account owner!');
exception
when get_authorisation = 0 then
dbms_output.put_line( sqlerrm );
end;

Errors: PROCEDURE DO_UTTAG Line/Col: 8/2 PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:

begin function pragma procedure subtype type current cursor delete exists prior external language The symbol "begin" was substituted for "DECLARE" to continue. Line/Col: 19/26 PLS-00103: Encountered the symbol "=" when expecting one of the following: . then or

Adding an exception (calling function) into procedure - SQL Oracle

2 Answers2