oracleoracle11goracle10goracle-sqldeveloperoracle-cursor

fetching table data into a table using cursor


I have a table called phonebook and it has two columns (firstName, LastName). I want to create a table of lastName index by firstName using cursor, and I wrote this code:

CREATE OR REPLACE PROCEDURE proc1 AS
    TYPE tableNames IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR(20);
    v1 tableNames;
    v_firstName PHONEBOOK.FIRSTNAME%TYPE;
    v_lastName PHONEBOOK.LASTNAME%TYPE;
    CURSOR c_name IS SELECT FIRSTNAME, LASTNAME FROM PHONEBOOK;
BEGIN
    OPEN c_name;
    LOOP
        FETCH c_name INTO v_firstName, v_lastName;
        EXIT WHEN c_name%NOTFOUND;
        v1(v_firstName) := v_lastName;
    END LOOP;

    FOR idx IN v1.FIRST..v1.LAST 
    LOOP
        DBMS_OUTPUT.PUT_LINE (v1(idx));
    END LOOP;

    CLOSE c_name;
END;
/

It has been successfully compiled. When I run this procedure it should print lastNames which stored in the tableNames but it gave me an error:

ORA-06502 "PL/SQL: numeric or value error"
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 help me to solve this problem


Solution

  • Not FOR, but WHILE. Also, I used cursor FOR loop as a source; easier to write & maintain.

    SQL> create table phonebook (firstname varchar2(10), lastname varchar2(10));
    
    Table created.
    
    SQL> insert into phonebook
      2    select 'Little', 'Foot'    from dual union all
      3    select 'Mc'    , 'Donalds' from dual;
    
    2 rows created.
    
    SQL> create or replace procedure proc1 as
      2    type tablenames is table of varchar2(10) index by varchar2(10);
      3    v1 tablenames;
      4    idx varchar2(10);
      5  begin
      6    for cur_r in (select firstname, lastname
      7                  from phonebook
      8                 )
      9    loop
     10      v1(cur_r.firstname) := cur_r.lastname;
     11    end loop;
     12
     13    idx := v1.first;
     14    while idx is not null loop
     15      dbms_output.put_line(v1(idx));
     16      idx := v1.next(idx);
     17    end loop;
     18  end;
     19  /
    
    Procedure created.
    
    SQL> exec proc1;
    Foot
    Donalds
    
    PL/SQL procedure successfully completed.
    
    SQL>