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
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>