Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT -- FOR ALL Exceptions
BULK COLLECT -- FOR ALL Exceptions [message #416267] Fri, 31 July 2009 07:23 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member

Below is my procedure and create table scripts while compile i got the below error.

I cant conclude why this error means.

create or replace procedure bulk_check as 

cursor cur_p44 is 
select lmp_sys_gen_le_id,lmp_long_name from p44_le_main_profile where lmp_sgmnt_code_value='30';

type t_char is table of varchar2(1000) index by binary_integer;

type t_num is table of number(30,10) index by binary_integer;

l_le_id    t_num ;
l_lng_name t_char;

Itr_No  Number;
Itr_exp EXCEPTION;

pragma exception_init(Itr_exp,-24381);

Begin

open cur_p44;

loop

   FETCH cur_p44 BULK COLLECT
     INTO l_le_id,		   
	 	  l_lng_name LIMIT 1000;
		  
if l_le_id.COUNT>0 then
forall i in l_le_id.FIRST .. l_le_id.LAST SAVE EXCEPTIONS

insert into mod_p44 (le_id,long_name) values (l_le_id(i),l_lng_name(i));

commit;
end if;
EXIT WHEN cur_p44%NOTFOUND;
END LOOP;

EXCEPTION
   WHEN Itr_exp THEN
     Itr_No := SQL%BULK_EXCEPTIONS.COUNT;
     DBMS_OUTPUT.PUT_LINE('Number of statements failed: ' || Itr_exp);
	 
   FOR j IN 1 .. Itr_exp LOOP

     DBMS_OUTPUT.PUT_LINE('Error No'||i||'Error Occurred During'||'Iteration No'||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
     DBMS_OUTPUT.PUT_LINE('Error message is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

   END LOOP;

CLOSE cur_p44;

End bulk_check;		  



create table mod_p44(le_id number(10),long_name varchar2(100))



Error :
-----------

PLS-00705: exception 'ITR_EXP' used in expression requiring return type
Re: BULK COLLECT -- FOR ALL Exceptions [message #416269 is a reply to message #416267] Fri, 31 July 2009 07:32 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
Itr_No := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE('Number of statements failed: ' || Itr_exp);

FOR j IN 1 .. Itr_exp LOOP
What do you want to display / loop? Itr_no (Or) Itr_exp??

By
Vamsi
Re: BULK COLLECT -- FOR ALL Exceptions [message #416272 is a reply to message #416267] Fri, 31 July 2009 07:35 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well once I've sorted out the errors caused by the lack of a create table for p44_le_main_profile I get these errors:
SQL> sho errors procedure bulk_check;
Errors for PROCEDURE BULK_CHECK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
41/6     PL/SQL: Statement ignored
41/27    PLS-00306: wrong number or types of arguments in call to '||'
43/4     PL/SQL: Statement ignored
43/18    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed


I guess it's the same thing though - you're referencing the exception as though it's a number. It's not. The fact that you've linked it to an error number with a pragma does not mean it becomes a number type.
Exceptions can be raised and caught in error handlers - there's nothing else you can do with them.
Re: BULK COLLECT -- FOR ALL Exceptions [message #416478 is a reply to message #416272] Mon, 03 August 2009 01:28 Go to previous message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I have changed Itr_exp into Itr_no it is working fine.

Thank you very much.

Previous Topic: sequence in Dynamic sql
Next Topic: Greatest Date
Goto Forum:
  


Current Time: Sun Dec 04 23:10:59 CST 2016

Total time taken to generate the page: 0.04433 seconds