Exceptions [message #397391] |
Fri, 10 April 2009 08:09  |
shrinika
Messages: 306 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 #397419 is a reply to message #397415] |
Fri, 10 April 2009 09:52   |
shrinika
Messages: 306 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   |
Frank
Messages: 7901 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   |
pablolee
Messages: 2882 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 #397831 is a reply to message #397426] |
Tue, 14 April 2009 02:14  |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
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
|
|
|