ORA-10400 error in PL/SQL, cannot auto generate tran_no field in table using procedure

T_Acc_Details is a table for storing the account number details of a person with the given fields.

create table T_Acc_Details(
  acc_no number(5) primary key,
  acc_holder_name varchar2(50),
  acc_type varchar2(5) check (acc_type in ('SB', 'CA', 'FD')),
  opening_date date,
  balance_amt number(10,2)
)

I have also inputted values into the field like the following:

insert into T_Acc_Details values(10000, 'A', 'SB', to_date('23/11/2019', 'DD/MM/YYYY'), 1000.2);

Secondly, I created the Transaction details table in which the values are to be inserted on the basis of it being deposit or withdrawal. The table query is as follows:

create table T_Tran_Details(
  tran_no number(5) primary key not null,
  acc_no number(5),
  tran_type varchar2(5) check (tran_type in ('w', 'd')),
  transaction_date date,
  amount number(10,2),
  foreign key(acc_no) references T_Acc_Details(acc_no)
)

Furthur, this is my procedure. Here, I have taken acc_no, amount, tran_type as paramters and taken current balance from balance amount and tran_no as variables. I am trying to write the logic for deposit portion. Also, autogenerating value for tran_no.

create or replace procedure pr_updateBal(
p_acc_no T_Acc_Details.Acc_No%type,
p_amount T_Tran_Details.Amount%type,
p_tran_type T_Tran_Details.Tran_Type%type
) is
p_curr_bal T_Acc_Details.Balance_Amt%type;
p_tran_no T_Tran_Details.Tran_No%type;
begin
  select max(nvl(tran_no,0)+1) into p_tran_no from T_Tran_Details;
  select T_Acc_Details.Balance_Amt into p_curr_bal from T_Acc_Details where T_Acc_Details.Acc_No = p_acc_no;
  if(p_tran_type = 'd') then
    p_curr_bal := p_curr_bal + p_amount;
    insert into T_Tran_Details(tran_no, acc_no, tran_type, transaction_date, amount) 
    values (p_tran_no, p_acc_no, p_tran_type, to_date(sysdate,'DD/MM/YY'), p_amount);
    update T_Acc_Details
    set T_Acc_Details.Balance_Amt = p_curr_bal;
    dbms_output.put_line('Amount deposited!');
  end if;
end pr_updateBal;

As soon as I try to test the code and run, the insert into T_Tran_Details(.. gives rise to this error. How are the values even null? I have taken tran_no as primary key.

error

Answer

I have found your problem, so this line:

select max(nvl(tran_no,0)+1) into p_tran_no from T_Tran_Details;

generates a null value bcs your table is empty, when u simply write:

Select max(nvl(tran_no,0)+1) max from T_Tran_Details; 

u get a null value in the max column.

To generate new tran_no try using a sequence or like this:

for i in (select max(tran_no) max from T_Tran_Details) loop
   if i.max is null then p_tran_no:=0;
   else p_tran_no:=i.max +1;
end if;
end loop;

So your whole code would look like this:

create or replace procedure pr_updateBal(
            p_acc_no T_Acc_Details.Acc_No%type,
            p_amount T_Tran_Details.Amount%type,
            p_tran_type T_Tran_Details.Tran_Type%type
) 
is
p_curr_bal T_Acc_Details.Balance_Amt%type;
p_tran_no T_Tran_Details.Tran_No%type;
begin


 for i in (select max(tran_no) max from T_Tran_Details) loop
      if i.max is null then p_tran_no:=0;
      else p_tran_no:=i.max+1;
    end if;
 end loop;

  select T_Acc_Details.Balance_Amt into p_curr_bal from T_Acc_Details 
   where  T_Acc_Details.Acc_No = p_acc_no;

 if(p_tran_type = 'd') then
       p_curr_bal := p_curr_bal + p_amount;
 insert into T_Tran_Details(tran_no, acc_no, tran_type, transaction_date, amount) 
values (p_tran_no, p_acc_no, p_tran_type, to_date(sysdate,'DD/MM/YY'), p_amount);
update T_Acc_Details
set T_Acc_Details.Balance_Amt = p_curr_bal;
dbms_output.put_line('Amount deposited!');
end if;
end pr_updateBal;