ORA-06502 in a stored procedure

Question

Hello I have this simple procedure that compiles fine but after I initialize the variables the error is this.

The table is created like this

Create table vcelar_pomocky 
(
    cislo_nakupu Number(5,0) NOT NULL ,
    cislo_ula Number(5,0) NOT NULL ,
    rodne_cislo Varchar2 (30) NOT NULL ,
    datum_nakupu Date NOT NULL ,
    nazov Varchar2 (20) NOT NULL ,
    typ_polozky Varchar2 (20) NOT NULL ,
    cena_polozky Number(5,2) NOT NULL ,

    primary key (cislo_nakupu) 
) 
/

The procedure looks like this:

CREATE OR REPLACE PROCEDURE quera_pomocky
(
v_cislo_nakupu  IN  VCELAR_POMOCKY.cislo_nakupu%TYPE,
v_nazov         OUT VCELAR_POMOCKY.nazov%TYPE,
v_cena_polozky  OUT VCELAR_POMOCKY.cena_polozky%TYPE
)
IS
BEGIN
    SELECT  nazov || ' ' || typ_polozky,cena_polozky
        INTO v_nazov,v_cena_polozky
        FROM VCELAR_POMOCKY
        WHERE v_cislo_nakupu = cislo_nakupu AND rodne_cislo = '750927/3913';
END quera_pomocky;
/

VARIABLE p_nazov VARCHAR2(20);
VARIABLE p_cena_polozky VARCHAR2(20);
EXECUTE quera_pomocky(24, :p_nazov , :p_cena_polozky);
PRINT p_nazov p_cena_polozky;

And the error is

Error starting at line : 65 in command -
BEGIN quera_pomocky(24, :p_nazov , :p_cena_polozky); END;

Error report:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DOMA.QUERA_POMOCKY", line 9
ORA-06512: at line 1
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).

*Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

Please edit your answer and show us the definition ot the table VCELAR_POMOCKY. We need the datatypes, which are used by the %TYPE construct in your query... — wolφi

Littlefoot Littlefoot · Accepted Answer · 2020-05-03T10:55:15

You are putting this:

nazov || ' ' || typ_polozky

into a parameter

v_nazov OUT VCELAR_POMOCKY.nazov%TYPE

which is - in a table - declared as

nazov Varchar2 (20)

So: if length of nazov || ' ' || typ_polozky exceeds 20 characters (mind you: both are - in the table - declared as varchar2(20) which makes that concatenation's MAX length 41), you're in trouble.

I'd suggest you to set OUT parameter to e.g.

v_nazov OUT varchar2(50)

ORA-06502 in a stored procedure

1 Answers