ORA-06502: PL/SQL: numeric or value error- oracle

Question

I am trying to get the dependencies on a table using a stored procedure. For my sp, i have given schema name and table names as inputs and constraint related variables as output. i have also added one cursor to return multiple rows.

My sp runs fine but when am trying to execute the stored procedure, it gives error saying:

     ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.TEST", line 23
ORA-06512: at line 10
06502. 00000 -  "PL/SQL: numeric or value error%s"

This is how am trying to execute the stored procedure: -- trying to execute the stored procedure

 Set  serveroutput on
        declare 
        V_constraintname varchar2(20);
        V_rowner  varchar2(20);
        V_rconstraintname  varchar2(20);
        V_columnname  varchar2(20);
        V_tabname  varchar2(20);
        V_refcsr SYS_REFCURSOR;
        begin
        dbms_output.enable;
        Test('ABC','XYZ',V_constraintname,V_rowner,V_rconstraintname,V_columnname,V_tabname,V_refcsr);

         dbms_output.put_line(V_constraintname) ;
         dbms_output.put_line(V_rowner) ;
         dbms_output.put_line(V_rconstraintname) ;
         dbms_output.put_line(V_columnname) ;
         dbms_output.put_line(V_tabname) ;
         --dbms_output.put_line(V_refcsr) ;
        end;
        /

First 2 values are the inputs and remaining are in out parameters of the stored procedure.

It would be helpful to post the entire content of test along with the above. Also the complete error (which may have a useful line number where the error occurred). — Peter M.
I suppose since the procedure compiled without errors, you believe the issue is with how you call it. Not so. The procedure may compile fine, but if at runtime you give it a numeric value where the procedure expects a date, or if you give it the string 'abc' where it expects a number, it will throw the runtime error you saw. The error is in the code for the sp, not in how you call it. As Peter said, please share your sp code - otherwise we can't help. — mathguy
Please do not create anything in the SYS schema (or any other built-in schema). — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2016-08-11T13:52:40

You have values in the data dictionary that are larger than the variables you have declared in your anonymous block.

The simplest way to avoid that is to use the %TYPE syntax, to declare them based on the data type of the column whose data they will eventually receive:

declare 
    V_constraintname   all_constraints.constraint_name%type;
    V_rowner           all_constraints.r_owner%type;
    V_rconstraintname  all_constraints.r_constraint_name%type;
    V_columnname       all_cons_columns.column_name%type;
    V_tabname          all_constraints.table_name%type;
    V_refcsr           SYS_REFCURSOR;
begin
...

ORA-06502: PL/SQL: numeric or value error- oracle

2 Answers