2 Replies Latest reply: Nov 14, 2012 3:09 AM by 971895 RSS

    PL/SQL Table - Data not displayed

    Sid_ Z.
      Hi All,

      Below is the code which is not giving error but also not displaying the data. Culd anyone tell me what is wrong in this.
      Data
      
      Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 
      Connected as sizsupport
      
      
      SQL>  SELECT firstname FROM dw_cust_bookings WHERE ROWNUM < 10;
      
      FIRSTNAME
      ----------------------------------------
      Chantelle
      KIRSTINE
      Wendy
      Kath
      Ellen
      Elizabeth
      Sarah
      Karen
      Leanne
      
      9 rows selected
      
      SQL> 
      Code
      
      DECLARE
        TYPE list_of_names_t IS TABLE OF VARCHAR2(100);
      
        happyfamily list_of_names_t ;
        n NUMBER;
      
        CURSOR names_cur IS
          SELECT firstname FROM dw_cust_bookings WHERE ROWNUM < 10;
      BEGIN
        OPEN names_cur;
        n := 1;
        LOOP
          FETCH names_cur BULK COLLECT
            INTO happyfamily LIMIT 1000;
        
          EXIT WHEN happyfamily.COUNT = 0;
          n := n +1;
        END LOOP;
        
        FOR i IN 1 .. happyfamily.COUNT  LOOP
        dbms_output.put_line( happyfamily (i));
        END LOOP;
      END;
        • 1. Re: PL/SQL Table - Data not displayed
          920059
          Did you set the parameter:

          set serveroutput on

          ? When set to off it doesn't prevent select statements from showing data, but dbms_output calls output isn't shown.

          Edited by: apiminov on 14.11.2012 1:07
          • 2. Re: PL/SQL Table - Data not displayed
            971895
            Try like ......

            DECLARE
            TYPE list_of_names_t IS TABLE OF VARCHAR2(100);
            happyfamily list_of_names_t ;
            n NUMBER;

            CURSOR names_cur IS
            SELECT ename FROM emp WHERE ROWNUM < 10;

            BEGIN
            OPEN names_cur;
            n := 1;
            LOOP FETCH names_cur BULK COLLECT INTO happyfamily LIMIT 1000;

            FOR i IN 1 .. happyfamily.COUNT LOOP
            dbms_output.put_line( happyfamily (i));
            END LOOP;
            EXIT WHEN happyfamily.COUNT=0;
            end loop;

            END;
            /