CODEWITHSUNDEEP
sqloracle-databaseplsql
Renaud is Not Bill Gates
Renaud is Not Bill Gates

Reputation: 2084

PL/SQL: Concat string in a loop

I want to exec a CURSOR in SQL*Plus, but I'm getting this error message :

ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 11

This is my PL/SQL Script :

DECLARE
    CURSOR cr IS SELECT * FROM LOGICIEL;
    unLog LOGICIEL%ROWTYPE;
    nbrEtoiles NUMBER;
    etoiles LOGICIEL.ETOILES%TYPE;
BEGIN
    OPEN cr;
    FETCH cr INTO unLog;
    WHILE cr%FOUND LOOP
        LOOP
            etoiles := etoiles || '*';
            nbrEtoiles := nbrEtoiles + 1;
            EXIT WHEN nbrEtoiles >= (unLog.PRIX/100);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('Nombre Etoiles: ' || etoiles);
        --UPDATE LOGICIEL SET ETOILES = etoiles;
        nbrEtoiles := 0;
        FETCH cr INTO unLog;
    END LOOP;
    CLOSE cr;
END;
/

Upvotes: 0

Views: 11064

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

Given that line 11 is

etoiles := etoiles || '*';

and given the error, it would appear that the string you are trying to build is too long for the local variable you've declared. For every row in the logiciel table, you're adding at least 1 character to the local variable etoiles and potentially more based on the value of the prix column. It's not shocking that at some point the string is too long for the variable you've declared.

Upvotes: 2

Related Questions