Getting error PLS-003036 wrong number or types of argument in call to =

user_13077202 0
Viewed 26

I had written the below trigger

create or replace trigger my_trigger
Before insert or update on table1
referencing new as new old as old 
for each row 
declare id number;
cursor id_cnt is 
select count(*) from table2 where my_id=:new.my_id;
begin 
if :new.my_id is null
then RAISE_APPLICATION_ERROR(-001,"MY_ID should nit be null");
elsif id_cnt=0 then
RAISE_APPLICATION_ERROR(-002,"not a valid id ");
else
select new_id from table2 where  my_id=:new.my_id;
if lenght(new_id) <5
then
RAISE_APPLICATION_ERROR(-003,"length is very small ");
END IF;
END IF;

END my_trigger;

At if :new.my_id is null i am getting the below error error PLS-003036 wrong number or types of argument in call to =

There are 2 conditions needs to be checked first condition i need to check my_id is null or not and second condition need to check the length of new_id before that i am checking if that my_id is already existed in table 2 before inserting into table1

plsql oracle
1 Answers

You:

  • want to SELECT ... INTO rather than using a CURSOR
  • misspelt LENGTH
  • need to use ' for string literals and not "; and
  • need to use -20000 to -20999 for user-defined error numbers.

Like this:

create or replace trigger my_trigger
  Before insert or update on table1
  referencing new as new old as old 
  for each row 
declare
  id number;
  id_cnt PLS_INTEGER;
  v_new_id table2.new_id%TYPE;
begin 
  IF :new.my_id is null THEN
    RAISE_APPLICATION_ERROR(-20001,'MY_ID should nit be null');
  END IF;

  select count(*)
  INTO   id_cnt
  from   table2
  where  my_id=:new.my_id;

  if id_cnt=0 then
    RAISE_APPLICATION_ERROR(-20002,'not a valid id');
  else
    select new_id
    INTO   v_new_id
    from   table2
    where  my_id=:new.my_id;

    if length(v_new_id) < 5 then
      RAISE_APPLICATION_ERROR(-20003,'length is very small');
    END IF;
  END IF;
END my_trigger;
/

db<>fiddle here

user_15092640
Related