This discussion is archived
2 Replies Latest reply: Nov 14, 2012 1:09 AM by 971895 RSS

PL/SQL Table - Data not displayed

Sid_ Z. Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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;
    /

Legend

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