Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT (10.2)
BULK COLLECT [message #381427] Fri, 16 January 2009 21:36 Go to next message
srihari1974
Messages: 12
Registered: November 2008
Location: atlanta
Junior Member
CREATE OR REPLACE PROCEDURE Proc1 
     (p_error  OUT NUMBER) 
IS 
  TYPE rec_1 IS RECORD(ename emp.ename%TYPE, 
                        dname dept.dname%TYPE); 
  TYPE t_rec IS TABLE OF REC_1 INDEX BY BINARY_INTEGER ; 
  v_rec         T_REC; 
  CURSOR cur1 IS 
    SELECT ename, 
           dname 
    FROM   emp 
           INNER JOIN dept 
             ON emp.deptno = dept.deptno; 
  v_tablecount  INTEGER; 
BEGIN 
  dbms_output.Put_line('dept table update begins ' 
                       ||To_char(SYSDATE,'MM/DD/YYYY HH:MI:SS')); 
   
  OPEN cur1; 
   
  FETCH cur1 BULK COLLECT INTO v_rec LIMIT 10000; 
   
  v_tablecount := v_rec.COUNT; 
   
  IF v_rec > 0 THEN 
    FORALL i IN 1..v_rec.COUNT 
      UPDATE dept 
      SET    found_ind = 'Y', 
             last_updated_date = SYSDATE 
      WHERE  dname = v_rec.Dname(i); 
     
    FORALL i IN 1..v_rec.COUNT 
      UPDATE emp 
      SET    dname = v_rec.Dname(i), 
             found_ind = 'Y', 
             last_updated_date = SYSDATE 
      WHERE  ename = v_rec.Ename(i); 
  END IF; 
   
  CLOSE c_perfdata; 
   
  dbms_output.Put_line('Dept table update complete ' 
                       ||To_char(SYSDATE,'MM/DD/YYYY HH:MI:SS')); 
   
  dbms_output.Put_line('emp table update complete ' 
                       ||To_char(SYSDATE,'MM/DD/YYYY HH:MI:SS')); 
   
  p_error := 0; 
EXCEPTION 
  WHEN OTHERS THEN 
    dbms_output.Put_line('Run Date: ' 
                         ||To_char(SYSDATE,'yyyymmdd') 
                         ||'  Error: ' 
                         ||SQLCODE 
                         ||'-' 
                         ||Substr(SQLERRM,1,200)); 
     
    p_error := SQLCODE; 
END; 


[EDITED by LF: applied [code] tags]

[Updated on: Sat, 17 January 2009 02:27] by Moderator

Report message to a moderator

Re: BULK COLLECT [message #381428 is a reply to message #381427] Fri, 16 January 2009 22:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
ugly & unreadable post.


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.


Besides I don't see any question, so I won't waste time trying to guess what you want.
Re: BULK COLLECT [message #381429 is a reply to message #381428] Fri, 16 January 2009 22:22 Go to previous messageGo to next message
srihari1974
Messages: 12
Registered: November 2008
Location: atlanta
Junior Member
I am sorry abt posting
My question is

Is this procedure correct?
Can i declare record type and USE BULK COLLECT

and IS it OK if i fetch cursor from two tables
My Procedure is taking for ever!!
Re: BULK COLLECT [message #381437 is a reply to message #381427] Fri, 16 January 2009 23:40 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Is this procedure correct?
How can anyone but you know?

You have table & provided us no DDL for it.
You have data & provided us no DML for test data.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

You have not shown us via CUT & PASTE what you actually did or how Oracle responded.

You have not provided any expected output.
Re: BULK COLLECT [message #381440 is a reply to message #381429] Sat, 17 January 2009 00:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Some remarks:

First and foremost: get rid of that useless exception handler. If an exception occurs, don't worry, Oracle will tell you what it was. It doesn't need you to write it.
Second: how many rows are there in emp and dept, if you say this takes forever?!
Third: You limit your bulk collect to 10000 (which sounds quite high), but you don't loop. So, if there were more than 10000 records, the rest would never be fetched.
Re: BULK COLLECT [message #381445 is a reply to message #381427] Sat, 17 January 2009 01:07 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:
Michel Cadot wrote on Thu, 06 November 2008 07:50
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Just an insert and an update are sufficient, you don't need all these loops.

Regards
Michel

This time again I bet you can do it with just SQL.
Previous Topic: Trim & Instr
Next Topic: V$parameter
Goto Forum:
  


Current Time: Sun Dec 11 06:10:27 CST 2016

Total time taken to generate the page: 0.16301 seconds