CODEWITHSUNDEEP
oracle-databasefunctionstored-proceduresplsqldeveloper
bin
bin

Reputation: 131

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

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.

Upvotes: 0

Views: 14884

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

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
...

Upvotes: 1

Rene
Rene

Reputation: 10541

Since constraint-, column- and table etc. names can be up to 30 characters long, I would suggest declaring you variables also for that length.

 V_constraintname varchar2(30);
 V_rowner  varchar2(30);
 V_rconstraintname  varchar2(30);
 V_columnname  varchar2(30);
 V_tabname  varchar2(30);

What is the maximum length of a table name in Oracle?

Upvotes: 1

Related Questions