CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
csandreas1
csandreas1

Reputation: 2388

Stored procedure ERROR 1520: File No:ORA-06502: PL/SQL: numeric or value error when calling it from oracle_cx python driver

I am calling a stored procedure from Python program.

There is an issue that happens on certain values. For example it fails when i send values 2020,2017 but it succeeds wen I send 2010,2050 etc. What can be the issue?

DB Version: NLSRTL Version 11.2.0.4.0 - Production

Oracle_cx: latest

Procedure parameter that causes the issue : P_FILE_YEAR IN NUMBER

Error Message: ERROR 1520: File No:ORA-06502: PL/SQL: numeric or value error: number precision too large

It fails in the following exception:

BEGIN
        SELECT  MAX(mlal_file_no) + 1
        INTO    ln_file_no
        FROM    doc_mail_allocation
        WHERE   mlal_file_year = P_FILE_YEAR
        AND     std_file_type_code = 2 ;
    EXCEPTION
        WHEN no_data_found THEN
            ln_file_no := 1 ;
        WHEN OTHERS THEN
            p_error_code := 1 ;
            p_error_msg  := 'ERROR 1520: File No:'||SQLERRM||' AND flal_file_year = '||P_FILE_YEAR;
            RETURN ;
    END ;

Upvotes: 0

Views: 265

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

Looks like this:

Sample data:

SQL> select * From doc_mail_allocation;

MLAL_FILE_YEAR STD_FILE_TYPE_CODE MLAL_FILE_NO
-------------- ------------------ ------------
          2010                  2          100
          2020                  2          999

Your code:

SQL> declare
  2    ln_file_no number(3);
  3  begin
  4    SELECT  MAX(mlal_file_no) + 1
  5          INTO    ln_file_no
  6          FROM    doc_mail_allocation
  7          WHERE   mlal_file_year = &P_FILE_YEAR
  8          AND     std_file_type_code = 2 ;
  9    dbms_output.put_line('result = ' || ln_file_no);
 10  end;
 11  /
Enter value for p_file_year: 2010
result = 101

PL/SQL procedure successfully completed.

SQL> /
Enter value for p_file_year: 2020
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4


SQL>

See the difference & error cause? If you declared a variable as "too small" (number(3)), for year 2020 MAX(mlal_file_no) + 1 equals 1000 and it can't fit into such a variable.

Solution? Enlarge ln_file_no, or check why this happens (if it shouldn't) - then there's error in data.

Upvotes: 1

Related Questions