0

I have a strange error on this pl/sql script

'PL/SQL: numeric or value error: character to number conversion error' in the is_prime function

if((nr mod it) = 0)
HERE->then
        return cast(0 as int);
end if;

I can't figure out what I did wrong, because I pass only int's to is_prime and I have declared only a int in is_prime...

set serveroutput on;

drop table exresult;
create table exresult(AA int,BB int);

create or replace function is_prime(nr in int) return int is
  it int := 0;
begin
  for it in 2..floor(sqrt(nr)) loop
    if((nr mod it) = 0)
      then
        return cast(0 as int);
    end if;
  end loop;
  return cast(1 as int);
end is_prime;
/

create or replace function sum_digits(nr in int) return int is
  summ int := 0;
  tmp int := nr;
begin
  loop
    summ := summ + (tmp mod 10);
    tmp := floor(tmp / 10);
    exit when tmp = 0;
  end loop;

  return summ;
end sum_digits;
/

declare
  target constant int := 5;
  nmod int := 0;
  nprm int := 0;
  it   int := 0;
begin
  for it in 1..10000 loop
    nmod := sum_digits(it) mod 10;
    nprm := is_prime(it);
    dbms_output.put_line(it + ',' + nmod + ','  + nprm);
    if(nmod = target)
      then
        insert into exresult select it, nprm from dual;
    end if;
  end loop;
end;
2
  • 1
    What happens if you change e.g. return cast(0 as int); to return 0; which is all you need? Commented Mar 10, 2018 at 10:37
  • nothing changes Commented Mar 10, 2018 at 10:38

1 Answer 1

3

Your problem is actually here:

dbms_output.put_line(it + ',' + nmod + ','  + nprm);

+ is not the concatenation operator in PL/SQL, you need ||:

dbms_output.put_line(it || ',' || nmod || ','  || nprm);

That's relying on implicit conversion of the int values to varchar2, which is fine, but more correctly should be:

dbms_output.put_line(to_char(it) || ',' || to_char(nmod) || ','  || to_char(nprm));
Sign up to request clarification or add additional context in comments.

2 Comments

thanks for taking the time to read my code and point this mistake. gotta love at what line oracle points the error
It will be pointing to a line in the final block of code you ran, not in the whole script.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.