CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Hugo Nilsson
Hugo Nilsson

Reputation: 29

Procedure to check if authorized

So I have this function:

create or replace function get_authorization( 
p_pnr in bankcustomer.pnr%type, 
p_knr in account.cnr%type) 
return number
as
v_authorization bankcustomer.pnr%type; 
begin
select count(*)
into v_authorization
from bankcustomer,account
where pnr = p_pnr 
and cnr = p_cnr;
return v_authorization; 
exception
when no_data_found then
return -1;
end;

This returns 1 or 0 if allowed. I need to do a procedure which adds a row in a table called withdraw and that procedure needs to call get_authorization to check if the customer is allowed. This is what i have so far:

create or replace procedure do_withdraw(
p_pnr in withdraw.pnr%type,
p_belopp in withdraw.belopp%type)
as
declare
authorization exception;
begin
insert into uttag(pnr,amount)
values((select pnr from account),p_amount); /*pnr is foreign key in withdraw*/
if user not in (select get_amount(p_pnr) from dual) then
raise authorization; 
end if; 
exception  
when authorization then
raise_application_error(-20007,'Unauthorized!');
commit;
end;

I get alot of error messages and specifically on declare. I really cant wrap my head around this :(

Upvotes: 1

Views: 140

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You have few problems with your code -

create or replace procedure do_withdraw(
                            p_pnr    in withdraw.pnr%type,
                            p_belopp in withdraw.belopp%type)
as
-- declare        -- Declare keyword is not used in procedure
authorization exception;
begin
     insert into uttag(pnr,amount)
     values((select pnr from account),       -- This might return multiple rows, So you have to add a where clause in this query.
            p_amount); /*pnr is foreign key in withdraw*/
     if user <> get_authorization(p_pnr) then  -- You are checking current user with amount which should be corrected.
        raise authorization; 
     end if;
     commit;        -- Commit should be last sattement of procedure
exception  
         when authorization then
              raise_application_error(-20007,'Unauthorized!');
         Rollback;  -- In exception you should use Rollabck instead of Commit;
end;

Upvotes: 1

Hugo Nilsson
Hugo Nilsson

Reputation: 29

So ive tried what you said and think i know what you mean:

create or replace procedure do_withdraw(
                            p_pnr    in withdraw.pnr%type,
                            p_amount in withdraw.amount%type)
as
-- declare        -- Declare keyword is not used in procedure
authorization exception;
begin
     insert into withdraw(pnr,amount)
     values((select pnr from account where pnr = p_pnr),       -- This might return multiple rows, So you have to add a where clause in this query.
            p_amount); /*pnr is foreign key in withdraw*/
     if user not in (select get_authorization(p_pnr)) then  -- You are checking current user with amount which should be corrected.
        raise authorization; 
     end if;
     commit;        -- Commit should be last sattement of procedure
exception  
         when authorization then
              raise_application_error(-20007,'Unauthorized!');
         Rollback;  -- In exception you should use Rollabck instead of Commit;
end;

Now i get error: Line/Col: 11/51 PLS-00103: Encountered the symbol ")" when expecting one of the following:. ( , * % & - + /

Upvotes: 0

Related Questions