Hi Experts,
Database version 11.2.0.4.0.
I have declare an array type like this.
CREATE OR REPLACE TYPE emparray IS VARRAY (100) OF varchar2 (30);
/
Declare a procedure like this.
CREATE OR REPLACE PROCEDURE prc_get_arr (p_maxrow IN number,
p_emp OUT emparray)
AS
my_emp emparray := emparray ();
CURSOR c_cust
IS
SELECT ename, job
FROM emp
WHERE ROWNUM <= p_maxrow;
v_emp varchar2 (10);
v_job varchar2 (10);
BEGIN
OPEN c_cust;
LOOP
FETCH c_cust INTO v_emp, v_job;
EXIT WHEN c_cust%NOTFOUND;
my_emp.EXTEND;
my_emp (my_emp.COUNT) := v_emp;
my_emp (my_emp.COUNT) := v_job;
END LOOP;
CLOSE c_cust;
p_emp := my_emp;
END;
/
DECLARE
p_maxrow NUMBER:=10;
p_emp emparray;
v_cnt number:=0;
BEGIN
prc_get_arr( p_maxrow => p_maxrow, p_emp => p_emp );
v_cnt:= p_emp.count;
--dbms_output.put_line(v_cnt);
for i in p_emp.first..p_emp.last loop
dbms_output.put_line(p_emp(i));
end loop;
END;
It returns only the JOB column value of EMP table which is in schema SCOTT.
How to get or return the ename column value ?
What are the changes required ?