Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Binding Query (merged)
Bulk Binding Query (merged) [message #383520] Thu, 29 January 2009 01:15 Go to next message
ind9
Messages: 65
Registered: January 2009
Member
Hi,

I am using oracle 9.2.0.8.0 release.

I have 2 forall statments like

forall i in 1..Tbl_Emp.Count  Save Exceptions
  Insert Into Emp
       Values(Tbl_Emp(i),
              Tbl_Emp_Nm(i));
 Forall j in 1..Tbl_Emp.Count
  Update Emp_Log
   Set Procesed_Rec = 1
   Where Emp_ID = Tbl_Emp(j);

EXCEPTION
     WHEN dml_errors THEN
		   errors := SQL%BULK_EXCEPTIONS.COUNT;
		   dbms_output.put_line('Number of errors is ' || errors);
		   FOR i IN 1..errors LOOP
		      dbms_output.put_line('Error ' || i || ' occurred during '||
		         'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
		      dbms_output.put_line('Oracle error is ' ||
		         SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));		   END LOOP;
  


Table EMP is containing set of employees and EMp_ID is a primary key column. If we try to insert a duplicate record then exception will raise.

1. My query is can i display employee Id for which it is failing to insert. Like while inserting emp 100 it is failed then can i display the message for employee 100 already exists and so on.
2. I have 2 forall statments how would i know for which forall statment it is raising an error.

kindly help me.

Its urgent

Thanks in advance

Re: Bulk Binding Query (merged) [message #383526 is a reply to message #383520] Thu, 29 January 2009 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost your question.
If you have any problem, check again the forum to see if your question is there BEFORE reposting.

Quote:
Its urgent

No, it is not.

Quote:
END LOOP;

Question

Quote:
2. I have 2 forall statments how would i know for which forall statment it is raising an error.

Put each one in a BEGIN/EXCEPTION/END block but try
update ... where emp_id in (select * from table(tbl-emp))
instead of second FORALL.

Regards
Michel
Re: Bulk Binding Query (merged) [message #383528 is a reply to message #383526] Thu, 29 January 2009 01:30 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
How can i capture the Emp_ID where the exception raised
Re: Bulk Binding Query (merged) [message #383533 is a reply to message #383528] Thu, 29 January 2009 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you think is the purpose of "SQL%BULK_EXCEPTIONS(i).ERROR_INDEX" in YOUR code?

Regards
Michel
Re: Bulk Binding Query (merged) [message #383561 is a reply to message #383533] Thu, 29 January 2009 03:12 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
Thanks for your reply.

Forall j in 1..Tbl_Emp.Count
  Update Emp_Log
   Set Procesed_Rec = 1
   Where [B]Emp_ID = Tbl_Emp(j)
      And Emp_Nm = Tbl_Emp_Nm(j)
      And Bank_Cd = Tbl_bk_CD(j);[/B]

You are recommending to modify this code 
 update Emp_Log
    ......
  Where Emp_Id In
              (Select * From Table(Tbl_Emp)


Now i need to update the Emp_Log
table based on Emp_ID, EMp_Nm and Bank_CD.

In my database we have reasonable large volume of data.

How do you recommend to update the Emp_Log table thru forall or as by using this.Please recommend me if you are suggesting like below then how can i handle emp_nm and bank_cd coditions also

update Emp_Log
    ......
  Where Emp_Id In
              (Select * From Table(Tbl_Emp)




Thanks
Re: Bulk Binding Query (merged) [message #383565 is a reply to message #383561] Thu, 29 January 2009 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is always faster to do it in a single SQL statement.

Regards
Michel
Re: Bulk Binding Query (merged) [message #383568 is a reply to message #383565] Thu, 29 January 2009 03:26 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
sorry i didn't get you

Forall j in 1..Tbl_Emp.Count
  Update Emp_Log
   Set Procesed_Rec = 1
   Where Emp_ID = Tbl_Emp(j)
      And Emp_Nm = Tbl_Emp_Nm(j)
      And Bank_Cd = Tbl_bk_CD(j);


How can i rewrite the above code by using Oracle Pipelined Table Functions
 Update Emp_Log
   Set Procesed_Rec = 1
    Where Emp_ID...
      And Emp_Nm..

Please help

Re: Bulk Binding Query (merged) [message #383582 is a reply to message #383565] Thu, 29 January 2009 03:54 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
Tbl_Emp, Tbl_Emp_Nm are plsql tables.
Re: Bulk Binding Query (merged) [message #383584 is a reply to message #383568] Thu, 29 January 2009 03:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Pipelined functions are not the answer here.

Michel is saying that it would be quicker to build a single SQL statement that incorporated the logic that you are using to populate the tables Tbl_Emp, Tbl_Emp_Nm and Tbl_bk_CD and updated the emp_log table.

Similarly, it would be quicker to use a single statement to insert into Emp rather than to select detail into pl/sql tables and ue FORALL to do the insert.

If you can post the logic that you're using to populate the tables, then we should be able to give you some pointers in the right direction.
Re: Bulk Binding Query (merged) [message #383586 is a reply to message #383582] Thu, 29 January 2009 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I also meant, if you have some complex stuff to fill the arrays:
SQL> create type mytype is table of number
  2  /

Type created.

SQL> create table t (id number, processed varchar2(1))
  2  /

Table created.

SQL> declare
  2    mytab mytype := mytype();
  3  begin
  4    -- some complex stuff that fit my array
  5    mytab.extend;
  6    mytab(1) := 1;
  7    -- Update
  8    update t set processed='Y' 
  9      where id in (select * from table(mytab));
 10  end;
 11  /

PL/SQL procedure successfully completed.

Regards
Michel
Re: Bulk Binding Query (merged) [message #383592 is a reply to message #383586] Thu, 29 January 2009 04:18 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
Declare
  Cursor C_Emp2 Is
   Select Emp_ID, Emp_Nm, Bank_Cd
    From EMp2
    Where Rec_Processed is null;

 Type Tbl_Emp_ID_Type is table of Emp2.Emp_ID%Type
  Index by Binary Integer;

 Type Tbl_Emp_Nm_Type is table of Emp2.Emp_ID%Type
  Index by Binary Integer;
 Type Tbl_Bank_CD_Type is table of Emp2.Emp_ID%Type
  Index by Binary Integer;

   Tbl_Emp_ID  Tbl_Emp_ID_Type ;
   Tbl_Emp_Nm Tbl_Emp_Nm_Type ;
  Tbl_Bank_CD  Tbl_Bank_CD_Type ;
Begin
 Open C_Emp2;   
  Loop
     If Tbl_Emp_ID.Exists(1) Then 
        Tbl_Emp_ID.Delete;
        Tbl_Emp_Nm.Delete;
       Tbl_Bank_CD.Delete;
     End If;   
    Fetch C_Emp2 
     Bulk Collect Into Tbl_Emp_ID, Tbl_Emp_Nm, Tbl_Bank_CD
    Limit 200;
  Begin
  forall i in 1..Tbl_Emp.Count  Save Exceptions
  Insert Into Emp
       Values(Tbl_Emp_ID(i),
              Tbl_Emp_Nm(i),Tbl_Bank_CD(i) );
 Forall j in 1..Tbl_Emp.Count
  Update Emp_Log
   Set Procesed_Rec = 1
   Where Emp_ID = Tbl_Emp_ID(j)
      AND Emp_Nm = Tbl_Emp_Nm(j)
      AND Bank_Cd = Tbl_Bank_CD(j);
 EXCEPTION
     WHEN dml_errors THEN
	errors := SQL%BULK_EXCEPTIONS.COUNT;
	FOR i IN 1..errors LOOP
	  Log_Err(Tbl_Emp_ID(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX));
		      dbms_output.put_line('Oracle error is ' ||
		         SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));	 
         END LOOP;

  End;
  End Loop;
  Exception 
   when others THEN
    dbms_output.put_line('Oracle error is ' ||
		         SQLERRM);
  END;



1. My query is can i display employee Id for which it is failing to insert. Like while inserting emp 100 it is failed then can i display the message for employee 100 already exists and so on.
2. I have 2 forall statments how would i know for which forall statment it is raising an error.

kindly help me.

Thanks in advance
Re: Bulk Binding Query (merged) [message #383593 is a reply to message #383586] Thu, 29 January 2009 04:21 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
We are not allowed to create any type or objects.

Thanks
Re: Bulk Binding Query (merged) [message #383596 is a reply to message #383593] Thu, 29 January 2009 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ask the one that is allowed to do it for you.
Types are part of code like tables are.
Try to make they understand it is as silly to say you can't create this type of custom datatype as saying you can't use timestamp datatype for instance.

Regards
Michel
Re: Bulk Binding Query (merged) [message #383598 is a reply to message #383596] Thu, 29 January 2009 04:45 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
Please understand its already implemented and it is in the production environment. so i can change only the part of code not creating any types.

Thanks
Re: Bulk Binding Query (merged) [message #383601 is a reply to message #383592] Thu, 29 January 2009 04:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The way to see which FORALL is raising the exceptio is to wrap each of your forall statements in it's own exception handling block.

I reckon that your code can be rewritten like this:
Declare

Begin

  MERGE INTO Emp e
  USING (Select Emp_ID, Emp_Nm, Bank_Cd
         From EMp2
         Where Rec_Processed is null) e2
  ON (e.emp_id = e2.emp_id)
  WHEN NOT MATCHED THEN INSERT (e.emp_id ,e.emp_nm ,e.bank_cd) 
                        VALUES (e2.emp_id,e2.emp_nm,e2.bank_cd);

  Update Emp_Log
   Set Procesed_Rec = 1
   Where Emp_ID in (Select Emp_ID
                    From EMp2
                    Where Rec_Processed is null);
  END;

That will only insert records into Emp that have emp_ids that don't already exist, and will update all of the emp_log records.
Re: Bulk Binding Query (merged) [message #383604 is a reply to message #383601] Thu, 29 January 2009 05:23 Go to previous messageGo to next message
ind9
Messages: 65
Registered: January 2009
Member
How about the performance if we use merge. That is why i used forall
Re: Bulk Binding Query (merged) [message #383606 is a reply to message #383604] Thu, 29 January 2009 05:24 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I already said: use a single SQL statement is always faster than any PL/SQL.

Regards
Michel
Previous Topic: Performance Issue (For loop)
Next Topic: PLSQL script not collecting temp table fields - variables problem?
Goto Forum:
  


Current Time: Fri Dec 09 13:51:50 CST 2016

Total time taken to generate the page: 0.06106 seconds