CREATE OR REPLACE TYPE  AS OBJECT(
    <nombre columna> <tipo>,
    [ ,]
    . . .
);
/

Por ejemplo:

CREATE OR REPLACE TYPE data_rowtype AS OBJECT ( 
    owner_type         varchar2(5 char),
    owner_code         varchar2(255 char),
    registry_id        varchar2(255 char),
    registry_desc      varchar2(4000 char), 
    parent_id          varchar2(255 char),
    group_ind     varchar2(1 char),
    start_date         date,
    end_date           date
); 
/

Una vez definida la estructura de datos de los registros que manipulará la función PL/SQL, es necesario crear un nuevo tipo de dato relacionado que constituya la estructura de tabla donde se acumulen los valores recuperados. Se tratará de una especie de contenedor de registros que se utilizará como una especie de tabla. La sintaxis para hacerlo es la siguiente:

CREATE OR REPLACE TYPE <nombre tipo> AS TABLE OF <tipo estructura personalizada>; 
/
Para el ejemplo anterior, la declaración correspondiente sería la siguiente:

CREATE OR REPLACE TYPE data_tabletype AS TABLE OF data_rowtype;
/

Implementación de las funciones:

Dentro de los bloques PL/SQL que conforman las funciones de la API es posible ejecutar consultas parametrizadas para poder recuperar valores. Si se quieren recuperar todos de golpe de forma síncrona antes de retornar de la ejecución de la función, se utilizará el mecanismo BULK COLLECT. En el siguiente ejemplo se puede observar cómo una consulta parametrizada utilizando este mecanismo:

FUNCTION findDataRegistryById(ownerType VARCHAR2,
  ownerCode VARCHAR2, 
  registryId VARCHAR2) return data_tabletype
AS
 query_str varchar2(4000 char);
 output_record data_tabletype := data_tabletype();
BEGIN
 query_str := 'select data_rowtype(owner_type, 
            owner_type, 
            owner_code, 
            registry_id, 
            registry_desc, 
            parent_id, 
            group_ind, 
            start_date date, 
            end_date date)
        )
        from table_values
        where owner_type=:ownerType
            and owner_code=:ownerCode
            and registry_id=:registryId';
     
 EXECUTE IMMEDIATE query_str
 BULK COLLECT INTO output_record 
 USING ownerType, ownerCode, registryId;    
  
 RETURN output_record;    
END findDataRegistryById;

Los puntos importantes de la implementación son los siguientes:

• La definición de la sentencia se realizará en un variable de tipo VARCHAR. La definición de los parámetros dentro de esta se hará mediante la sintaxis :nombre_parametro
• Los valores recuperados por la sentencia se encapsularán en un objeto del tipo asociado al TYPE definido cómo AS TABLE OF del retorno, en el ejemplo data_rowtype y data_tabletype respectivamente.
• La ejecución de la sentencia se realizará a través de las instrucción EXECUTE IMMEDIATE
• Al utilizar la instrucción BULK COLLECT se podrán especificar los parámetros para la ejecución de la sentencia especificándolos usando la cláusula USING. Se deberá especificar el mismo número de parámetros y en el mismo orden que en la definición de la consulta. En el ejemplo ownerType, ownerCode y registryId
• La función retornará un valor con el TYPE definido como AS TABLE OF asociado a la estructura de datos, en el ejemplo data_tabletype.

La técnica del BULK COLLECT presenta el inconveniente de bloquear la ejecución de la función hasta no haber recuperado todos los valores resultantes de la consulta. Esto la hace apropiada sólo para los casos en que el volumen de datos a recuperar es bajo. Oracle proporciona un mecanismo más eficiente para retornar grandes volúmenes de resultados: Las pipelined functions. Este tipo de funciones permite retornar los resultados obtenidos de forma incremental de forma asíncrona, haciendo que no sea necesario retornar el control de la ejecución desde el código que la ejecuta para empezar a procesar los resultados. Esto lo hace muy práctico para tratar grandes conjuntos de datos ya que proporciona un grado de paralelismo mayor. Un ejemplo de implementación de este mecanismo sería el siguiente:

FUNCTION findDataRegistries(ownerType VARCHAR2,
  ownerCode VARCHAR2, 
  registryId VARCHAR2) return data_tabletype
PIPELINED IS
 query_str varchar2(4000 char);
    TYPE record_cursor_type IS REF CURSOR;
    record_cursor record_cursor_type;
 output_record data_rowtype;
BEGIN
 query_str := 'select data_rowtype(owner_type, 
            owner_type, 
            owner_code, 
            registry_id, 
            registry_desc, 
            parent_id, 
            group_ind, 
            start_date date, 
            end_date date)
        )
        from table_values
        where owner_type=:ownerType
            and owner_code=:ownerCode';
     
    OPEN record_cursor FOR query_str USING ownerType, ownerCode;
    LOOP
        FETCH record_cursor INTO output_record;
        EXIT WHEN record_cursor%NOTFOUND;
        PIPE ROW(output_record);
    END LOOP;
    CLOSE record_cursor;
  
    RETURN;    
END findDataRegistries;

Los puntos importantes de la implementación son los siguientes:

• Se incluirá la palabra reservada PIPELINED en la definición de la función
• La definición de la sentencia se realizará en un variable de tipo VARCHAR. La definición de los parámetros dentro de esta se hará mediante la sintaxis :nombre_parametro
• Los valores recuperados por la sentencia se encapsularán en un objeto del tipo asociado al TYPE definido cómo AS TABLE OF del retorno, en el ejemplo data_rowtype y data_tabletype respectivamente.
• Se utilizará un bucle para recorrer un cursor con los datos recuperados y para cada iteración se ejecutará la instrucción PIPE ROW para retornar de forma asíncrona los datos recuperados (output_record).
• Al abrir el cursor se podrán especificar los parámetros para la ejecución de la sentencia especificándolos usando la cláusula USING. Se deberá especificar el mismo número de parámetros y en el mismo orden que en la definición de la consulta.
• Una vez concluido el bucle, esto es cuando se cumpla la condición EXIT WHEN record_cursor%NOTFOUND porque no haya mas datos en el cursor, este será cerrado.
• La función no retornará valor alguno dado que esta función se realiza mediante la llamada a PIPE ROW.

DECLARE
    . . .
BEGIN
    . . .
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        -- Tratar error cuando se retornen demasidos valores...        
        dbms_output.put_line(SQLCODE || ': ' || SQLERRM); -- Log
    WHEN OTHERS
        -- Tratar errores de otro tipo
        dbms_output.put_line(SQLCODE || ': ' || SQLERRM); -- Log
END;

FUNCTION findRegistryById(ownerType VARCHAR2,
  ownerCode VARCHAR2, 
  registryId VARCHAR2) return data_tabletype
 AS
  . . .
 BEGIN
  -- Validaciones
  IF ownerType is null THEN
   raise_application_error(-20001, 
                            'El tipo de propietario es obligatorio');
  ELSIF ownerType not in('REF', 'PROV') THEN
   raise_application_error(-20002, 
                            'El tipo de propietario no es válido');
  ELSIF registryId is null THEN
   raise_application_error(-20003, 
                            'El código del registro es obligatorio');
  END IF;

                . . .

END findRegistryById;

CREATE OR REPLACE PACKAGE BODY dataRetrievalPkg AS

FUNCTION findDataRegistryById(ownerType VARCHAR2, 
    ownerCode VARCHAR2, 
registryId VARCHAR2) return data_tabletype
BEGIN
    . . . 
END findDataRegistryById;

FUNCTION findAllDataRegistries(ownerType VARCHAR2, 
    ownerCode VARCHAR2) return data_tabletype
BEGIN
    . . .
END findAllDataRegistries;

END dataRetrievalPkg;
/

select data_owners.name, t.* 
from table(dataRetrievalPkg.findDataRegistryById('REF', null, 'ID00001')) t;

select t.* 
from table(dataRetrievalPkg.findAllDataRegistries('PROV', 'ID00001')) t,
    data_owners
where t.owner_type = data_owners.type
    and t.owner_code = data_owners.owner_code;