473,320 Members | 1,957 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

bulk collect and for all..........

can someone explain clearly what is bulk collect and for all..........
Feb 29 '08 #1
2 2575
amitpatel66
2,367 Expert 2GB
can someone explain clearly what is bulk collect and for all..........
Check out here .
Feb 29 '08 #2
can someone explain clearly what is bulk collect and for all..........
Expand|Select|Wrap|Line Numbers
  1.  
  2. create type obj as object(x number)
  3.     create table objtable of obj
  4. /  
  5.  
  6.     declare 
  7.      type obj1 is table of obj; 
  8.      z obj1:=obj1(obj(1),obj(2),obj(3),obj(4)); 
  9.      type obj2 is varray(5) of integer; 
  10.      x obj2:=obj2(1,2,3); 
  11.      begin 
  12.      forall i in z.first..z.last 
  13.       insert into objtable values(z(i));--Inserts the values in table of objects objtable. 
  14.  forall i in x.first..x.last 
  15.      insert into emp_table values(x(i),'amit');-----Inserts the values in emp_table 
  16.      forall i1 in x.first..x.last 
  17.      delete emp_table where num=x(i1);----Deletes all the records of emp_table 
  18.      if sql%bulk_rowcount(2)=0       then 
  19.      dbms_output.put_line('value does not exist'); 
  20.          else 
  21.      dbms_output.put_line('value exist'|| sql%bulk_rowcount(2)); 
  22.      end if; 
  23.      forall i2 in x.first..x.last 
  24.      update emp_table set num=x(i2) where num=x(i2); 
  25.  if sql%bulk_rowcount(2)=0 then-------------------------A 
  26.        dbms_output.put_line('value does not exist'); 
  27.      end if; 
  28.      end; 
  29.  
  30. when we user cursor then
  31.  
  32. create table emp_tab4(empno number, ename varchar2(20),salary number)
  33. insert into emp_tab4 values(1,'anil',1000)
  34. insert into emp_tab4 values(2,'sunil',2000)
  35.  
  36. declare 
  37.         type eno is table of number; 
  38.         type ename is table of varchar2(20); 
  39.         x eno; 
  40.         y ename; 
  41.         cursor c1 is select empno,ename from emp_tab4 WHERE salary > 1000; 
  42.        rows natural:=2; 
  43.        cnt number:=0; 
  44.        begin 
  45.        select empno,ename BULK COLLECT into x , y from emp_tab4;--A 
  46.        open c1; 
  47.         fetch c1  BULK COLLECT into x , y; 
  48.  
  49.     for i in x.first..x.last loop 
  50.          dbms_output.put_line(x(i)||'  '||y(i)); 
  51.         end loop; 
  52.        close c1; 
  53.        open c1; 
  54.            fetch c1  BULK COLLECT into x , y LIMIT rows;---------B 
  55.            dbms_output.put_line('Limits the no of rows'); 
  56.           for i in x.first..x.last loop 
  57.              dbms_output.put_line(x(i)||'  '||y(i)); 
  58.               end loop; 
  59.       close c1; 
  60.            delete emp_tab4 where ename='sunil'------------------------C 
  61.             returning empno  BULK COLLECT into x; 
  62.  
  63.    for i in x.first..x.last loop 
  64.           cnt:=cnt+1; 
  65.           end loop; 
  66.           dbms_output.put_line('the no of rows affected'||' '|| cnt); 
  67.       end; 
  68. /
  69.  
  70.  
Mar 4 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Paul Rowe | last post by:
Hi "You" I have two collection types declared at the SQL level. 1. Do you know of any known bugs with the BULK COLLECT clause used with the TABLE operator? I have a situation now where I am...
7
by: iqbal | last post by:
Hi all, We have an application through which we are bulk inserting rows into a view. The definition of the view is such that it selects columns from a table on a remote server. I have added the...
2
by: G Uljee | last post by:
Can someone help me with my performance problem? I need to read a large queue (MSMQ, sometimes the max of 4MB) and want to save that directly into a Access 2003 database. On the moment I save per...
6
by: Sean | last post by:
HI There, I am making the transition from asp to asp .net, I am currenty writing an application that requires a bulk insert from a webform into SQL server, normally I would just create rows of...
2
by: Amir | last post by:
Hi every one I have an application that should send email to notify users. But the message's body is dynamic and changes for every user. In fact there is a template that for everyone the dynamic...
1
by: sumanroyc | last post by:
Hi, We are trying to insert into a remote database using the forall statement 5000 rows at a time. type t_column_id is table of number index by binary_integer; v_column_id t_column_id; begin...
1
by: devmiral | last post by:
Hello every one I trying simple bulk collect , it is giving me an error in oracle 9i, i wroe as per book and on the web deatils about bulk collect declare type emp_type is table of emp%rowtype;...
0
debasisdas
by: debasisdas | last post by:
We can fetch from a cursor into one or more collections: DECLARE TYPE NameList IS TABLE OF employees.last_name%TYPE; TYPE SalList IS TABLE OF employees.salary%TYPE; CURSOR c1 IS SELECT...
3
by: oravm | last post by:
Hi, I re-write a query and used bulk collect to improve the performance of the batch process. The query below has NO compile error but when execute query there is error 'ORA-01403: no data...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.