CODEWITHSUNDEEP
oracle-databaseplsql
user7111260
user7111260

Reputation:

Strange error in PLSQL script

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;

Upvotes: 0

Views: 48

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132710

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));

Upvotes: 3

Related Questions