PL/SQL create object inside a anonymous block

Question

I'm new to Oracle SQL DEVELOPER and I can't create a object inside a anonymous block. In this page Oracle Database Object-Relational Developer's Guide

    CREATE TYPE address_typ AS OBJECT ( 
   street          VARCHAR2(30),
   city            VARCHAR2(20),
   state           CHAR(2),
   postal_code     VARCHAR2(6) );
/
CREATE TYPE employee_typ AS OBJECT (
  employee_id       NUMBER(6),
  first_name        VARCHAR2(20),
  last_name         VARCHAR2(25),
  email             VARCHAR2(25),
  phone_number      VARCHAR2(20),
  hire_date         DATE,
  job_id            VARCHAR2(10),
  salary            NUMBER(8,2),
  commission_pct    NUMBER(2,2),
  manager_id        NUMBER(6),
  department_id     NUMBER(4),
  address           address_typ,
  MAP MEMBER FUNCTION get_idno RETURN NUMBER,
  MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY employee_typ ) );
/
CREATE TYPE BODY employee_typ AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN employee_id;
  END;
  MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY employee_typ ) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(first_name || ' '  || last_name);
    DBMS_OUTPUT.PUT_LINE(address.street);
    DBMS_OUTPUT.PUT_LINE(address.city || ', '  || address.state || ' ' ||
                         address.postal_code);   
  END;
END;
/
CREATE TABLE employee_tab OF employee_typ;

And...

--Requires the top code
DECLARE
  emp employee_typ; -- emp is atomically null
BEGIN
-- call the constructor for employee_typ
  emp := employee_typ(315, 'Francis', 'Logan', 'FLOGAN',
        '415.555.0100', '01-MAY-04', 'SA_MAN', 11000, .15, 101, 110, 
         address_typ('376 Mission', 'San Francisco', 'CA', '94222'));
  DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name); -- display details
  emp.display_address();  -- call object method to display details
END;
/

There are 2 separated block of code (Example 3-1 Working With Object Types and Example 3-2 Declaring Objects in a PL/SQL Block). How can i put it all in one anonymous block?

Can't I create the object inside the scope of a anonymous block, use it and them remove it?

Answer 1

If you just need a temporary data structure you can use record type in block level declaration, is less readable and limitated but can works for some simple stuff

declare

  type address_typ is record(
   street          VARCHAR2(30),
   city            VARCHAR2(20),
   state           CHAR(2),
   postal_code     VARCHAR2(6)  
  );

  TYPE employee_typ is record (
    employee_id       NUMBER(6),
    first_name        VARCHAR2(20),
    last_name         VARCHAR2(25),
    email             VARCHAR2(25),
    phone_number      VARCHAR2(20),
    hire_date         DATE,
    job_id            VARCHAR2(10),
    salary            NUMBER(8,2),
    commission_pct    NUMBER(2,2),
    manager_id        NUMBER(6),
    department_id     NUMBER(4),
    address           address_typ/*,
    MAP MEMBER FUNCTION get_idno RETURN NUMBER,
    MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY employee_typ ) */
  );

  emp employee_typ;

begin 
  emp.employee_id := 315;
  emp.first_name := 'Francis';
  emp.last_name := 'Logan';
  --and so on for the others fields

  emp.address.street := '376 Mission';
  emp.address.city := 'San Francisco';
  emp.address.state := 'CA';
  emp.address.postal_code := '94222';

  DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name);
  --no member available so you have to print each field
  DBMS_OUTPUT.PUT_LINE(emp.address.street);
  DBMS_OUTPUT.PUT_LINE(emp.address.city || ', '  || emp.address.state || ' ' ||  emp.address.postal_code);  
end;

Answer 2

you cannot do direct DDL within an anonymous block. You should use dynamic SQL:

declare
  l_sql varchar2(2000);
begin
  l_sql := 'CREATE TYPE address_typ AS OBJECT ( 
   street          VARCHAR2(30),
   city            VARCHAR2(20),
   state           CHAR(2),
   postal_code     VARCHAR2(6) )';

  execute immediate l_sql;

end;

So put each of your "create type" in a dynamic SQL and it should do what you want!