Issues with my Associative Array [message #684618] |
Fri, 09 July 2021 21:35 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
I am trying to write a simple procedure for getting a better understanding of Associative Arrays and all I am doing is to extract the employee names from the employees table.
Schema used: HR schema given by Oracle
Here's my packaged procedure for this:
CREATE OR REPLACE PACKAGE xtractempdetails
IS
TYPE empspecificfirstname_rt IS TABLE OF EMPLOYEES.first_name%TYPE INDEX BY BINARY_INTEGER;
empspecificfname_aa empspecificfirstname_rt;
PROCEDURE getempdetails(p_InNumempid IN EMPLOYEES.employee_id%TYPE,
p_Outtypeempfname OUT empspecificfirstname_rt
--, p_Outtypeempfname OUT empspecificfname_aa ====> gives me an error: PLS-00488:must be a type
);
END;
CREATE OR REPLACE PACKAGE BODY xtractempdetails IS
PROCEDURE getempdetails(p_InNumempid IN EMPLOYEES.employee_id%TYPE,
p_Outtypeempfname OUT empspecificfirstname_rt
)
IS
CURSOR cur_empfirst_name IS
SELECT first_name
FROM employees
WHERE employee_id = p_InNumempid;
lv_NumCount NUMBER(3);
BEGIN
OPEN cur_empfirst_name;
FETCH cur_empfirst_name INTO p_Outtypeempfname;
lv_NumCount:= p_Outtypeempfname.COUNT;
DBMS_OUTPUT.put_line('Total Number of Employees are : ' ||lv_NumCount);
FOR indx IN 1..lv_NumCount
LOOP
p_Outtypeempfname(indx).first_name;
END LOOP;
CLOSE cur_empfirst;
END;
When I try to compile I keep geeting an error:
[b]PLS-00597:expression 'p_Outtypeempfname' in the INTO list is of wrong type.[/b]
Any reason why? Is it that I need to use BULK COLLECT for this? Can I not do this with an explicit cursor?
[Updated on: Fri, 09 July 2021 21:37] Report message to a moderator
|
|
|
|
Re: Issues with my Associative Array [message #684620 is a reply to message #684619] |
Sat, 10 July 2021 12:05 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
Solomon,
You are right with . I modified my procedure to make it more meaningful and it works.
Question is why BULK COLLECT and why not without it
Why this: fetch cursor_name BULK COLLECT INTO type and why not with plain: fetch cursor_name into type
a)Is it because FETCH INTO clause can handle only 1 row of data where as since I am using a collection and bringing in all 106 rows from the tables, it is blowing out?
b)If so, then do we always have to use BULK COLLECT when retrieving data via a collection/associative array?
[Updated on: Sat, 10 July 2021 12:23] Report message to a moderator
|
|
|
|
|