SET ECHO ON SERVEROUTPUT ON

--
-- plsql12.sql
--

-- Simple demo of running sql against an array instead of a table


-- Create a table which will be joined with a virtual table
DROP   TABLE DROPME ;
CREATE TABLE DROPME (aTerm varchar2(40));
INSERT INTO  DROPME VALUES ('t1');
INSERT INTO  DROPME VALUES ('t2');
INSERT INTO  DROPME VALUES ('t3');
INSERT INTO  DROPME VALUES ('t4');
INSERT INTO  DROPME VALUES ('t5');
INSERT INTO  DROPME VALUES ('t6');

-- First create some *database* objects.  These cannot be PL/SQL objects.

DROP TYPE listOtermsType FORCE;
DROP TYPE aRowType       FORCE;

-- The main reason I create this object is so that I may assign
-- a column name (in this case 'aTerm') to the virtual table I intend to create.
CREATE TYPE aRowType AS OBJECT ( aTerm varchar2(40) )
/

-- Now create a TYPE which allow me to create listOterms
CREATE TYPE listOtermsType AS TABLE OF aRowType
/

-- Now jump into the land of pl/sql
DECLARE

  -- Declare and construct some rows for my virtual table
  xRow1 aRowType := aRowType ('t1');
  xRow2 aRowType := aRowType ('t2');
  xRow3 aRowType := aRowType ('t3');

  -- Declare and construct nested-table which will be my virtual SQL table
  tempTerms listOtermsType := listOtermsType(xRow1,xRow2,xRow3);

  -- I build a REF CURSOR so I may access data in the virtual SQL table
  TYPE cursorType IS REF CURSOR;
  recordSet cursorType;

  i INTEGER;
  tmpTerm VARCHAR2(40);

BEGIN

  -- If I just want to count the rows in my virtual SQL table, I may do that now.
  -- Notice my use of the keywords: TABLE and CAST
  SELECT COUNT(*) INTO i
  FROM TABLE ( CAST (tempTerms AS listOtermsType ));

  -- The above query should count 3 rows
  dbms_output.put_line(i);

  -- If I want to look at the data rather than just count it,
  -- I need to open my REF CURSOR.
  -- To make the demo more interesting I join the virtualSQLtable
  -- with a real table named DROPME.
  OPEN recordSet FOR 
  SELECT virtualSQLtable.aTerm
  FROM TABLE ( CAST (tempTerms AS listOtermsType )) virtualSQLtable,
  DROPME
  WHERE virtualSQLtable.aTerm = DROPME.aTerm;

  -- Now I may look at the data
  LOOP
    FETCH recordSet INTO tmpTerm;
    EXIT WHEN recordSet%NOTFOUND;
    dbms_output.put_line(tmpTerm);
  END LOOP;

END;
/