Help Bulk Collect

[gokden]

Good staff, the problem is the following ....

I have the following loop

Select all

 
 for i in vt_tpfunc.first..vt_tpfunc.last 
   loop 
      vv_string := '  
         select to_number(to_char(max(mp.dt),''j'')) dt_num 
              , '||vt_tpfunc(i)||'((mp.vl + nvl(mp.vl_ajuste,0)))  vl 
              , max(mp.tpmovctr_id)                 tpmovctr_id 
              , max(pm.nro_parc)                    nro_parc 
              , max(pm.tp_parc)                     tp_parc 
              , max(pm.dt_vencto)                   dt_vencto 
           from mov_parc   mp 
              , prev_movto pm 
          where mp.dt           between '''||ed_dt_inic||''' 
                                    and '''||ed_dt_fim||''' 
           and mp.prevmovto_id  = pm.prevmovto_id 
           and pm.contrfinan_id = '||vt_reg_contrfinan.contrfinan_id||' 
           and mp.tpmovctr_id   = '||vt_tipomovctr_id(i)||' 
         group by pm.contrfinan_id'; 
-- 
      execute immediate vv_string 
      bulk collect into vt_dt_num 
                      , vt_vl 
                      , vt_tpmovctr_id 
                      , vt_nro_parc 
                      , vt_tp_parc 
                      , vt_dt_venc; 
-- 
   end loop;  

More in the course of the loop, it goes Inserting the values ??on top ...
That is, at the end of the loop, the vectors do have the values ??of the last result of the loop.
I need it at the end of the loop, the vectors have all the values ??of the loop = D

intendam ??

vlw ....

[Tineks]

And ai gokden, beauty ??

Brow, as you have declared the VARIABLES VT_DT_NUM, VT_TPMOVCTR_ID, VT_NRO_PARC, VT_TP_PARC, VT_DT_VENC ??

I ask the following, for you to fill a variable with the bulk collect you need to create it of the table type, so you can access the values ??through the index, as if it were an array, matrix .. In this case if you did not declare them as a table probably it is assigning the variable the value of the first record found.

Below is an example of how to use the table in that case.

Select all

 
DECLARE 
   TYPE t IS TABLE OF DATE; 
 
   l_t        t; 
   v_select   VARCHAR2 (1000); 
BEGIN 
   v_SELECT := 'SELECT *  
                  FROM (SELECT TRUNC (SYSDATE, ''MM'') + LEVEL - 1 DATA 
                          FROM DUAL 
                       CONNECT BY LEVEL <= (TRUNC (LAST_DAY (SYSDATE)) - TRUNC (SYSDATE, ''MM'') + 1)) dt 
                      ORDER BY dt.DATA'; 
 
   EXECUTE IMMEDIATE v_select 
   BULK COLLECT INTO l_t; 
 
   FOR x IN l_t.FIRST .. l_t.LAST 
   LOOP 
      DBMS_OUTPUT.put_line ('Data : ' || l_t(x) ); 
   END LOOP; 
END; 

Whatever doubts is only to speak. !!

[] 's

[gokden]

Here is the Declaration of Vectors ...

Select all

  vt_dt_num      dbms_sql.number_table; 
  vt_vl          dbms_sql.number_table; 
  vt_tpmovctr_id dbms_sql.number_table; 
  vt_nro_parc    dbms_sql.number_table; 
  vt_tp_parc     dbms_sql.number_table; 
  vt_dt_venc     dbms_sql.date_table;

I believe, then I should give right não é??

[Tineks]

Yes, I should yes, below I changed the code I made to use the same kind of variable, and it worked normally.

Select all

 
DECLARE  
   l_d        dbms_sql.date_table; 
   v_select   VARCHAR2 (1000);  
BEGIN  
   v_SELECT := 'SELECT *  
                  FROM (SELECT TRUNC (SYSDATE, ''MM'') + LEVEL - 1 DATA  
                          FROM DUAL  
                       CONNECT BY LEVEL <= (TRUNC (LAST_DAY (SYSDATE)) - TRUNC (SYSDATE, ''MM'') + 1)) dt  
                      ORDER BY dt.DATA';  
 
   EXECUTE IMMEDIATE v_select  
   BULK COLLECT INTO l_d;  
 
   FOR x IN l_d.FIRST .. l_d.LAST  
   LOOP  
      DBMS_OUTPUT.put_line ('Data : ' || l_d(x) );  
   END LOOP;  
END;  

In the part referring to the insert, how did you do? Did you use the forall? Created a loop and used the index for every position of variable?

[] 's

[gokden]

I just used that bulk collectsi that is in the same code ....
this is my insert ....

[gokden]

good guy the problem is that same ....
he will insert over the value of the vector ....

What I think I was going to work out, it's the Next ....
Ali in Bulk Collect, I put other variables,
ai, just below, from Bulk Collect I speak those vectors receive their value more the value of the other variables. ..
One thing like this ...

Select all

 
execute immediate vv_string 
bulk collect into vv_1 
               , vv_2 
               , vv_3 
               , vv_4 
               , vv_5 
               , vv_6; 
 
vt_dt_num         := vt_dt_num||vv_1; 
vt_vl                 := vt_vl||vv_2; 
vt_tpmovctr_id   := vt_tpmovctr_id||vv_3; 
vt_nro_parc       := vt_nro_parc||vv_4; 
vt_tp_parc         := vt_tp_parc||vv_5; 
vt_dt_venc         := vt_dt_venc||vv_6; 

Is that a way?

[Tineks]

And if you try something like this ..

Select all

 
execute immediate vv_string  
bulk collect into vv_1  
               , vv_2  
               , vv_3  
               , vv_4  
               , vv_5  
               , vv_6;  
 
for x in vv_1.first..vv_2.last loop 
   insert into ... values (vv_1(x), vv_2(x), vv_3(x)...); 
end loop; 

[] 's

[gokden]

so I would not give, because I do not do insert in any table .....

I only use vectors even = /

AI break = /

[gokden]

What I'm not Intendo, it's because that is not recording in the second position of the vector ...
He records only in the first ....

That's why he's giving This whole roll ....

[gokden]

AEWWWWW
I have now been;)

I used that new programming linguargement = D
POG DevelOper (Gambiarra-oriented programming) = x

99] Ai worked out ..
was like this ....

Select all

 
-- 
      execute immediate vv_string 
      bulk collect into vt_dt_num2 
                      , vt_vl2 
                      , vt_tpmovctr_id2 
                      , vt_nro_parc2 
                      , vt_tp_parc2 
                      , vt_dt_venc2; 
-- 
      vt_dt_num(i)      := vt_dt_num2(1); 
      vt_vl(i)          := vt_vl2(1); 
      vt_tpmovctr_id(i) := vt_tpmovctr_id2(1); 
      vt_nro_parc(i)    := vt_nro_parc2(1); 
      vt_tp_parc(i)     := vt_tp_parc2(1); 
      vt_dt_venc(i)     := vt_dt_venc2(1); 
-- 

Brigado for aid there = D

[Tineks]

That's it..!!

has time that we have to use these techniques né .. rs ...

[] 's !!!