Home » SQL & PL/SQL » SQL & PL/SQL » Append collection variable (10g,11g)
Append collection variable [message #568357] Thu, 11 October 2012 02:42 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hi All,

please help


Declare
Cursor c1...;
Cursor c2...;
begin
open c1 ;
fecth c1 bulk collect into v1;
close c1;

If condition then
open c2;
fetch c2 bulk collect into v1;
close c2;
end;
/

Is there any way by which if condition gets true then v1 gets appended rather than being overwritten?


declare
type lst_deptno is table of dept.deptno%type index by binary_integer;
type lst_deptno_emp is table of emp.deptno%type index by binary_integer;
v_deptno lst_deptno;
v_deptno_emp lst_deptno_emp;
cursor c1 is select deptno from dept;
cursor c2 is select deptno from emp;
begin
open c1;
fetch c1 bulk collect into v_deptno;
close c1;

dbms_output.put_line('deptno in dept_1='||v_deptno.count);
open c2;
fetch c2 bulk collect into v_deptno;
close c2;
dbms_output.put_line('deptno in dept_1='||v_deptno.count);

end ;
/

Actual O/P: deptno in dept_1=4
            deptno in dept_1=14

Desired O/P:deptno in dept_1=4
            deptno in dept_1=18





[Updated on: Thu, 11 October 2012 02:48]

Report message to a moderator

Re: Append collection variable [message #568363 is a reply to message #568357] Thu, 11 October 2012 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe be this will help:
SQL> declare 
  2    v_deptno     sys.odcinumberlist;
  3    v_deptno_emp sys.odcinumberlist;
  4    v3           sys.odcinumberlist;
  5    cursor c1 is select deptno from dept;
  6    cursor c2 is select deptno from emp;
  7  begin
  8  
  9    open c1;
 10    fetch c1 bulk collect into v_deptno;
 11    close c1;
 12    dbms_output.put_line('deptno in dept_1='||v_deptno.count);
 13  
 14    open c2;
 15    fetch c2 bulk collect into v_deptno_emp;
 16    close c2;
 17  
 18    select * bulk collect into v3 from (
 19       select * from table(v_deptno_emp) union all select * from table(v_deptno)
 20    );
 21    dbms_output.put_line('deptno in dept_1='||v3.count);
 22  
 23  end ;
 24  /
deptno in dept_1=4
deptno in dept_1=18

PL/SQL procedure successfully completed.

Regards
Michel
Re: Append collection variable [message #568364 is a reply to message #568357] Thu, 11 October 2012 03:04 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Can you explain

Quote:
Desired O/P:deptno in dept_1=4
deptno in dept_1=18
Re: Append collection variable [message #568370 is a reply to message #568364] Thu, 11 October 2012 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The labels are wrong/meaningless.
The second one is the sum (union) of the 2 queries 14+4=18.

Regards
Michel
Re: Append collection variable [message #568386 is a reply to message #568370] Thu, 11 October 2012 05:01 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thx Michel and Murali for spending your precious times

I was looking for some magical pl/sql option, with 1 word tada..... ,but i guess rudimentary loop are at rescue


declare
type lst_deptno is table of dept.deptno%type index by binary_integer;
type lst_deptno_emp is table of emp.deptno%type index by binary_integer;
v_deptno lst_deptno;
v_deptno_emp lst_deptno_emp;
cursor c1 is select deptno from dept;
cursor c2 is select deptno from emp;
l_count integer:=0;
begin
open c1;
fetch c1 bulk collect into v_deptno;
close c1;

dbms_output.put_line('deptno in dept_1='||v_deptno.count);
--v_deptno.extend();
--dbms_output.put_line('deptno in dept_2='||v_deptno.count);
open c2;
fetch c2 bulk collect into v_deptno_emp;
close c2;
dbms_output.put_line('deptno in dept_emp='||v_deptno_emp.count);
l_count:=v_deptno_emp.count;
dbms_output.put_line(l_count);
dbms_output.put_line(v_deptno_emp(1));
for i in 1..v_deptno.count loop
v_deptno_emp(l_count + i):= v_deptno(i);
--dbms_output.put_line(v_deptno_emp(l_count + i));
end loop;
--dbms_output.put_line('deptno in dept_emp_1='||v_deptno_emp.count);
for i in 1..v_deptno_emp.count loop
dbms_output.put_line('v_deptno_emp('|| i ||') = ' || v_deptno_emp(i));
end loop;
end ;
/


Re: Append collection variable [message #568392 is a reply to message #568386] Thu, 11 October 2012 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please INDENT the code, this is unreadable.
Your way is a slow by slow one, I thought you wanted more efficient.

Regards
Michel
Re: Append collection variable [message #568415 is a reply to message #568386] Thu, 11 October 2012 06:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2007
Registered: January 2010
Senior Member
rishwinger wrote on Thu, 11 October 2012 06:01
I was looking for some magical pl/sql option


SQL> create or replace
  2    type NumList
  3      as table of number
  4  /

Type created.

SQL> set serveroutput on
SQL> declare
  2      v_deptno     NumList;
  3      v_deptno_emp NumList;
  4      cursor c1 is select deptno from dept;
  5      cursor c2 is select deptno from emp;
  6  begin
  7      open c1;
  8      fetch c1 bulk collect into v_deptno;
  9      close c1;
 10      dbms_output.put_line('deptno in dept = ' || v_deptno.count);
 11      open c2;
 12      fetch c2 bulk collect into v_deptno_emp;
 13      close c2;
 14      dbms_output.put_line('deptno in emp = ' || v_deptno_emp.count);
 15      v_deptno := v_deptno multiset union all v_deptno_emp;
 16      dbms_output.put_line('deptno in dept + emp = ' || v_deptno.count);
 17  end;
 18  /
deptno in dept = 4
deptno in emp = 14
deptno in dept + emp = 18

PL/SQL procedure successfully completed.

SQL> 


SY.

Re: Append collection variable [message #568417 is a reply to message #568415] Thu, 11 October 2012 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Doh! This is why it didn't work with me, sys.odcinumberlist is a VARRAY of numbers and not a TABLE of numbers.

Regards
Michel
Re: Append collection variable [message #577715 is a reply to message #568357] Tue, 19 February 2013 17:43 Go to previous message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi ,

When I commented the statement " Exit When C1%Notfound;" and placed it at the end of block.

Its inserting records.



Loop
    Begin
      Fetch C1 Bulk Collect
        Into t_Rtl_Tbl_1 Limit 10000;
    --  Exit When C1%Notfound;

      Forall Indx In 1 .. t_Rtl_Tbl_1.Count Save Exceptions

                         --Update Dwi_Rtl_Sls_Retrn_Line_bkp for Item_Cost_Amt values from cusror where Item_Cost_Amt Is Null
         
Insert Into temp (Sku_Item_Key,Locationno,Bsns_Unit_Key, Item_Cost_Amt ) Values (t_Rtl_Tbl_1(Indx).Sku_Item_Key,t_Rtl_Tbl_1(Indx).Locationno,
t_Rtl_Tbl_1(Indx).Bsns_Unit_Key,t_Rtl_Tbl_1(Indx).Cost_Cal_Amt);                
       /* Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp t
           Set t.Item_Cost_Amt = t_Rtl_Tbl_1(Indx).Cost_Cal_Amt
         Where t.Sku_Item_Key = t_Rtl_Tbl_1(Indx).Sku_Item_Key
           And t.Locationno = t_Rtl_Tbl_1(Indx).Locationno
           And t.Bsns_Unit_Key = t_Rtl_Tbl_1(Indx).Bsns_Unit_Key
           And t.Item_Cost_Amt Is Null;*/
 a:=t_Rtl_Tbl_1.Count;
 Insert Into temp2 Values (a);
    Exception
      When Ex_Dml_Errors Then
        l_Error_Count1 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count1: ' ||
                             l_Error_Count1);
        For i In 1 .. l_Error_Count1 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
        When Others Then
          emesg := SQLERRM;
          ecode := SQLCODE;
          dbms_output.put_line(ecode||' '||emesg);
    End;
     Exit When C1%Notfound;
    Commit;
  End Loop;
  emesg:=v_Sql || ' ' || ' and a.Item_Cost_Amt is null';
  Close C1;



When it has records why its exiting from the block.
Previous Topic: Updates by Week
Next Topic: Bulk Collect & For all in PL/SQL
Goto Forum:
  


Current Time: Sat Aug 30 19:45:27 CDT 2014

Total time taken to generate the page: 0.15272 seconds