Home » SQL & PL/SQL » SQL & PL/SQL » Issues with my Associative Array (Oracle 12c, O/S: Windows 10 pro)
Issues with my Associative Array [message #684618] Fri, 09 July 2021 21:35 Go to next message
buggleboy007
Messages: 278
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 #684619 is a reply to message #684618] Sat, 10 July 2021 09:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3087
Registered: January 2010
Location: Connecticut, USA
Senior Member
You need to use bulk fetch:

FETCH cur_empfirst_name BULK COLLECT INTO p_Outtypeempfname;
SY.
Re: Issues with my Associative Array [message #684620 is a reply to message #684619] Sat, 10 July 2021 12:05 Go to previous messageGo to next message
buggleboy007
Messages: 278
Registered: November 2010
Location: Canada
Senior Member
Solomon,

You are right with
BULK COLLECT INTO
. 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

Re: Issues with my Associative Array [message #684621 is a reply to message #684620] Sat, 10 July 2021 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 67997
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Database PL/SQL Language Reference

Re: Issues with my Associative Array [message #684622 is a reply to message #684621] Sat, 10 July 2021 14:58 Go to previous message
buggleboy007
Messages: 278
Registered: November 2010
Location: Canada
Senior Member
Michel Cadot wrote on Sat, 10 July 2021 12:34

Database PL/SQL Language Reference
Merci Beaucoup.
It appears that based on Oracle's tip, the most efficient way to pass collections to and from database server is to use AA with FORALL statement or BULK COLLECT clause.

https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS99930
(under appropriate uses of AA)

[Updated on: Sat, 10 July 2021 15:13]

Report message to a moderator

Previous Topic: Getting PLS: 00306 wrong number or types of arguments in call to
Next Topic: How to convert multi_byte to single_byte character
Goto Forum:
  


Current Time: Wed Oct 20 18:43:30 CDT 2021