Live ORACLE

--Cleanup ...........
DROP PACKAGE teams
/

DROP TYPE person_typ
/

DROP TYPE hobbies_var
/

-- Creating ..........
CREATE TYPE person_typ AS OBJECT
(NAME VARCHAR2(25),
age NUMBER
)
/

CREATE TYPE hobbies_var AS VARRAY(10) OF VARCHAR2(25)
/

CREATE OR REPLACE PACKAGE teams
AUTHID current_user
AS
PROCEDURE create_table(tab_name  VARCHAR2);

PROCEDURE insert_row(tab_name  VARCHAR2,p  PERSON_TYP,h  HOBBIES_VAR);

PROCEDURE print_table(tab_name  VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY teams
AS
PROCEDURE Create_table
   (tab_name  VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE '
                  ||tab_name
                  ||' (pers person_typ, hobbs hobbies_var)';
END;

PROCEDURE Insert_row
   (tab_name  VARCHAR2,
    p         PERSON_TYP,
    h         HOBBIES_VAR)
IS
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO '
                  ||tab_name
                  ||' VALUES (:1, :2)'
 USING p,h;
END;

PROCEDURE Print_table
   (tab_name  VARCHAR2)
IS
TYPE refcurtyp IS REF CURSOR;
v_cur  REFCURTYP;
p      PERSON_TYP;
h      HOBBIES_VAR;
BEGIN
OPEN v_cur FOR 'SELECT pers, hobbs FROM '
               ||tab_name;
LOOP
  FETCH v_cur INTO p,h;
  EXIT WHEN v_cur%NOTFOUND;
  -- print attributes of 'p' and elements of 'h'
  dbms_output.Put_line('Name: '
                       ||p.NAME
                       ||' - Age: '
                       ||p.age);
  FOR i IN h.FIRST.. h.LAST LOOP
    dbms_output.Put_line('Hobby('
                         ||i
                         ||'): '
                         ||H(i));
  END LOOP;
END LOOP;
CLOSE v_cur;
END;
END;
/

DECLARE
 team_name  VARCHAR2(15);
BEGIN
 team_name := 'Notables';
 teams.Create_table(team_name);
 teams.Insert_row(team_name,Person_typ('John',31),Hobbies_var('skiing','coin collecting','tennis'));
 teams.Insert_row(team_name,Person_typ('Mary',28),Hobbies_var('golf','quilting','rock climbing','fencing'));
 teams.Print_table(team_name);
END;
/  

TEAMS Package Using Dynamic SQL for Object Types and Collections