Problem with PL/SQL anonymous block in an exam

Question

I am working with a code in SQLDeveloper for an exam and I'm having problems with the code. The error shown is

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

ORA-06512: at line 7

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.

The code I'm using is this one:

VAR RUT_CLIENTE VARCHAR2(15);
EXEC :RUT_CLIENTE:= '12487147-9';


DECLARE
    V_NOMBRE VARCHAR2(75);
    V_RUN VARCHAR2(50);
    V_RENTA VARCHAR2(12);
    V_EST_CIVIL VARCHAR2(40);
BEGIN
    SELECT 
        CLI.NOMBRE_CLI || ' ' || CLI.APPATERNO_CLI || ' ' || CLI.APMATERNO_CLI, 
        TO_CHAR(CLI.NUMRUT_CLI || '-' || CLI.DVRUT_CLI),
        TO_CHAR(CLI.RENTA_CLI, '$999G999G999'),
        EST.DESC_ESTCIVIL
        INTO V_NOMBRE, V_RUN, V_RENTA, V_EST_CIVIL
    FROM CLIENTE CLI JOIN ESTADO_CIVIL EST
    ON CLI.ID_ESTCIVIL = EST.ID_ESTCIVIL
    WHERE CLI.NUMRUT_CLI || '-' || CLI.DVRUT_CLI = :RUT_CLIENTE;
    DBMS_OUTPUT.PUT_LINE('DATOS DEL CLIENTE');
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('----------------');
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('Nombre: ' || V_NOMBRE);
    DBMS_OUTPUT.PUT_LINE('RUN: ' || V_RUN);
    DBMS_OUTPUT.PUT_LINE('Estado Civil: ' || V_EST_CIVIL);
    DBMS_OUTPUT.PUT_LINE('Renta: ' || V_RENTA);
END;

What am I doing wrong? Also, I have to make this block run three times, each time having to enter a different RUT_CLIENTE (the equivalent of the Social Security number in Chile) to show different results, so should I use a loop for that?

seems, one of the following V_NOMBRE VARCHAR2(75); V_RUN VARCHAR2(50); V_RENTA VARCHAR2(12); V_EST_CIVIL VARCHAR2(40); is not long enough — Sergey
– Sergey, Commented Apr 22, 2021 at 14:03
As per line 7, Increase the length of V_EST_CIVIL VARCHAR2(40). — Ankit Bajpai
– Ankit Bajpai, Commented Apr 22, 2021 at 14:04
Line 7 is your select statement. So the problem is that one of the four local variables you're selecting into is too small. Look at the definitions of the columns from the two tables (not present here) to see what the maximum size of the various columns is. — Justin Cave
– Justin Cave, Commented Apr 22, 2021 at 14:05
I forgot to mention that line 7 is when I declare V_RUN VARCHAR2(50); — Sebastián Izurieta Larenas
– Sebastián Izurieta Larenas, Commented Apr 22, 2021 at 14:06
I tried making V_RENTA VARCHAR2(12) longer and it worked, don't know why it was causing so much trouble. — Sebastián Izurieta Larenas
– Sebastián Izurieta Larenas, Commented Apr 22, 2021 at 14:10

Sayan Malakshinov · Accepted Answer · 2021-04-22 14:24:58Z

You can avoid such errors if you will use define your variables using the types from your cursor:


DECLARE
    cursor cur(p_RUT_CLIENTE) is 
      SELECT 
          CLI.NOMBRE_CLI || ' ' || CLI.APPATERNO_CLI || ' ' || CLI.APMATERNO_CLI as col_nombre, 
          TO_CHAR(CLI.NUMRUT_CLI || '-' || CLI.DVRUT_CLI) as col_run,
          TO_CHAR(CLI.RENTA_CLI, '$999G999G999')          as col_renta,
          EST.DESC_ESTCIVIL                               as col_est_civil
      FROM CLIENTE CLI JOIN ESTADO_CIVIL EST
      ON CLI.ID_ESTCIVIL = EST.ID_ESTCIVIL
      WHERE CLI.NUMRUT_CLI || '-' || CLI.DVRUT_CLI = p_RUT_CLIENTE;
    
    V_NOMBRE    cur.col_nombre%type;
    V_RUN       cur.col_run%type;
    V_RENTA     cur.col_renta%type;
    V_EST_CIVIL cur.est_civil%type;
    
BEGIN
    open cur(:RUT_CLIENTE)
    fetch cur into INTO V_NOMBRE, V_RUN, V_RENTA, V_EST_CIVIL;
    close cur;
    
    DBMS_OUTPUT.PUT_LINE('DATOS DEL CLIENTE');
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('----------------');
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('Nombre: ' || V_NOMBRE);
    DBMS_OUTPUT.PUT_LINE('RUN: ' || V_RUN);
    DBMS_OUTPUT.PUT_LINE('Estado Civil: ' || V_EST_CIVIL);
    DBMS_OUTPUT.PUT_LINE('Renta: ' || V_RENTA);
END;

Collectives™ on Stack Overflow

Problem with PL/SQL anonymous block in an exam

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related