CODEWITHSUNDEEP
oracle-databasefunctionplsqlprocedure
S.J.
S.J.

Reputation: 1

Bad bind variable in procedure in oracle

I'm trying to create a procedure that inserts a new row in a table deposit/uttag, throws an exception if you are not authorized to do this transaction. I'm supposed to create a function that checks if someone is authorized or not, and then use that function in this procedure.

I keep getting this error which I don't get:

Errors: PROCEDURE DO_UTTAG
Line/Col: 18/22 PLS-00049: bad bind variable 'NEW.PNR'
Line/Col: 18/31 PLS-00049: bad bind variable 'NEW.KNR'

Quick translations:

Saldo=balance

konto= account

pnr= personal number

knr= customer number

belopp= amount

These are the main tables:

CREATE TABLE bankkund(
PNR VARCHAR2(11) PRIMARY KEY,
FNAMN  VARCHAR2(25) NOT NULL,
ENAMN VARCHAR2(25) NOT NULL ,   
PASSWD VARCHAR2(16) NOT NULL,
UNIQUE(PASSWD));

CREATE TABLE kontotyp(
KTNR NUMBER(6)PRIMARY KEY,
 KTNAMN  VARCHAR2(20)NOT NULL,
 RÄNTA NUMBER(5,2) NOT NULL);

CREATE TABLE konto(
KNR NUMBER(8)PRIMARY KEY,
 KTNR NUMBER(6)NOT NULL,
 REGDATUM DATE NOT NULL,
 SALDO NUMBER(10,2),
 FOREIGN KEY(ktnr) REFERENCES kontotyp(ktnr));

 CREATE TABLE uttag(
 RADNR NUMBER(9) PRIMARY KEY,
 PNR VARCHAR2(11)NOT NULL,
 KNR NUMBER(8)NOT NULL,
 BELOPP  NUMBER(10,2),
 DATUM DATE NOT NULL,
 FOREIGN KEY(pnr) REFERENCES bankkund(pnr),
 FOREIGN KEY(knr) REFERENCES konto(knr));

This is the procedure:

create or replace procedure do_uttag (
  p_radnr  kontoägare.radnr%TYPE,
  p_pnr    bankkund.pnr%TYPE, 
  p_knr    konto.knr%TYPE, 
  p_belopp uttag.belopp%TYPE)
as
v_saldo konto.saldo%type;
begin
select saldo
into v_saldo
from konto;
  insert into uttag(
    radnr,   pnr,   knr,   belopp,   datum
  ) values (
    p_radnr, p_pnr, p_knr,  p_belopp, SYSDATE
  );
  
  if get_behörighet(:NEW.PNR,:NEW.KNR) = 0
   then
      raise_application_error(-20101, 'Du har inte behörigheten!');
      
      else
        update konto
        set saldo = v_saldo - p_belopp;
        dbms_output.put_line('Saldot är nu = ' ||(v_saldo - p_belopp));
  
   end if;

end;
/

This is the function that I created for authorization:

create or replace function get_behörighet (
p_pnr in bankkund.pnr%type,  
p_knr in konto.knr%type)
return number
is 
v_pnr bankkund.pnr%type;
v_knr konto.knr%type;
begin
select bankkund.pnr, konto.knr 
into v_pnr, v_knr
from bankkund, konto
where bankkund.pnr = p_pnr
and konto.knr = p_knr;
return 1;
exception
when others then
return 0;
end;
/

Would really appreciate the help!!

Upvotes: 0

Views: 86

Answers (1)

Krasnoslobodtsev Sergei
Krasnoslobodtsev Sergei

Reputation: 236

get_behörighet(:NEW.PNR,:NEW.KNR)

this is a form of addressing fields allowed in triggers

Upvotes: 1

Related Questions