Fetching cursor data into array in PL/SQL

Question

I have a problem at one exercise: I have a table with an ussualy column, and another column which is another table, like this:

   CREATE OR REPLACE TYPE list_firstnames AS TABLE OF VARCHAR2(10);

then I create the following table:

   CREATE TABLE persons (last_name VARCHAR2(10), first_name list_firstnames)
   NESTED TABLE first_name STORE AS lista;

I insert:

INSERT INTO persons VALUES('Stewart', list_firstnames('John', 'Jack'));
INSERT INTO persons VALUES('Bauer', list_firstnames('Helen', 'Audrey'));
INSERT INTO persons VALUES('Obrian', list_firstnames('Mike', 'Logan'));

I want to make a cursor to take all last and first names from persons, and then I want to put all of them in an array. After this, I want to count the first names which contains the 'n' letter.

First of all, I want to know how I can put all the information from the cursor in an array. I try this:

DECLARE 
      CURSOR firstnames_students IS
      select last_name,COLUMN_VALUE as "FIRSTNAME" from persons p,         TABLE(p.last_name) p2;
      v_lastname VARCHAR(50);
      v_firstname VARCHAR(50);
      v_I NUMBER := 1;
      v_count NUMBER := 0;
      v_contor INTEGER := 0;
      TYPE MyTab IS TABLE OF persons%ROWTYPE INDEX BY VARCHAR2(20);
      std MyTab;
 BEGIN
     OPEN firstnames_students;
     LOOP
        FETCH firstnames_students INTO v_lastname, v_firstname;
        EXIT WHEN firstnames_students%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_lastname || ' ' || v_firstname);
     END LOOP;
        --select * INTO std FROM firstnames_students; 
     CLOSE firstnames_students;
  END;

MT0 · Accepted Answer · 2016-03-22 22:58:52Z

1

SET SERVEROUTPUT ON;
DECLARE 
  CURSOR students IS
    SELECT * FROM persons;

  v_row   PERSONS%ROWTYPE;
  v_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  v_count INT := 0;
 BEGIN
  OPEN students;
  LOOP
    FETCH students INTO v_row;
    EXIT WHEN students%NOTFOUND;
    v_names.EXTEND;
    v_names(v_names.COUNT) := v_row.last_name;
    FOR i IN 1 .. v_row.first_name.COUNT LOOP
      v_names.EXTEND;
      v_names(v_names.COUNT) := v_row.first_name(i);
    END LOOP;
  END LOOP;
  CLOSE students;

  FOR i IN 1 .. v_names.COUNT LOOP
    IF INSTR( v_names(i), 'n' ) > 0 THEN
      v_count := v_count + 1;
      DBMS_OUTPUT.PUT_LINE( v_names(i) );
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE( v_count );
END;

Output:

John
Helen
Obrian
Logan
4

answered Mar 22, 2016 at 22:58

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Caroso Over a year ago

yes, but how i can count only de firstnames? In this you count the last and first names which contains the 'n' letter. The output should be: Output: The number of words is: 3 --(John, Hellen, Logan) The full names with these first names which contains 'n' is: Stewart John Bauer Hellen Obrian Logan.

MT0 Over a year ago

You could solve it by not putting the last names into the array.(Or you do not even need the array and could just do the count in the cursor loop.)

Collectives™ on Stack Overflow

Fetching cursor data into array in PL/SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related