1

Topic: To fill in a collection

Good afternoon;
It is necessary to add in collection OFFICEINFO_LAYOUT_ITEM  WORKINGTIME_INFO
[Error] PLS-00231 (814: 48): PLS-00231: function ' PKG_PROCESS.GETOFFICEWORKINGTIME ' cannot be used in SQL
I think, I somehow incorrectly use SELECT * BULK COLLECT INTO arr FROM TABLE
Help ;

FUNCTION GetOfficeWorkingTime (p_officeid number) return WORKINGTIME_INFO pipelined is
--To show office working hours
l_sql varchar2 (1000);
l_day varchar2 (5);
l_work_from varchar2 (5);
l_work_till varchar2 (5);
begin
for l_day in 1. 7 loop
l_sql: = ' select'
||' wd ' || to_char (l_day) || ' _work_from,'
||' wd ' || to_char (l_day) || ' _work_till'
||' from MBL_OFFICES'
||' where id = ' || to_char (p_officeid);
dbg (l_sql);
begin
execute immediate l_sql into l_work_from, l_work_till;
dbg (' l_day = ' || l_day);
dbg (' l_work_from = ' || l_work_from);
dbg (' l_work_till = ' || l_work_till);
pipe row (WORKINGTIME_LAYOUT_ITEM (l_day, l_work_from, l_work_till));
exception
when others then
pipe row (WORKINGTIME_LAYOUT_ITEM (l_day, null, null));
end;
end loop;
end;
FUNCTION GetOfficesInfo return OFFICEINFO_LAYOUT pipelined is
--The information on offices
cursor c_office is
SELECT / * + DRIVING_SITE (ZZZ) */
ID, PHONE, LATITUDE,
CITY, COUNTRY
from mbl_offices zzz
where enabled=1;
type c_office_type is table of c_office%rowtype;
v_res c_office_type;
i number;
arr WORKINGTIME_INFO;
begin
open c_office;
fetch c_office bulk collect into v_res limit 500;
if v_res.count> 0 then
for i in v_res.first. v_res.last loop
SELECT * BULK COLLECT INTO arr FROM TABLE (GetOfficeWorkingTime (v_res (i).id));
pipe row (OFFICEINFO_LAYOUT_ITEM (
v_res (i).id;
v_res (i).phone;
v_res (i).latitude;
v_res (i).city
,arr
));
end loop;
end if;
close c_office;
SaveActivityLog (5, null, null, null, ' OK ');
return;
end;

2

Re: To fill in a collection

Agat22;
Function GetOfficeWorkingTime in the packet specification is declared?
In SQL requests, even in a packet, it is possible to use only the functions declared in the specification.

3

Re: To fill in a collection

Declared, now swears on

SELECT * BULK COLLECT INTO arr FROM TABLE (GetOfficeWorkingTime (v_res (i).id));

[Error] ORA-00947 (814: 37): PL/SQL: ORA-00947: there are no values for the data

4

Re: To fill in a collection

Agat22;
And it is exact for this line, instead of on the following?

5

Re: To fill in a collection

Precisely, when it I remove - all

6

Re: To fill in a collection

Agat22 wrote:

...

FUNCTION GetOfficeWorkingTime (p_officeid number) return WORKINGTIME_INFO pipelined is
--To show office working hours
l_sql varchar2 (1000);
l_day varchar2 (5);
l_work_from varchar2 (5);
l_work_till varchar2 (5);
begin
for l_day in 1. 7 loop
l_sql: = ' select'
||' wd ' || to_char (l_day) || ' _work_from,'
||' wd ' || to_char (l_day) || ' _work_till'
>>> || ' from MBL_OFFICES' <<<
>>> || ' where id = ' <<<|| to_char (p_officeid);
.......

Whether instead of to add to you  between the table and where?

7

Re: To fill in a collection

Already found and added, but it did not solve a problem certainly

8

Re: To fill in a collection

What you here try to make?

pipe row (WORKINGTIME_LAYOUT_ITEM (l_day, null, null));
pipe row (OFFICEINFO_LAYOUT_ITEM (
v_res (i).id;
v_res (i).phone;
v_res (i).latitude;
v_res (i).city
,arr
))

The operator pipe row returns a line under number i.
That is it would be correct so:

pipe row (WORKINGTIME_LAYOUT_ITEM (l_day));

Where l_day - an integral type variable
And you that do?
What for a mad enclosure?
To fill  a collection it is possible:

TYPE in_array IS RECORD (a NUMBER (10);
b NUMBER (10));
TYPE out_array IS RECORD (c NUMBER (10);
d in_array);
var out_array: = out_array ();
var. EXTEND;
FOR i IN var. FIRST. var. LAST
LOOP
var (i).d. EXTEND (10);
FOR j IN var (i).d. FIRST. var (i).d. LAST
LOOP
var (i).d (j).a = 1;
var (i).d (j).b = 2;
END LOOP;
END LOOP;

1. Rewrite collection filling through cycles, instead of through BULK COLLECT.
2. Simplify structure of objects.