CODEWITHSUNDEEP
oracle-databaseplsql
user15746603
user15746603

Reputation:

Define custom type in package

I have the below type:

create or replace TYPE t3_data AS OBJECT (
    vk   NVARCHAR2(12),
    pl   VARCHAR2(2000),
    dt   NUMBER,
    ay   CHAR(10),
    ao   CHAR(10),
    an   VARCHAR2(4000),
    r    NUMBER,
    c    CHAR(10)
);

Which I use in a function like the below:

FUNCTION pick_values RETURN t3_table PIPELINED

The relation between t3_data and t3_table is the below:

create or replace TYPE t3_table IS
    TABLE OF t3_data;

Is there a way to define t3_data and t3_table inside a package in which I already have the function defined?

Upvotes: 0

Views: 1280

Answers (1)

Justin Cave
Justin Cave

Reputation: 231711

Object types are SQL types so they cannot be c reated in PL/SQL. You can, however, define a record type in a package rather than an object type and you should be able to use that record type to define a pipelined table function

create or replace package test_pkg
as
  type t3_data_rec is record (
    vk   NVARCHAR2(12),
    pl   VARCHAR2(2000),
    dt   NUMBER,
    ay   CHAR(10),
    ao   CHAR(10),
    an   VARCHAR2(4000),
    r    NUMBER,
    c    CHAR(10)
  );
  
  type t3_table is table of t3_data_rec;
  
  function pick_values 
    return t3_table pipelined;
end;
/

create or replace package body test_pkg
as
  function pick_values 
    return t3_table pipelined
  is
    l_rec t3_data_rec;
  begin
    l_rec.vk := N'foo';
    pipe row( l_rec );
  end;
end;
/

select *
  from table( test_pkg.pick_values );

Upvotes: 1

Related Questions