ORA-06502: PL/SQL: numeric or value error: character to number conversion error appears on the first call

Question

I have the below pl/sql procedure

PROCEDURE insert_p(
                p_batch_rec  IN  ra_batches%rowtype,
                p_batch_id   OUT NOCOPY ra_batches.batch_id%type,
                p_name       OUT NOCOPY ra_batches.name%type
              )

batch_id is NUMBER(18,0) and p_name is VARCHAR2(50 CHAR)

I'm calling the procedure with

insert_p (l_batch_rec, p_batch_id, p_name);

where p_batch_id:=NULL and p_name:=NULL

I get the conversion error only on the first time I run the procedure. If I run again without changes, it runs fine. To reproduce the error, I disconnect and connect again.

Any ideas why does this error come and how should I resolve the same???

1) stackoverflow.com/questions/how-to-ask 2) catb.org/~esr/faqs/smart-questions.html#before — zep
– zep, Commented Mar 23, 2012 at 8:00

Jon Heller · Accepted Answer · 2012-03-24 04:01:01Z

Does the error happen in the initialization part of a package that is used by the function?

You should be able to easily find exactly where the error happens. By default, Oracle will display the object and the line number of the error. (Although, to my great frustration, I find that it is very common for people to write when others then [poor logging that throws out line number].)

SQL> --Create package
SQL> create or replace package test_package is
  2     procedure test_procedure;
  3     conversion_error number;
  4  end;
  5  /

Package created.

SQL> --Create package body.  Note the initialization part that will fail.
SQL> create or replace package body test_package is
  2     procedure test_procedure is
  3     begin
  4             null;
  5     end;
  6  begin
  7     conversion_error := 'This is not a number';
  8  end;
  9  /

Package body created.

SQL> --This will fail the first time
SQL> begin
  2     test_package.test_procedure;
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "JHELLER.TEST_PACKAGE", line 7
ORA-06512: at line 2


SQL> --But will now work until you reconnect, or run DBMS_SESSION.RESET_PACKAGE.
SQL> begin
  2     test_package.test_procedure;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>

Collectives™ on Stack Overflow

ORA-06502: PL/SQL: numeric or value error: character to number conversion error appears on the first call

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related