Home » SQL & PL/SQL » SQL & PL/SQL » Flush\ purge values from Array (Oracle 10g, Win XP)
Flush\ purge values from Array [message #307135] Mon, 17 March 2008 23:21 Go to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
Hi all.

I need help for one Issue I am facing

Following code I used to create the Table type as t_n_dept_id
and then created variable n_dept_id for the same:

TYPE t_n_dept_id IS TABLE OF dept.n_dept_id%TYPE INDEX BY
PLS_INTEGER;

n_dept_id                  t_n_dept_id;


Now I am manipulating this n_dept_id table type array in a loop , but I need to Flush the previous array values at the
begining of each iteration, how to do that?

This is similar dummy code

declare
         CURSOR c1 
         IS Select n_dept_id 
         from Dept
         where rownum < 950;

    v_fetch_limit               NUMBER                             := 100;   
   
    TYPE t_n_dept_id IS TABLE OF dept.n_dept_id%TYPE INDEX BY PLS_INTEGER;
    n_dept_id                  t_n_dept_id;

begin
OPEN c1;

      LOOP
         FETCH c1
         BULK COLLECT INTO
         n_dept_id 
         limit v_fetch_limit;
         
         IF n_dept_id.COUNT > 0
         THEN
          FOR i IN n_dept_id.FIRST .. n_dept_id.LAST
            loop
             
             update Dept
             set Dept_id = n_dept_id(i)*i + 2;
             where dep_no = n_dept_id;
             
            end loop;
                      
            end if;
          EXIT WHEN c1%NOTFOUND;
      END LOOP;

end; 


Please suggest...Its urgent.


from,
freakabhi

[Updated on: Mon, 17 March 2008 23:42]

Report message to a moderator

Re: Flush\ purge values from Array [message #307146 is a reply to message #307135] Tue, 18 March 2008 00:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
It already does that, as demonstrated below.

SCOTT@orcl_11g> declare
  2  	      CURSOR c1
  3  	      IS Select DEPTNO
  4  	      from Dept
  5  	      where rownum < 950;
  6  
  7  	 v_fetch_limit		     NUMBER				:= 2;
  8  
  9  	 TYPE t_n_dept_id IS TABLE OF dept.deptno%TYPE INDEX BY PLS_INTEGER;
 10  	 n_dept_id		    t_n_dept_id;
 11  
 12  begin
 13  OPEN c1;
 14  
 15  	   LOOP
 16  	      FETCH c1
 17  	      BULK COLLECT INTO
 18  	      n_dept_id
 19  	      limit v_fetch_limit;
 20  
 21  	      IF n_dept_id.COUNT > 0
 22  	      THEN
 23  	       FOR i IN n_dept_id.FIRST .. n_dept_id.LAST
 24  		 loop
 25  
 26  		  dbms_output.put_line (n_dept_id(i));
 27  
 28  		 end loop;
 29  
 30  		 dbms_output.put_line ('-----------------');
 31  
 32  		 end if;
 33  	       EXIT WHEN c1%NOTFOUND;
 34  	   END LOOP;
 35  
 36  end;
 37  /
10
20
-----------------
30
40
-----------------

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 


icon11.gif  Re: Flush\ purge values from Array [message #307163 is a reply to message #307146] Tue, 18 March 2008 01:08 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
Hi,

thanx for the reply.

But this is not the exact code...My code is bit different
and too lengthy so I cant put it all.

Only thing I need is can we have someway to flush the previous values.some function like flush()?


from,
Freakabhi
Re: Flush\ purge values from Array [message #307165 is a reply to message #307163] Tue, 18 March 2008 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No. dbms_output only fills a buffer that the client (SQL*Plus) must display and it can do it only when PL/SQL gives it back the control that is at then end of the block execution.

You can use a pipelined function, instead but this is no more an anonymous PL/SQL block.

Regards
Michel
Re: Flush\ purge values from Array [message #307348 is a reply to message #307163] Tue, 18 March 2008 09:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You need to post some code that does not "flush" or replace the old values from the array, so we can see what you are talking about. In general, you can just reinitialize it to an empty set or delete from it.
Re: Flush\ purge values from Array [message #307349 is a reply to message #307165] Tue, 18 March 2008 09:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Mon, 17 March 2008 23:14
No. dbms_output only fills a buffer that the client (SQL*Plus) must display and it can do it only when PL/SQL gives it back the control that is at then end of the block execution.

You can use a pipelined function, instead but this is no more an anonymous PL/SQL block.

Regards
Michel




I believe the op said he wants to "flush" the values from the array, not from the dbms_output buffer. I only used dbms_output to demonstrate that each time through the loop, the newly bulk fetched values do replace the previously fetched values, so there is no need to "flush". However, he has since indicated that the code he posted does not demonstrate the problem, so now we are waiting to see what the real problem is.
Re: Flush\ purge values from Array [message #307353 is a reply to message #307163] Tue, 18 March 2008 09:56 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Perhaps what you are looking for is n_dept_id.delete:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/collections.htm#CJAFGFIG

SCOTT@orcl_11g> 
SCOTT@orcl_11g> declare
  2  	      CURSOR c1
  3  	      IS Select DEPTNO
  4  	      from Dept
  5  	      where rownum < 950;
  6  
  7  	 v_fetch_limit		     NUMBER				:= 2;
  8  
  9  	 TYPE t_n_dept_id IS TABLE OF dept.deptno%TYPE INDEX BY PLS_INTEGER;
 10  	 n_dept_id		    t_n_dept_id;
 11  
 12  begin
 13  OPEN c1;
 14  
 15  	   LOOP
 16  	      FETCH c1
 17  	      BULK COLLECT INTO
 18  	      n_dept_id
 19  	      limit v_fetch_limit;
 20  
 21  	      IF n_dept_id.COUNT > 0
 22  	      THEN
 23  
 24  		-- "flush":
 25  		n_dept_id.delete;
 26  
 27  		dbms_output.put_line (n_dept_id.count);
 28  		dbms_output.put_line ('-----------------');
 29  	      end if;
 30  	      EXIT WHEN c1%NOTFOUND;
 31  	   END LOOP;
 32  
 33  end;
 34  /
0
-----------------
0
-----------------

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

[Updated on: Tue, 18 March 2008 09:57]

Report message to a moderator

Previous Topic: ORA-01653: Unable to extend table
Next Topic: procedure execution halt
Goto Forum:
  


Current Time: Sat Dec 10 22:14:17 CST 2016

Total time taken to generate the page: 0.09044 seconds