1

Topic: Collection in request

Hello!
Stick with a nose - I can not find explanations to an event...

drop table smp_tmp;
create table smp_tmp (dat date);
insert into smp_tmp
select add_months (trunc (sysdate, ' year '), level-1) from dual connect by level <13;
commit;
declare
type am_type is table of varchar2 (200) index by varchar2 (100);
v_am am_type;
begin
v_am (' 1/1/2017 '): = ' "1/1/2017 ' ' as January ';
v_am (' 2/1/2017 '): = ' "2/1/2017 ' ' as February ';
v_am (' 3/1/2017 '): = ' "3/1/2017 ' ' as March ';
v_am (' 4/1/2017 '): = ' "4/1/2017 ' ' as April ';
v_am (' 5/1/2017 '): = ' "5/1/2017 ' ' as May ';
v_am (' 6/1/2017 '): = ' "6/1/2017 ' ' as June ';
v_am (' 7/1/2017 '): = ' "7/1/2017 ' ' as July ';
v_am (' 8/1/2017 '): = ' "8/1/2017 ' ' as August ';
v_am (' 9/1/2017 '): = ' "9/1/2017 ' ' as September ';
v_am (' 10/1/2017 '): = ' "10/1/2017 ' ' as October ';
v_am (' 11/1/2017 '): = ' "11/1/2017 ' ' as November ';
v_am (' 12/1/2017 '): = ' "12/1/2017 ' ' as December ';
for i in
(
with tab as (select to_char (d.dat, ' dd.mm.yyyy ') ff, d.dat dat from smp_tmp d)
select t.dat, t.ff;
--v_am (t.ff) res - not work
--v_am (to_char (trunc (sysdate, ' month '), ' dd.mm.yyyy ')) work_
from tab t
)
loop
dbms_output.put_line (i.ff);
dbms_output.put_line (v_am (i.ff)); - work
end loop;
end;

Actually a subject...
Thanks!

2

Re: Collection in request

wellvk wrote:

Hello!
Stick with a nose - I can not find explanations to an event...

declare
type am_type is table of varchar2 (200) index by varchar2 (100);
v_am am_type;
begin
v_am (' 1/1/2017 '): = ' "1/1/2017 ' ' as January ';
v_am (' 2/1/2017 '): = ' "2/1/2017 ' ' as February ';
v_am (' 3/1/2017 '): = ' "3/1/2017 ' ' as March ';
v_am (' 4/1/2017 '): = ' "4/1/2017 ' ' as April ';
v_am (' 5/1/2017 '): = ' "5/1/2017 ' ' as May ';
v_am (' 6/1/2017 '): = ' "6/1/2017 ' ' as June ';
v_am (' 7/1/2017 '): = ' "7/1/2017 ' ' as July ';
v_am (' 8/1/2017 '): = ' "8/1/2017 ' ' as August ';
v_am (' 9/1/2017 '): = ' "9/1/2017 ' ' as September ';
v_am (' 10/1/2017 '): = ' "10/1/2017 ' ' as October ';
v_am (' 11/1/2017 '): = ' "11/1/2017 ' ' as November ';
v_am (' 12/1/2017 '): = ' "12/1/2017 ' ' as December ';
for i in
(
with tab as (select to_char (d.dat, ' dd.mm.yyyy ') ff, d.dat dat from smp_tmp d)
select t.dat, t.ff>>>, <<<
--v_am (t.ff) res - not work
--v_am (to_char (trunc (sysdate, ' month '), ' dd.mm.yyyy ')) work_
from tab t
)
loop
dbms_output.put_line (i.ff);
dbms_output.put_line (v_am (i.ff)); - work
end loop;
end;

3

Re: Collection in request

v_am (t.ff) res - does not work due to correlated argument "t.ff"
v_am (to_char (trunc (sysdate, ' month '), ' dd.mm.yyyy ')) works - due to there is no correlated argument (it is equivalent to literal argument)

4

Re: Collection in request

wellvk wrote:

v_am (to_char (trunc (sysdate, ' month '), ' dd.mm.yyyy '))

It is calculated PL/SQL and it is replaced on:B1

wellvk wrote:

v_am (t.ff)

cannot be calculated neither PL/SQL, nor SQL - it is not known what element of a collection.

5

Re: Collection in request

Elic;
I correctly understand, what the collection should be declared at circuit level?

6

Re: Collection in request

wellvk wrote:

I correctly understand, what the collection should be declared at circuit level?

Is not present.
At first, in SQL there are the associative arrays, and furthermore no such.
Secondly, even if bind-it a collection entirely, SQL is not able to address to its element on an index.
How PL/SQL Resolves Identifier Names: Resolution of Names in Static SQL Statements

7

Re: Collection in request

it is better so:
How PL/SQL Resolves Identifier Names: Resolution of Names in Static SQL Statements

8

Re: Collection in request

wellvk;
And what not so?
12-ka

...
dbms_output.put_line (i.ff || ' - ' || v_am (i.ff)); - work
...
1/1/2017 - ' 1/1/2017 ' as January
2/1/2017 - ' 2/1/2017 ' as February
3/1/2017 - ' 3/1/2017 ' as March
4/1/2017 - ' 4/1/2017 ' as April
5/1/2017 - ' 5/1/2017 ' as May
6/1/2017 - ' 6/1/2017 ' as June
7/1/2017 - ' 7/1/2017 ' as July
8/1/2017 - ' 8/1/2017 ' as August
9/1/2017 - ' 9/1/2017 ' as September
10/1/2017 - ' 10/1/2017 ' as October
11/1/2017 - ' 11/1/2017 ' as November
12/1/2017 - ' 12/1/2017 ' as December
Statement processed.
0.01 seconds

....
stax

9

Re: Collection in request

Stax wrote:

and what not so?

Stanislav, act in film from a bench hammer.

10

Re: Collection in request

Elic wrote:

it is passed...
Stanislav, act in film from a bench hammer.

Works like
Or a question in commented out?
--v_am (t.ff) res - not work
--v_am (to_char (trunc (sysdate, ' month '), ' dd.mm.yyyy ')) work_
....
stax