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.
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;