CODEWITHSUNDEEP
sqloracle-database
Ralph Blackk
Ralph Blackk

Reputation: 51

Error: numeric or value errors

I know this error issue was been addressed before, but I can't seem to find any relevant solution so I'm posting this question. 

create table subscribers(
  num_s  number(6,0) ,  
  name varchar2(30)  constraint nameM not null, 
  surname varchar2(20), 
  town varchar2(30), 
  age number(3,0)  ,
  rate number(3,0) ,
  reduc number(3,0) ,
  CONSTRAINT subscriber_pk primary key (num_s),
  constraint age_c check (age between 0 and 120)
);

create or replace type copy_bookT as object(
  num               number(6),
  loancode          varchar2 (10),
  book_ref          ref bookT
);

create table copy_books of copy_bookT(
  constraint pk_cb primary key (num),
  constraint chk_st check (loancode in('Loan', 'Not')),
  loancode default 'Loan' not null
);


create table Lending(
  cb_num      number(6),
  sb_num      number(6),
  date_L      date,
  constraint fk_cb foreign key (cb_num) references copy_books(num),
  constraint fk_sb foreign key (sb_num) references Subscribers(num_s)
);



create or replace trigger chk_DateL
for insert or update on lending
COMPOUND TRIGGER
--declare
L_Date int;
avail varchar2(10);
subtype copy_booksRec is lending%ROWTYPE;
type copied_bks is table of copy_booksRec;
cbks copied_bks := copied_bks();

before each row is 
begin
  cbks.extend;
  cbks(cbks.last).cb_num := :new.cb_num;
  cbks(cbks.last).sb_num := :new.sb_num;
end before each row;

before statement is
begin
  for i in cbks.first .. cbks.last loop
    select loancode into avail from copy_books where num = cbks(i).cb_num;
    select count(date_L) into L_Date from lending where sb_num = cbks(i).sb_num and date_L = cbks(i).date_L;
      if (L_Date = 0 and avail = 'Loan') then
        update copy_books set loancode = 'Not' where num = cbks(i).cb_num;
        cbks.delete;
--        cbks(i).date_L := cbks(i).date_L;
    else
      dbms_output.put_line('You can only make ONE LOAN at a time! You have already loaned a book on ' || L_Date);
      cbks.delete;
    end if;
  end loop;
--  FORALL i IN cbks.first .. cbks.last
--      insert into lending values cbks(i);
    cbks.delete;
  end before statement;
end chk_DateL;
/
show errors

It all compiles successfully, but when I try to insert a sample record like:

insert into lending values (2, 700, '10-MAR-14');

it raises a numeric error which comes from the trigger line 18. I don't know what needs fixing despite my efforts.

Upvotes: 0

Views: 147

Answers (3)

Alex Poole
Alex Poole

Reputation: 191435

While the date format issue is a valid point, that isn't causing your error. It's coming from line 18, which is the for ... loop line:

before statement is
begin
  for i in cbks.first .. cbks.last loop

You've got cbks being extended and populated from the before row part of the trigger. When the before statement part fires, cbks is empty, as the row-level trigger hasn't fired yet. It's the first and last references that are throwing the ORA-06502: PL/SQL: numeric or value error error.

You can demonstrate the same thing with a simple anonymous block:

declare
  type my_type is table of dual%rowtype;
  my_tab my_type := my_type();
begin
  for i in my_tab.first .. my_tab.last loop
    null;
  end loop;
end;
/

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5

SQL Fiddle; you can see you can avoid it by adding an extend, but that doesn't really help you in your version, since you seem to want the row values. (You can eliminate the error in your code with an extend, but it's unlikely to do what you want still).

I'm really not sure what you're trying to achieve here, so I don't really have any advice on what you need to do differently.

Upvotes: 3

smnbbrv
smnbbrv

Reputation: 24571

as Mureinik already told, Oracle does not know about how to transform your varchar2 into date datatype and you should use date explicitly. But instead of making to_date use date literal - in my opinion it is more clear than using of to_date function

insert into lending values (2,700,date '2014-03-10');

by the way, you can simply change your NLS settings by altering the current session and installing the date format you need

Upvotes: 2

Mureinik
Mureinik

Reputation: 311978

You should not count on Oracle's default date format to translate your string literal to a date value , you should define the format you're using explicitly:

insert into lending values (2, 700, to_date('10-MAR-14', 'DD-MON-YY'));

Upvotes: 5

Related Questions