Home » SQL & PL/SQL » SQL & PL/SQL » Exceptions (oracle10g)
Exceptions [message #397391] Fri, 10 April 2009 08:09 Go to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
I am trying to find some sample code to trap the built-in
exceptions. Here are the exceptions i am looking...

ACCESS_INTO_NULL
COLLECTION_IS_NULL
ROWTYPE_MISMATCH
SELF_IS_NULL
SYS_INVALID_ROWID


I googled for these.. i am not able find anywhere...

Any help is appreciated..

Thanks
Re: Exceptions [message #397392 is a reply to message #397391] Fri, 10 April 2009 08:13 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
You have got to be joking. Do you know how to use Google?
Re: Exceptions [message #397397 is a reply to message #397391] Fri, 10 April 2009 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not Oracle exceptions.

Regards
Michel
Re: Exceptions [message #397415 is a reply to message #397397] Fri, 10 April 2009 09:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Fri, 10 April 2009 15:49
Not Oracle exceptions.

Regards
Michel


SQL> begin
  2    null;
  3  exception
  4    when access_into_null
  5    then
  6      null;
  7    when collection_is_null
  8    then
  9      null;
 10    when rowtype_mismatch
 11    then
 12      null;
 13    when self_is_null
 14    then
 15      null;
 16    when sys_invalid_rowid
 17    then
 18      null;
 19  end;
 20  /

PL/SQL procedure successfully completed.

Which of these isn't Oracle? Wink

@shrinika: better practice some more your Googling skills
Re: Exceptions [message #397419 is a reply to message #397415] Fri, 10 April 2009 09:52 Go to previous messageGo to next message
shrinika
Messages: 267
Registered: April 2008
Senior Member
I am not expecting these answers.. I am expecting some valid code in execution part to trap these exceptions..

Frank - I am not expecting this code... I am not expecting NULL in execution part. I need some real code to trap these exception.

SQL> begin
  2    null;
  3  exception
  4    when access_into_null
  5    then
  6      null;
  7    when collection_is_null
  8    then
  9      null;
 10    when rowtype_mismatch
 11    then
 12      null;
 13    when self_is_null
 14    then
 15      null;
 16    when sys_invalid_rowid
 17    then
 18      null;
 19  end;
 20  /



Here are some sample code for exceptions... Like below, i need some script to raise the exceptions...

Here is the code for CURSOR_ALREADY_OPEN...

scott@orcl> set serveroutput on
scott@orcl> declare
  2  cursor c1 is select * from emp;
  3  begin
  4  open c1;
  5  open c1;
  6  exception
  7  when cursor_already_open then
  8  dbms_output.put_line('The cursor is already opened...');
  9  end;
 10  /
The cursor is already opened...

PL/SQL procedure successfully completed.

scott@orcl> 


Here is the code ZERO_DIVDE

scott@orcl> set serveroutput on
scott@orcl> declare
  2  x number;
  3  begin
  4  x := 23/0;
  5  exception
  6  when zero_divide then
  7  dbms_output.put_line('Zero devide error...');
  8  end;
  9  /
Zero devide error...

PL/SQL procedure successfully completed.

scott@orcl> 


Here is the code for DUP_VAL_ON_INDEX..

scott@orcl> begin
  2  update emp set empno = 999 where rownum <=2;
  3  exception
  4  when dup_val_on_index then
  5  dbms_output.put_line('trying to update duplicate values on unique key');
  6  end;
  7  /
trying to update duplicate values on unique key

PL/SQL procedure successfully completed.

scott@orcl> 



Here is code for INVALID_CURSOR

scott@orcl> declare
  2  cursor c1 is select * from emp;
  3  begin
  4  close c1;
  5  exception 
  6  when invalid_cursor then
  7  dbms_output.put_line('invalid cursor...');
  8  end;
  9  /
invalid cursor...

PL/SQL procedure successfully completed.

scott@orcl> 


Here is code for INVALID_NUMBER

scott@orcl> declare
  2  Bogus_Value   varchar2(30) := 'NOT A NUMBER';
  3  begin
  4  update EMP set
  5  mgr = to_number(Bogus_Value);
  6  exception
  7  when INVALID_NUMBER then
  8  dbms_output.put_line('Trying to update Invalid number..');
  9  end;
 10  /
Trying to update Invalid number..

PL/SQL procedure successfully completed.

scott@orcl> 


Here is code for NO_DATA_FOUND

scott@orcl> declare
  2  v_empno number;
  3  begin
  4  select empno into v_empno
  5  from emp
  6  where empno=8723;
  7  exception
  8  when no_data_found then
  9  dbms_output.put_line('no data found in emp table...');
 10  end;
 11  /
no data found in emp table...

PL/SQL procedure successfully completed.

scott@orcl> 


here is code for TOO_MANY_ROWS

scott@orcl> declare
  2  v_empno number;
  3  begin
  4  select empno into v_empno
  5  from emp;
  6  exception
  7  when too_many_rows then
  8  dbms_output.put_line('Too many rows in emp table...');
  9  end;
 10  /
Too many rows in emp table...

PL/SQL procedure successfully completed.

scott@orcl> 


Here is the code for VALUE_ERROR

scott@orcl> set serveroutput on
scott@orcl> declare
  2  v_dname varchar2(5);
  3  begin
  4  select dname into v_dname
  5  from dept
  6  where deptno=10;
  7  exception
  8  when value_error then
  9  dbms_output.put_line('value error....');
 10  end;
 11  /
value error....

PL/SQL procedure successfully completed.

scott@orcl> 
 

[Updated on: Fri, 10 April 2009 09:56]

Report message to a moderator

Re: Exceptions [message #397421 is a reply to message #397419] Fri, 10 April 2009 10:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
shrinika wrote on Fri, 10 April 2009 16:52
I am not expecting these answers.. I am expecting some valid code in execution part to trap these exceptions..

Frank - I am not expecting this code... I am not expecting NULL in execution part. I need some real code to trap these exception.


Dude, too bad for you.
I was answering Michel anyway. For you I had reserved my advise.
I googled again, now with a different predefined exception, and again the top hit was the page that explains them all.
If you cannot build an example from the descriptions given, then don't worry, you will NOT need these exceptions.
Re: Exceptions [message #397424 is a reply to message #397421] Fri, 10 April 2009 11:00 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Frank, How very dare you be so rude as to not post a full and complete reply, giving the OP exactly what he demands (even tho he didn't actually state exactly what it was he was demanding - but we'll leave that to the side) You're behavior is quite unacceptable and I think that we are lucky that the OP is not demanding his membership feed repaid in full!
@shrtheinika I apologise for the actions of Frank on his behalf. I will gladly write code that you want. I reckon it should take me about 3 days at £500 per day. PM me with a purchase order and we'll get the ball rolling for you buddy.
Re: Exceptions [message #397426 is a reply to message #397415] Fri, 10 April 2009 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Fri, 10 April 2009 16:25
Michel Cadot wrote on Fri, 10 April 2009 15:49
Not Oracle exceptions.

Regards
Michel


SQL> begin
  2    null;
  3  exception
  4    when access_into_null
  5    then
  6      null;
  7    when collection_is_null
  8    then
  9      null;
 10    when rowtype_mismatch
 11    then
 12      null;
 13    when self_is_null
 14    then
 15      null;
 16    when sys_invalid_rowid
 17    then
 18      null;
 19  end;
 20  /

PL/SQL procedure successfully completed.

Which of these isn't Oracle? Wink

Seems my exception list is not up to date. Embarassed

Thanks for the correction.
Michel

[Updated on: Fri, 10 April 2009 11:09]

Report message to a moderator

Re: Exceptions [message #397831 is a reply to message #397426] Tue, 14 April 2009 02:14 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
I have search in the Oracle Documentation and found some links related, also try to reproduce some error.

---------------------
1 . ACCESS_INTO_NULL

ACCESS_INTO_NULL


---------------------
2. COLLECTION_IS_NULL

SQL> set serveroutput on
SQL> -- Without Trapping 
SQL> DECLARE
   TYPE T IS VARRAY (2) OF NUMBER (10);

   P T;
BEGIN
   DBMS_OUTPUT.PUT_LINE (P (1));
END;
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 6

SQL> -- With Trapping 
SQL> DECLARE
   TYPE T IS VARRAY (2) OF NUMBER (10);

   P T;
BEGIN
   DBMS_OUTPUT.PUT_LINE (P (1));
EXCEPTION
   WHEN COLLECTION_IS_NULL
   THEN
      raise_application_error(-20009,'Error Tarpped');
END;
ORA-20009: Error Tarpped
ORA-06512: at line 10


---------------------
3. ROWTYPE_MISMATCH

ROWTYPE_MISMATCH


---------------------
4. SELF_IS_NULL

Only definition, No example.



---------------------
5. SYS_INVALID_ROWID

SQL> -- Without Trapping 

SQL> DECLARE
   a ROWID;
BEGIN
   SELECT dbms_rowid.rowid_create (99
                                  ,2
                                  ,3
                                  ,4
                                  ,4
                                  )
     INTO a
     FROM dual;
END;
ORA-01410: invalid ROWID
ORA-06512: at "SYS.DBMS_ROWID", line 38
ORA-06512: at line 4

SQL> -- With Trapping 
SQL> DECLARE
   a ROWID;
BEGIN
   SELECT dbms_rowid.rowid_create (99
                                  ,2
                                  ,3
                                  ,4
                                  ,4
                                  )
     INTO a
     FROM dual;
EXCEPTION
   WHEN sys_invalid_rowid
   THEN
      raise_application_error (-20009, 'Error Tarpped');
END;

ORA-20009: Error Tarpped
ORA-06512: at line 15


Thanks
Trivendra

[Updated on: Tue, 14 April 2009 02:14]

Report message to a moderator

Previous Topic: Logging DMLs
Next Topic: PLS-00103 in Procedure using ref cursor
Goto Forum:
  


Current Time: Sat Dec 10 18:29:20 CST 2016

Total time taken to generate the page: 0.04035 seconds