Accesing inside elements of nested table inside nested table

Question

I have this code

set serveroutput on

declare
  contador pls_integer;
begin
  select count(*) into contador from user_objects where object_name=upper('Matriculación');
  if contador = 1 then
    execute immediate 'drop table Matriculación';
  end if;
  select count(*) into contador from user_objects where object_name=upper('Matrícula');
  if contador = 1 then
    execute immediate 'drop table Matrícula';
  end if;
  select count(*) into contador from user_objects where object_name=upper('AlumnoObj');
  if contador = 1 then
    execute immediate 'drop type AlumnoObj';
  end if;
  select count(*) into contador from user_objects where object_name=upper('TitulaciónObj');
  if contador = 1 then
    execute immediate 'drop type TitulaciónObj';
  end if;
  select count(*) into contador from user_objects where object_name=upper('TablaAsignatura');
  if contador = 1 then
    execute immediate 'drop type TablaAsignatura';
  end if;
  select count(*) into contador from user_objects where object_name=upper('AsignaturaObj');
  if contador = 1 then
    execute immediate 'drop type AsignaturaObj';
  end if;
end;
/

CREATE TYPE AsignaturaObj AS OBJECT(
  idAsignatura    varchar2(6),
  nombre          varchar2(50),
  curso           number(1,0), -- en el original curso y cuatrimestre son varchar2, lo he cambiado por probar
  cuatrimestre    number(1,0),
  créditos        number(3,1),
  coste           number(6,2), -- no necesitamos un número de 16 dígitos como en el original
  idProfesor      varchar2(4)
);
/

CREATE OR REPLACE TYPE TablaAsignatura AS TABLE OF AsignaturaObj;
/

show errors
CREATE TYPE TitulaciónObj AS OBJECT(
  idTitulación    varchar2(6),
  nombre          varchar2(30),
  asignaturas     TablaAsignatura
) ;--NESTED TABLE asignaturas STORE AS Asignatura_Tabla return as value; -- esto NO
/

CREATE TYPE AlumnoObj AS OBJECT(
  dni             varchar2(9),
  idAlumno        varchar2(7),
  nombre          varchar2(20),
  apellido        varchar2(30),
  dirección       varchar2(35), -- calle {coma espacio} número --> 30+2+3=35
  ciudad          varchar2(20),
  teléfono        varchar2(9)
);
/

-- El objetivo es guardar una tabla que contenga por cada alumno cada titulación en la que esté, y por cada titulación las asignaturas en las que está matriculado
-- Nota: si un alumno está matriculado en asignaturas de varias titulaciones habrá una fila por cada titulación
CREATE TABLE Matriculación (
  num_matrícula   number(5,0)     CONSTRAINT Matriculación_PK PRIMARY KEY,
  alumno          AlumnoObj       CONSTRAINT Matriculación_ALUMNO_NN NOT NULL,
  titulación      TitulaciónObj
) NESTED TABLE titulación.asignaturas STORE AS Asignatura_Tabla;

set serveroutput on

declare
  alumno alumnoObj := alumnoObj('12345678A', 'A000000', 'Juan', 'Pérez Quintanilla', 'Calle Quintana, 23', 'Madrid', '913217712');

  asignatura1 asignaturaObj := asignaturaObj('222222', 'Pulpos', 2, 1, '4,5', 120, 'Q000');
  asignatura2 asignaturaObj := asignaturaObj('222223', 'Pólipos', 2, 1, '3', 80, 'Q000');
  titulación titulaciónObj := titulaciónObj('111111', 'Ciencias del Mar', TablaAsignatura(asignatura1, asignatura2));

begin
  insert into matriculación values (contador, alumno, titulación );
end;
/

it creates a table "Matriculación" (enrollment) that contains several objects, one of them is the object "titulación" (university degree) of the type "TitulaciónObj". This object contains itself a table of objects of the type "AsignaturaObj" (subject objects).

My question is, after I create a row for the "Matriculación" table, how can I access the degree objects (TitulaciónObj) as well as its inner subjects (asignaturas)?

Something like

select num_matrícula, alumno, titu.* from matriculación m,  table(m.titulación) titu;

provokes an ORA-22905: "cannot access rows from a non-nested table item"

Many thanks in advance

Answer 1

You need to reference the nested table when you use TABLE() rather than the containing object:

SELECT m.num_matrícula,
       m.alumno,
       m.titulación.idTitulación,
       m.titulación.nombre,
       a.*
FROM   matriculación m,
       TABLE(m.titulación.asignaturas) a;