This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Feb 19, 2013 1:57 AM by user2626293 RSS

Query On PL/SQL Collections

user2626293 Newbie
Currently Being Moderated
Hi All,

Please help me with the below code.

I have written this package, now i want to see the output by calling this package.

Please have a look at the below scenario. This is just a POC that i have tried over the EMPLOYEE table, once this is a success i will apply this to the original code.

How to call this package and how to capture the values that will be stored in the OUT parameters, please help me with this.

-- Package Spec

create or replace PACKAGE emp_dtls_pkg
AS
TYPE dept_rec_type IS RECORD
(DEPARTMENT_NAME HR.departments.DEPARTMENT_NAME%TYPE
,LOCATION_ID HR.departments.LOCATION_ID%TYPE);
TYPE emp_rec_type IS RECORD
(EMPLOYEE_ID HR.employees.EMPLOYEE_ID%TYPE
,FIRST_NAME HR.employees.FIRST_NAME%TYPE
,LAST_NAME HR.employees.LAST_NAME%TYPE
,EMAIL HR.employees.EMAIL%TYPE
,PHONE_NUMBER HR.employees.PHONE_NUMBER%TYPE
,HIRE_DATE HR.employees.HIRE_DATE%TYPE
,JOB_ID HR.employees.JOB_ID%TYPE
,SALARY HR.employees.SALARY%TYPE
,COMMISSION_PCT HR.employees.COMMISSION_PCT%TYPE);

TYPE emp_tbl_type IS TABLE OF emp_rec_type;

PROCEDURE get_emp_dtls
( p_dept_number IN number
, p_dept_rec OUT emp_dtls_pkg.dept_rec_type
, p_emp_rec OUT emp_dtls_pkg.emp_tbl_type
);
END emp_dtls_pkg;

-- Package Body

CREATE OR REPLACE PACKAGE BODY emp_dtls_pkg AS
PROCEDURE get_emp_dtls
( p_dept_number IN number
, p_dept_rec OUT emp_dtls_pkg.dept_rec_type
, p_emp_rec OUT emp_dtls_pkg.emp_tbl_type
)
IS
cursor emp_cur is
select EMPLOYEE_ID
,FIRST_NAME
,LAST_NAME
,EMAIL
,PHONE_NUMBER
,HIRE_DATE
,JOB_ID
,SALARY
,COMMISSION_PCT from HR.employees
where DEPARTMENT_ID = p_dept_number;

l_emp_tbl emp_dtls_pkg.emp_tbl_type := emp_dtls_pkg.emp_tbl_type();
l_dname HR.departments.DEPARTMENT_NAME%TYPE;
l_loc HR.departments.LOCATION_ID%TYPE;

BEGIN
select DEPARTMENT_NAME,LOCATION_ID
into l_dname, l_loc
from HR.departments
where DEPARTMENT_ID = p_dept_number;

IF l_loc is not null THEN
p_dept_rec.DEPARTMENT_NAME := l_dname;
p_dept_rec.LOCATION_ID := l_loc;

FOR emp_rec in emp_cur
LOOP
for i in l_emp_tbl.first .. l_emp_tbl.last
LOOP

l_emp_tbl(l_emp_tbl.LAST).EMPLOYEE_ID := emp_rec.EMPLOYEE_ID;
l_emp_tbl(l_emp_tbl.LAST).FIRST_NAME := emp_rec.FIRST_NAME;
l_emp_tbl(l_emp_tbl.LAST).LAST_NAME := emp_rec.LAST_NAME;
l_emp_tbl(l_emp_tbl.LAST).EMAIL := emp_rec.EMAIL;
l_emp_tbl(l_emp_tbl.LAST).PHONE_NUMBER := emp_rec.PHONE_NUMBER;
l_emp_tbl(l_emp_tbl.LAST).HIRE_DATE := emp_rec.HIRE_DATE;
l_emp_tbl(l_emp_tbl.LAST).JOB_ID := emp_rec.JOB_ID;
l_emp_tbl(l_emp_tbl.LAST).SALARY := emp_rec.SALARY;
l_emp_tbl(l_emp_tbl.LAST).COMMISSION_PCT := emp_rec.COMMISSION_PCT;
END LOOP;
END LOOP;
END IF;
p_emp_rec := l_emp_tbl;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' ERROR: ' || SQLERRM);
END get_emp_dtls;
END emp_dtls_pkg;
/

Thanks and Regards
Srinivas
  • 1. Re: Query On PL/SQL Collections
    Justin Cave Oracle ACE
    Currently Being Moderated
    I'm not sure quite what you are asking...

    If the procedure is doing what you want and you simply want an example of calling it
    DECLARE
      l_dept_rec  emp_dtls_pkg.dept_rec_type;
      l_emp_tbl   emp_dtls_pkg.emp_tbl_type;
    BEGIN
      emp_dtls_pkg.get_emp_dtls( 20, l_dept_rec, l_emp_tbl  );
    END;
    If you want to iterate through the L_EMP_TBL collection and do something like print the first name using DBMS_OUTPUT
    DECLARE
      l_dept_rec  emp_dtls_pkg.dept_rec_type;
      l_emp_tbl   emp_dtls_pkg.emp_tbl_type;
    BEGIN
      emp_dtls_pkg.get_emp_dtls( 20, l_dept_rec, l_emp_tbl  );
      FOR i IN 1 .. l_emp_tbl.count
      LOOP
        dbms_output.put_line( l_emp_tbl(i).first_name );
      END LOOP;
    END;
    If you want to do something else, can you rephrase your question?

    Justin
  • 2. Re: Query On PL/SQL Collections
    RajeshKanna Newbie
    Currently Being Moderated
    Hi Srinivas,

    I hope Below code will be helpful to u..

    -- Package Specification
    
    create or replace PACKAGE emp_dtls_pkg AS
      TYPE dept_rec_type IS RECORD(
        DEPARTMENT_NAME HR.departments.DEPARTMENT_NAME%TYPE,
        LOCATION_ID     HR.departments.LOCATION_ID%TYPE);
      TYPE emp_rec_type IS RECORD(
        EMPLOYEE_ID    HR.employees.EMPLOYEE_ID%TYPE,
        FIRST_NAME     HR.employees.FIRST_NAME%TYPE,
        LAST_NAME      HR.employees.LAST_NAME%TYPE,
        EMAIL          HR.employees.EMAIL%TYPE,
        PHONE_NUMBER   HR.employees.PHONE_NUMBER%TYPE,
        HIRE_DATE      HR.employees.HIRE_DATE%TYPE,
        JOB_ID         HR.employees.JOB_ID%TYPE,
        SALARY         HR.employees.SALARY%TYPE,
        COMMISSION_PCT HR.employees.COMMISSION_PCT%TYPE);
    
      TYPE emp_tbl_type IS TABLE OF emp_rec_type;
    
      PROCEDURE get_emp_dtls(p_dept_number IN number,
                             p_dept_rec    OUT emp_dtls_pkg.dept_rec_type,
                             p_emp_rec     OUT emp_dtls_pkg.emp_tbl_type);
    END emp_dtls_pkg;
    -- Package Body
    
    
    create or replace PACKAGE body emp_dtls_pkg IS
    
      PROCEDURE get_emp_dtls(p_dept_number IN number,
                             p_dept_rec    OUT emp_dtls_pkg.dept_rec_type,
                             p_emp_rec     OUT emp_dtls_pkg.emp_tbl_type) is
      
      begin
      
        SELECT DEPARTMENT_NAME, LOCATION_ID
          INTO p_dept_rec
          FROM departments d
         WHERE d.department_id = p_dept_number;
      
        SELECT EMPLOYEE_ID,
               FIRST_NAME,
               LAST_NAME,
               EMAIL,
               PHONE_NUMBER,
               HIRE_DATE,
               JOB_ID,
               SALARY,
               COMMISSION_PCT BULK COLLECT
          INTO p_emp_rec
          FROM employees e
         WHERE e.department_id = p_dept_number;
      
      end get_emp_dtls;
    
    end emp_dtls_pkg;
    -- Invokeing the Package
    
    DECLARE
    
      l_deptrec     emp_dtls_pkg.dept_rec_type;
      l_emptab      emp_dtls_pkg.emp_tbl_type;
      p_dept_number number(5) := 20;
    
    BEGIN
    
      emp_dtls_pkg.get_emp_dtls(p_dept_number, l_deptrec, l_emptab);
    
      dbms_output.put_line(l_deptrec.DEPARTMENT_NAME);
    
      for i in l_emptab.first .. l_emptab.last loop
        dbms_output.put_line(l_emptab(i).FIRST_NAME);
      end loop;
    
    END emp_dtls_pkg;
  • 3. Re: Query On PL/SQL Collections
    user2626293 Newbie
    Currently Being Moderated
    Dear Justin/Rajesh

    Thanks a zillion for your response, i will try this out and update you back.

    Thanks and Regards
    Srinivas
  • 4. Re: Query On PL/SQL Collections
    user2626293 Newbie
    Currently Being Moderated
    Dear Justin/Rajesh,

    I have tried the below code to retrive the values, but encountered an error.

    DECLARE
    l_dept_rec emp_dtls_pkg.dept_rec_type;
    l_emp_tbl emp_dtls_pkg.emp_tbl_type;
    BEGIN
    emp_dtls_pkg.get_emp_dtls( 20, l_dept_rec, l_emp_tbl );
    FOR i IN 1 .. l_emp_tbl.count
    LOOP
    dbms_output.put_line( l_emp_tbl(i).first_name );
    END LOOP;
    END;

    ORA-06531: Reference to uninitialized collection
    ORA-06512: at line 8
    06531. 00000 - "Reference to uninitialized collection"
    *Cause:    An element or member function of a nested table or varray
    was referenced (where an initialized collection is needed)
    without the collection having been initialized.
    *Action:   Initialize the collection with an appropriate constructor
    or whole-object assignment.

    Then i tried the below code, but same result. Please advice.

    DECLARE
    l_deptrec emp_dtls_pkg.dept_rec_type;
    l_emptab emp_dtls_pkg.emp_tbl_type:=emp_dtls_pkg.emp_tbl_type();
    p_dept_number number(5) := 20;
    BEGIN
    emp_dtls_pkg.get_emp_dtls(p_dept_number, l_deptrec, l_emptab);
    dbms_output.put_line(l_deptrec.department_name);
    for i in l_emptab.first .. l_emptab.last loop
    dbms_output.put_line(l_emptab(i).first_name);
    end loop;
    END;

    Edited by: user2626293 on Feb 5, 2013 9:24 PM

    Edited by: user2626293 on Feb 5, 2013 9:34 PM

    Edited by: user2626293 on Feb 5, 2013 9:35 PM
  • 5. Re: Query On PL/SQL Collections
    jeneesh Guru
    Currently Being Moderated
    Your problem is in Package body..Please check below the comments
    CREATE OR REPLACE
    PACKAGE BODY emp_dtls_pkg
    AS
    PROCEDURE get_emp_dtls(
        p_dept_number IN NUMBER ,
        p_dept_rec OUT emp_dtls_pkg.dept_rec_type ,
        p_emp_rec OUT emp_dtls_pkg.emp_tbl_type )
    IS
      CURSOR emp_cur
      IS
        SELECT EMPLOYEE_ID ,
          FIRST_NAME ,
          LAST_NAME ,
          EMAIL ,
          PHONE_NUMBER ,
          HIRE_DATE ,
          JOB_ID ,
          SALARY ,
          COMMISSION_PCT
        FROM HR.employees
        WHERE DEPARTMENT_ID                = p_dept_number;
      l_emp_tbl emp_dtls_pkg.emp_tbl_type := emp_dtls_pkg.emp_tbl_type();
      l_dname HR.departments.DEPARTMENT_NAME%TYPE;
      l_loc HR.departments.LOCATION_ID%TYPE;
    BEGIN
      SELECT DEPARTMENT_NAME,
        LOCATION_ID
      INTO l_dname,
        l_loc
      FROM HR.departments
      WHERE DEPARTMENT_ID           = p_dept_number;
      IF l_loc                     IS NOT NULL THEN
        p_dept_rec.DEPARTMENT_NAME := l_dname;
        p_dept_rec.LOCATION_ID     := l_loc;
        FOR emp_rec IN emp_cur
        loop
          --"Below LOOP not required.What you expect this loop to do?
          --FOR i IN l_emp_tbl.first .. l_emp_tbl.last
          --LOOP
            --"You need to extend the collection
            l_emp_tbl.extend;
            l_emp_tbl(l_emp_tbl.LAST).EMPLOYEE_ID    := emp_rec.EMPLOYEE_ID;
            l_emp_tbl(l_emp_tbl.LAST).FIRST_NAME     := emp_rec.FIRST_NAME;
            l_emp_tbl(l_emp_tbl.LAST).LAST_NAME      := emp_rec.LAST_NAME;
            l_emp_tbl(l_emp_tbl.LAST).EMAIL          := emp_rec.EMAIL;
            l_emp_tbl(l_emp_tbl.LAST).PHONE_NUMBER   := emp_rec.PHONE_NUMBER;
            l_emp_tbl(l_emp_tbl.LAST).HIRE_DATE      := emp_rec.HIRE_DATE;
            l_emp_tbl(l_emp_tbl.LAST).JOB_ID         := emp_rec.JOB_ID;
            l_emp_tbl(l_emp_tbl.LAST).SALARY         := emp_rec.SALARY;
            l_emp_tbl(l_emp_tbl.last).commission_pct := emp_rec.commission_pct;
          --END LOOP;
        END LOOP;
      END IF;
      p_emp_rec := l_emp_tbl;
    --"Dont use WHEN OTHERS like this..you will not see DBMS_OUTPUT   
    --"all the time you run
    /*EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(' ERROR: ' || SQLERRM);*/
    END get_emp_dtls;
    END emp_dtls_pkg;
    /
  • 6. Re: Query On PL/SQL Collections
    jeneesh Guru
    Currently Being Moderated
    And above all, you dont need all these loops... Just do a BULK COLLECT..
    --"Changed package
    create or replace
    package body EMP_DTLS_PKG
    as
      procedure GET_EMP_DTLS(
          P_DEPT_NUMBER in number ,
          P_DEPT_REC OUT EMP_DTLS_PKG.DEPT_REC_TYPE ,
          P_EMP_REC OUT EMP_DTLS_PKG.EMP_TBL_TYPE )
      is      
      begin
        select DEPARTMENT_NAME,LOCATION_ID
        into P_DEPT_REC.DEPARTMENT_NAME,P_DEPT_REC.LOCATION_ID
        from HR.DEPARTMENTS
        where DEPARTMENT_ID           = P_DEPT_NUMBER;
        if P_DEPT_REC.LOCATION_ID is not null then
          select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL ,
                 PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY ,
                 COMMISSION_PCT
          bulk collect into P_EMP_REC
          from HR.EMPLOYEES
          where DEPARTMENT_ID = P_DEPT_NUMBER;    
        end if;
      end GET_EMP_DTLS;
    end EMP_DTLS_PKG;
    /
    
    --"Run the procedure"
    
    DECLARE
      l_dept_rec emp_dtls_pkg.dept_rec_type;
      l_emp_tbl emp_dtls_pkg.emp_tbl_type;
    BEGIN
      emp_dtls_pkg.get_emp_dtls( 20, l_dept_rec, l_emp_tbl );
      FOR i IN 1 .. l_emp_tbl.count
      LOOP
        dbms_output.put_line( l_emp_tbl(i).first_name );
      END LOOP;
    END;
    
    Michael
    Pat
    {code}
    {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 7. Re: Query On PL/SQL Collections
    user2626293 Newbie
    Currently Being Moderated
    Dear Justin/Rajesh/Pat,

    Thanks a zillion for your valuable inputs. Actually i wanted to mark all your replies as correct but realised that i can mark any one reply as correct, sorry for that. I have a question, as i have earlier mentioned, this is a POC i have done on a EMPLOYEE table so that i can replicate this in my original logic.

    Now i will start on my original package and i wish to call this package from other packages and even from Oracle form. This will be fesiable right. Please advice.

    Thanks and Regards
    Srinivas
  • 8. Re: Query On PL/SQL Collections
    jeneesh Guru
    Currently Being Moderated
    user2626293 wrote:
    Thanks a zillion for your valuable inputs. Actually i wanted to mark all your replies as correct but realised that i can mark any one reply as correct, sorry for that.
    No need to feel sorry. That is how this forum works..
    >
    Now i will start on my original package and i wish to call this package from other packages and even from Oracle form. This will be fesiable right. Please advice.
    That depends on your actual requirement..You will have to explain in detail..

    Why are you storing these data in collections?
    What you will do after that? Can't you do the same thing by using plain SQL?
  • 9. Re: Query On PL/SQL Collections
    user2626293 Newbie
    Currently Being Moderated
    Hi Pat,

    Actually that package that i will be writing will be accessed even by JAVA team and will also be used by our team. We have used collections because we have to process multiple records at a time. i.e. we wish to pass some 100 values as an input and and receive multiple values as an output and process them further.

    There are other packages that we have to develop which will be using web services to get the infornation from a different system. I am not aware of how to use the web services in PL/SQL, but have to explore it.

    Thanks and Regards
    Srinivas
  • 10. Re: Query On PL/SQL Collections
    user2626293 Newbie
    Currently Being Moderated
    Hi Pat,

    I have a question for you. How is the below statement making a difference.

    --"Below LOOP not required.What you expect this loop to do?
    --FOR i IN l_emp_tbl.first .. l_emp_tbl.last
    --LOOP

    Actually i want to loop through all the records through this loop, but having the cursor loop in place, i think there is no need of the loop that i have written.

    But what if i dont have a cursor loop. How do we handle the situation. Please forgive me if my understanding is incorrect. Please advice me in this scenario.

    Now i am i a situation where i dont have the cursor loop, and i am giving the LOOP mentioned above and initializing the collection variable, but when i try to loop through the records using my anonymous block i am getting the reference to uninitalized collection. Please advice me on this.

    Thanks and Regards
    Srinivas

    Edited by: user2626293 on Feb 6, 2013 4:52 AM
  • 11. Re: Query On PL/SQL Collections
    user2626293 Newbie
    Currently Being Moderated
    Sorry for making this as unanswered, but i have encountered the above situation, that i need to address.

    Edited by: user2626293 on Feb 6, 2013 4:55 AM
  • 12. Re: Query On PL/SQL Collections
    user2626293 Newbie
    Currently Being Moderated
    Dear All,

    Could you please respond to my query above, need your thoughts.

    Thanks and Regards
    Srinivas
  • 13. Re: Query On PL/SQL Collections
    jeneesh Guru
    Currently Being Moderated
    user2626293 wrote:
    Now i am i a situation where i dont have the cursor loop, and i am giving the LOOP mentioned above and initializing the collection variable, but when i try to loop through the records using my anonymous block i am getting the reference to uninitalized collection. Please advice me on this.
    Post your latest code..
  • 14. Re: Query On PL/SQL Collections
    user2626293 Newbie
    Currently Being Moderated
    Hi Pat,

    I will post the code in some time (on the way to office). Thanks for your response.

    Thanks and Regards
    Srinivas
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points