SET ECHO         ON
SET SERVEROUTPUT ON
SET ARRAYSIZE    1

--
-- plsql10.txt
--

-- Simple PL/SQL demo of turning a PL/SQL Table into
-- a virtual SQL table which can then be SELECTed from.

-- Notice that myTabtype is a **database** object not a PL/SQL object
CREATE OR REPLACE TYPE myTabtype AS TABLE OF VARCHAR2(64)
/

-- The above syntax works on both UNIX and Windows
-- On Windows the syntax below does not work (for me):
-- CREATE OR REPLACE TYPE myTabtype AS TABLE OF VARCHAR2(64);
--

-- Now create PL/SQL objects
DECLARE

  aNumber NUMBER;

  dNestedTable myTabtype;

BEGIN

 -- Construct the nested table
 dNestedTable:= myTabtype
                  (
                  'ALL_CLUSTERS'    
  		  ,'DBA_CLUSTERS'   
  		  ,'USER_CLUSTERS'
                  );

  -- Drum roll please...
  -- Notice my use of the keywords: TABLE and CAST
  SELECT COUNT(*) INTO aNumber
  FROM TABLE ( CAST (dNestedTable AS myTabtype));

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

END;
/

-- End of demo