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

    Query On PL/SQL Collections

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