oracle-databasefunctionplsqlassociative-arraypipelined-function

select from table with function using pipelined table functions and associative table


I need to return the table as a result of this select. The argument passed to the function opredelyaet from which an array of associative data will be displayed as a table:

select * from table(task_2.get_con_coll('save'));

I wrote this code and to me it is correct and I do not see that I have missed, or that it is not paved with

Сreates an object

-- the creation of an array type
create or replace type con_coll_type is object(
    id        integer,
    user_name varchar2(255));

Then he created the package

create or replace package task_2 is
  /*
  -- may need to be so?
      type con_coll_type is record(
        id        integer,
        user_name varchar(255));
  -- */
  --Create associative array
  type con_coll_t is table of con_coll_type index by varchar2(255);
  -- need using this   
  function get_con_coll(coll_name varchar2) return con_coll_t
    pipelined;

end task_2;

create or replace package body task_2 is

  function get_con_coll(coll_name varchar2) return con_coll_t
    pipelined is
    indx        varchar(255);
    coll_edit   con_coll_t;
    coll_delete con_coll_t;
    coll_save   con_coll_t;
  begin
-- Filling collection
    coll_edit(1) := con_coll_type(1, 'some_name_1');
    coll_edit(2) := con_coll_type(2, 'some_name_2');
    coll_delete(3) := con_coll_type(3, 'some_name_3');
    coll_delete(4) := con_coll_type(4, 'some_name_4');
    coll_save(5) := con_coll_type(5, 'some_name_5');
    coll_save(6) := con_coll_type(6, 'some_name_6'); 

-- If the parameter is passed to the function "Save" - ​​a collection of output
    if coll_name = 'save' then
      indx := coll_save.first;
      loop
        exit when indx is null;
      -- pipelined output
        pipe row(con_coll_type(coll_save(indx).id,
                               coll_save(indx).user_name));
        indx := coll_save.next(indx);
      end loop;
    end if;

  end get_con_coll;

end task_2;

What's wrong with my code? I can not understand what I missed.


Solution

  • First, the Table type should be out of the package:
    create type con_coll_t is table of con_coll_type;

    And, if you have default values for your types, you can implement them like that:

      coll_delete con_coll_t;
      begin
        coll_delete := con_coll_t(con_coll_type(3, 'some_name_3'),
                                  con_coll_type(4, 'some_name_4'));
      end;
    

    With that being said, I think your general code should be something like that:

     -- the creation of an array type
     create or replace type con_coll_type is object(
         id        integer,
         user_name varchar2(255));
    
     create or replace type con_coll_t is table of con_coll_type;          
    
     create or replace package task_2 is
       function get_con_coll(coll_name varchar2) return con_coll_t
         pipelined;
     end task_2;
    
     create or replace package body task_2 is
    
       function get_con_coll(coll_name varchar2) return con_coll_t
         pipelined is
         indx        varchar(255);
         coll_edit   con_coll_t;
         coll_delete con_coll_t;
         coll_save   con_coll_t;
       begin
     -- Filling collection
         coll_edit    := con_coll_t( con_coll_type(1, 'some_name_1')
    
                                   , con_coll_type(2, 'some_name_2'));
         coll_delete  := con_coll_t( con_coll_type(3, 'some_name_3')
                                   , con_coll_type(4, 'some_name_4'));
         coll_save    := con_coll_t( con_coll_type(5, 'some_name_5')
                                   , con_coll_type(6, 'some_name_6'));
    
     -- If the parameter is passed to the function "Save" - ??a collection of output
         if coll_name = 'save' then
           indx := coll_save.first;
           loop
             exit when indx is null;
           -- pipelined output
             pipe row(con_coll_type(coll_save(indx).id,
                                    coll_save(indx).user_name));
             indx := coll_save.next(indx);
           end loop;
         end if;
    
       end get_con_coll;
    
     end task_2;