Compiled Messages:
------------------------------------------------------------
Message no. 6826
Posted by Waseem Manzoor (wmanzoor) on Sunday, March 5, 2006 9:44pm
Subject: Why User Defined Exceptions?
Hi Every body
I think we can write our program with out userdefined exceptions. We can take this work
from If else statement too? Then why user defined exceptions? What do u guys think?
Waseem Manzoor
------------------------------------------------------------
Message no. 6830[Branch from no. 6826]
Posted by Muhammad Latif (mlatif) on Monday, March 6, 2006 12:01am
Subject: Re: Why User Defined Exceptions?
Hey Waseem:
Yes you are right to some extent. We can do this work with If Else statment.. but when
the lines of code increase and the project becomes big, then using userdefined
exceptions is a good approach. Hope this helps.
Arsal
------------------------------------------------------------
Message no. 6835[Branch from no. 6826]
Posted by Antoinette Lockett (arlockett) on Monday, March 6, 2006 12:44am
Subject: Re: Why User Defined Exceptions?
the user-defined exceptions is used to defined the errors in a code. user-defined is used
the same way we used loops last week. We could have use a regular loop to defined our
information but we used FOR LOOP CURSOR to simplfy the code. This is the same with
user-defined exceptions it simplfies the code.
------------------------------------------------------------
Message no. 6837[Branch from no. 6826]
Posted by Harold Harris (hjharris2) on Monday, March 6, 2006 7:03am
Subject: Re: Why User Defined Exceptions?
Waseem,
With exceptions, you can handle errors conveniently without the need to code multiple
checks. Exceptions also improve readability by letting you isolate error-handling routines.
HH
------------------------------------------------------------
Message no. 6839[Branch from no. 6826]
Posted by Imran Pathan (ipathan) on Monday, March 6, 2006 2:38pm
Subject: Re: Why User Defined Exceptions?
I agree with Arsal
Imran
------------------------------------------------------------
Message no. 6842[Branch from no. 6835]
Posted by Bikash Adhikari (badhikari) on Monday, March 6, 2006 9:36pm
Subject: Re: Why User Defined Exceptions?
Very true it does simplifies the code.
BIkash
------------------------------------------------------------
Message no. 6844[Branch from no. 6835]
Posted by Kelsey Pooley (kjpooley) on Tuesday, March 7, 2006 10:19am
Subject: Re: Why User Defined Exceptions?
I agree with Antoinette (and with Arsal). It simplifies the code because you only have to create the exception one time. The
programs that we have seen so far have been pretty simple but as the code becomes more complex, it is easier to use the
exception rather than to recreate the code each time you need to use it.
------------------------------------------------------------
Message no. 6845[Branch from no. 6835]
Posted by Rohini Shrestha (rshrestha) on Tuesday, March 7, 2006 10:19am
Subject: Re: Why User Defined Exceptions?
User-defined exceptions are basically used to ensure the integrity of
the database and business rules. It helps to define errors in code.
Rohini
------------------------------------------------------------
Message no. 6847[Branch from no. 6830]
Posted by Gnaneshwar Bukka (gbukka) on Tuesday, March 7, 2006 11:41am
Subject: Re: Why User Defined Exceptions?
hey Wasim, I totally agree with Arsal, huge code creates huge problems,
so we need exception handlers for such complicated programs.
------------------------------------------------------------
Message no. 6851[Branch from no. 6826]
Posted by Sagun Piya (srpiya2) on Tuesday, March 7, 2006 1:12pm
Subject: Re: Why User Defined Exceptions?
Hey Waseem
It simplifies the code and makes the program shorter
Sagun
------------------------------------------------------------
Message no. 6854
Posted by Muhammad Latif (mlatif) on Tuesday, March 7, 2006 1:52pm
Subject: Application logs
Hi everyone:
I was wondering if we can record the exception and its source in application logs?
Anybody knows?
Arsal
------------------------------------------------------------
Message no. 6855[Branch from no. 6854]
Posted by Waseem Manzoor (wmanzoor) on Tuesday, March 7, 2006 2:07pm
Subject: Re: Application logs
Arsal,
Good question but I think complicated too.
This is the new feature that Oracle 10G has only. Oracle 10G has
one function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE that help
developers to trap And log exceptions.
In Earlier versions developers were used to take output of PL/SQL
and print that whole error message that looks like a scrap. But this
function prints error messages in a proper readable way.
Hope this will help you.
Waseem Manzoor
------------------------------------------------------------
Message no. 6856[Branch from no. 6855]
Posted by Muhammad Latif (mlatif) on Tuesday, March 7, 2006 2:31pm
Subject: Re: Application logs
Hey Waseem:
You have been very precise and accurate. I also found this function in 10g while doing
some extra reading. Thanks a lot.
Arsal
------------------------------------------------------------
Message no. 6857[Branch from no. 6855]
Posted by Krishnamurth Ashwini (kashwini) on Tuesday, March 7, 2006 2:57pm
Subject: Re: Application logs
Hi Arsal,
Found some more interesting points for your question in the following link
(Check out: What's My Line? Tracking Exceptions in Oracle10g in this
website).
http://www.quest-pipelines.com/newsletter-v5/newsletter_0904.htm
Ash
------------------------------------------------------------
Message no. 6858[Branch from no. 6857]
Posted by Rohini Shrestha (rshrestha) on Tuesday, March 7, 2006 5:10pm
Subject: Re: Application logs
I totally agree with you Waseem. Further,the command
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE gives us felxibility to log
exception. It provides the error stack all the way back to source.
However, it does not supply the error message, it just gives the error's
propagation path.
Rohini
------------------------------------------------------------
Message no. 6860[Branch from no. 6851]
Posted by Rhonda Nichols (renichols2) on Tuesday, March 7, 2006 8:59pm
Subject: Re: Why User Defined Exceptions?
Plus we can re-use defined exceptions without having to re-type IF, then, else statements
every time we want to check for an error.
------------------------------------------------------------
Message no. 6862[Branch from no. 6855]
Posted by Bikash Adhikari (badhikari) on Tuesday, March 7, 2006 11:22pm
Subject: Re: Application logs
An excellent information Waseem thank you for it. If we are able to
print the error message that way then it will be easier to debug or
correct the program errors.
Thank you.
BIkash
------------------------------------------------------------
Message no. 6864[Branch from no. 6862]
Posted by Muhammad Latif (mlatif) on Wednesday, March 8, 2006 3:01am
Subject: Re: Application logs
Hey Ash:
Thanks for sharing the link. It has very useful information.
Arsal
------------------------------------------------------------
Message no. 6866[Branch from no. 6864]
Posted by Venkat Munagala (vrmunagala) on Wednesday, March 8, 2006 10:55am
Subject: Re: Application logs
That was an apt answer from u waseem. Thank you
------------------------------------------------------------
Message no. 6867
Posted by Rohini Shrestha (rshrestha) on Wednesday, March 8, 2006 1:28pm
Subject: Compilation Error
Hey guys,
Did anyone get compilation error while executing "after trigger" code
provided by Dr. Liu in chapter 5?
Rohini
------------------------------------------------------------
Message no. 6868[Branch from no. 6867]
Posted by Krishnamurth Ashwini (kashwini) on Wednesday, March 8, 2006 1:36pm
Subject: Re: Compilation Error
As I had already posted earlier (in week8 problems and solutions)...Use
the following code...you shoudn't get any error. (There are two changes
in this script (line 7 and 15) as compared to that in the chapter05
material).
IMPORTANT: Check for quotation marks before executing.
I did not get any error with the script below.
CREATE OR REPLACE TRIGGER logPartchanges
AFTER INSERT OR UPDATE OR DELETE ON Parts
DECLARE
statementType CHAR(1);
BEGIN
IF INSERTING THEN
statementType := 'I';
ELSIF UPDATING THEN
statementType := 'U';
ELSE
statementType := 'D';
END IF;
INSERT INTO partsLog
VALUES (SYSDATE, statementType, USER);
END;
/
Ash
------------------------------------------------------------
Message no. 6869[Branch from no. 6868]
Posted by Rohini Shrestha (rshrestha) on Wednesday, March 8, 2006 2:08pm
Subject: Re: Compilation Error
includes attachment
Attached is the set of errors that I got. if anyone can help...
Rohini
------------------------------------------------------------
Message no. 6870[Branch from no. 6869]
Posted by Krishnamurth Ashwini (kashwini) on Wednesday, March 8, 2006 2:32pm
Subject: Re: Compilation Error
Rohini,
Well..it's strange...I am not able to figure out what is wrong here.
When I run my script i am not getting any errors..but when I run your
script I get the same errors that you have posted.
Solution:
Why don't you just copy and paste the same script that I have sent in
the previous mail...because I did the same thing 2 min back and it was
successful.
Just give it a try:-)
Ash
------------------------------------------------------------
Message no. 6872[Branch from no. 6870]
Posted by Rohini Shrestha (rshrestha) on Wednesday, March 8, 2006 2:47pm
Subject: Re: Compilation Error
Hey Ash
I figure out the problem, it in in line 4.
Thankx fro your help though. YOu have always been so helpful
Rohini
------------------------------------------------------------
Message no. 6882[Branch from no. 6860]
Posted by Abhash Bhandary (abhandary) on Wednesday, March 8, 2006 8:08pm
Subject: Re: Why User Defined Exceptions?
you are right Rhonda
Abhash
------------------------------------------------------------
Message no. 6883[Branch from no. 6866]
Posted by Abhash Bhandary (abhandary) on Wednesday, March 8, 2006 8:10pm
Subject: Re: Application logs
That was a perfect answer from you Waseem...and thanx also to Ash for
sharing that link
Abhash
------------------------------------------------------------
Message no. 6887[Branch from no. 6826]
Posted by Phanindra Maddhi (pmaddhi) on Thursday, March 9, 2006 12:11pm
Subject: Re: Why User Defined Exceptions?
Hi waseem
wirtting our own exception is always better because when we wirte program we know
where errors are generated so, i think it is better to have exceptions
bye....
------------------------------------------------------------
Message no. 6888
Posted by Phanindra Maddhi (pmaddhi) on Thursday, March 9, 2006 12:16pm
Subject: can any one define this or explain what is the use of this exception
Hi
As we are started thinking about execptions, then can any one define what is pragma
EXCEPTION INIT and explain what is the use of it.
------------------------------------------------------------
Message no. 6890[Branch from no. 6888]
Posted by Krishnamurth Ashwini (kashwini) on Thursday, March 9, 2006 12:53pm
Subject: Re: can any one define this or explain what is the use of this exception
Phanindra,
The following is an excerpt from this website www.cs.umb.edu.
To handle error conditions (typically ORA- messages) that have no
predefined name, you must use the OTHERS handler or the pragma
EXCEPTION_INIT. A pragma is a compiler directive that is processed at
compile time, not at run time.
In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an
exception name with an Oracle error number. That lets you refer to any
internal exception by name and to write a specific handler for it. When
you see an error stack, or sequence of error messages, the one on top is
the one that you can trap and handle.
You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL
block, subprogram, or package using the syntax
PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);
where exception_name is the name of a previously declared exception and
the number is a negative value corresponding to an ORA- error number.
The pragma must appear somewhere after the exception declaration in the
same declarative section.
Ash
------------------------------------------------------------
Message no. 6893[Branch from no. 6888]
Posted by Bikash Adhikari (badhikari) on Thursday, March 9, 2006 7:10pm
Subject: Re: can any one define this or explain what is the use of this exception
Agreeing with Ash, once the exception is defined (by us), it is no longer undefined, and we can use it in our EXCEPTION block just as other predefined exceptions and then bind (or INITialize them (using a PRAGMA EXCEPTION_INIT) to some undefined system exception (assuming we have the error code [number]).
Thank you.
BIkash
------------------------------------------------------------
Message no. 6894[Branch from no. 6888]
Posted by Sagun Piya (srpiya2) on Friday, March 10, 2006 2:04am
Subject: Re: can any one define this or explain what is the use of this exception
very helpful stuffs from Ashwini and Bikash. i hope this may help me.
Sagun
------------------------------------------------------------
Message no. 6903[Branch from no. 6887]
Posted by Imran Pathan (ipathan) on Friday, March 10, 2006 4:34am
Subject: Re: Why User Defined Exceptions?
You are right phani we can know the errors while writing the program itself. That is a
huge advantage.
Imran
------------------------------------------------------------
Message no. 6904[Branch from no. 6888]
Posted by Imran Pathan (ipathan) on Friday, March 10, 2006 4:45am
Subject: Re: can any one define this or explain what is the use of this exception
Pragma EXCEPTION_INIT
To handle error conditions that have no predefined name, you must use
the pragma EXCEPTION_INIT. A pragma is a compiler directive that is processed at
compile time, not at run time.
In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception
name with an Oracle error number. That lets you refer to any internal exception by name
and to write a specific handler for it. When you see an error stack, or sequence of error
messages, the one on top is the one that you can trap and handle.
Pragma exception_init
Coding
You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block,
subprogram, or package using the syntax
PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);
where exception_name is the name of a previously declared exception and the number is
a negative value corresponding to an ORA- error number. The pragma must appear
somewhere after the exception declaration in the same declarative section.
Example
DECLARE deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
...
-- Some operation that causes an ORA-00060 error (see locking)
EXCEPTION
WHEN deadlock_detected THEN
-- handle the error
WHEN OTHER
-- handle errors
END;
------------------------------------------------------------
Message no. 6905[Branch from no. 6890]
Posted by Gnaneshwar Bukka (gbukka) on Friday, March 10, 2006 9:24am
Subject: Re: can any one define this or explain what is the use of this exception
Good explanation guys....looks new......
Gnaneshwar Bukka.
------------------------------------------------------------
Message no. 6907[Branch from no. 6869]
Posted by Phanindra Maddhi (pmaddhi) on Friday, March 10, 2006 3:06pm
Subject: Re: Compilation Error
hi Rohini
The problem is u entered L instead of 1 if u correct, it will work u can
observe this error identifier symbol 'L'
------------------------------------------------------------
Message no. 6908[Branch from no. 6837]
Posted by Alex Auffenorde (aaauffenorde) on Friday, March 10, 2006 4:20pm
Subject: Re: Why User Defined Exceptions?
I agree with Harold, they are more convienient than coding multiple checks. For
example, this webiste offers many built in exceptions to oracle and the ORA error
messages that they coorespond to. They simply make coding easier and cleaner.
http://www.lc.leidenuniv.nl/awcourse/oracle/appdev.920/a96624/07_errs.htm
Hope this helps,
Alex Auffenorde
------------------------------------------------------------
Message no. 6909[Branch from no. 6907]
Posted by Venkat Munagala (vrmunagala) on Friday, March 10, 2006 6:02pm
Subject: Re: Compilation Error
Thanks for the information Phani. It really helped.
------------------------------------------------------------
Message no. 6910
Posted by Kelsey Pooley (kjpooley) on Saturday, March 11, 2006 9:33am
Subject: SQL format
Can anyone tell me how to save my script in sql format? I thought you just had to add the .sql to the end of your filename
when you created a spool file.
Thanks,
Kelsey
------------------------------------------------------------
Message no. 6911[Branch from no. 6908]
Posted by Harold Harris (hjharris2) on Saturday, March 11, 2006 10:48am
Subject: Re: Why User Defined Exceptions?
Thanks Alex. I hope everyone has an enjoyable Spring Break.
HH
------------------------------------------------------------
Message no. 6912[Branch from no. 6910]
Posted by Krishnamurth Ashwini (kashwini) on Saturday, March 11, 2006 10:50am
Subject: Re: SQL format
Kelsey,
You can save the script directly from the SQL prompt by using the
following command
SQL>SAVE C:\temp\SQL\project5.sql
Type SAVE followed by any required path.
Ash
------------------------------------------------------------
Message no. 6913[Branch from no. 6912]
Posted by Kelsey Pooley (kjpooley) on Saturday, March 11, 2006 10:53am
Subject: Re: SQL format
Thanks Ash. You're always so helpful!
Kelsey
------------------------------------------------------------
Message no. 6914[Branch from no. 6913]
Posted by Krishnamurth Ashwini (kashwini) on Saturday, March 11, 2006 10:56am
Subject: Re: SQL format
You are most welcome :-)
Ash
------------------------------------------------------------
Message no. 6915[Branch from no. 6910]
Posted by Alex Auffenorde (aaauffenorde) on Saturday, March 11, 2006 3:25pm
Subject: Re: SQL format
I usually type up my SQL file in a text editor like notepad. All you do
is 'Save As' then the name you wish to name your file and add the .sql
at the end. Under 'File Type' select 'All Files' and it will be just
like a script. You can also 'Copy and Paste' the text file into the
SQLPLUS prompt.
Hope this helps
Alex
------------------------------------------------------------
Message no. 6916[Branch from no. 6910]
Posted by Waseem Manzoor (wmanzoor) on Saturday, March 11, 2006 4:04pm
Subject: Re: SQL format
Hey Kelsey,
I think HH and Ash explained it well. You can do it in one of both ways
Thanks
Waseem Manzoor
------------------------------------------------------------
Message no. 6924[Branch from no. 6910]
Posted by Swarna Bangaru (sbangaru) on Saturday, March 11, 2006 5:51pm
Subject: Re: SQL format
As everyone adviced, you can save with .SQL extension on a notepad, or with SAVE
command in SQL*Plus.
I think a spool file saves the result of a program on SQL*plus.
------------------------------------------------------------
Message no. 6925[Branch from no. 6908]
Posted by Gwendolyn Mobley (gdmobley) on Saturday, March 11, 2006 5:57pm
Subject: Re: Why User Defined Exceptions?
Alex,
thanks for the article, good source of info.
------------------------------------------------------------
Message no. 6926[Branch from no. 6911]
Posted by Gwendolyn Mobley (gdmobley) on Saturday, March 11, 2006 5:58pm
Subject: Re: Why User Defined Exceptions?
H,
I plan too since I'll be in Japan for 3 weeks then on to London.
------------------------------------------------------------
Message no. 6927[Branch from no. 6826]
Posted by Swarna Bangaru (sbangaru) on Saturday, March 11, 2006 5:58pm
Subject: Re: Why User Defined Exceptions?
user defined exceptions handle exceptions that will not cause an oracle runtime error,
but require exception handling to enforce business rules ot ensure the integrity of
database.
------------------------------------------------------------
Message no. 6928[Branch from no. 6857]
Posted by Gwendolyn Mobley (gdmobley) on Saturday, March 11, 2006 6:00pm
Subject: Re: Application logs
good info, thanks
------------------------------------------------------------
Message no. 6929[Branch from no. 6915]
Posted by Gwendolyn Mobley (gdmobley) on Saturday, March 11, 2006 6:10pm
Subject: Re: SQL format
Alex,
I follow your procedure as well for my .sql scripts.
Gwen
------------------------------------------------------------
Message no. 6930[Branch from no. 6929]
Posted by Kelsey Pooley (kjpooley) on Saturday, March 11, 2006 6:15pm
Subject: Re: SQL format
Thank you for your help everyone. I appreciate it.
Kelsey
------------------------------------------------------------
Message no. 6933[Branch from no. 6855]
Posted by Krupa Kandavalli (kkandavalli) on Saturday, March 11, 2006 6:30pm
Subject: Re: Application logs
Good Information waseem,
Thank you
------------------------------------------------------------
Message no. 6934[Branch from no. 6908]
Posted by Krupa Kandavalli (kkandavalli) on Saturday, March 11, 2006 6:31pm
Subject: Re: Why User Defined Exceptions?
Thanks for the link Alex,
Very informative.
------------------------------------------------------------
Message no. 6936[Branch from no. 6867]
Posted by Gnaneshwar Bukka (gbukka) on Saturday, March 11, 2006 7:16pm
Subject: Re: Compilation Error
I had the same problem...but when I changed the 'L' to '1' and two more changes,
everything was fine.....
Gnaneshwar Bukka.
------------------------------------------------------------
Message no. 6937[Branch from no. 6910]
Posted by Gnaneshwar Bukka (gbukka) on Saturday, March 11, 2006 7:18pm
Subject: Re: SQL format
Easiest way...save the file in .SQL format......take care...
Gnaneshwar Bukka.
------------------------------------------------------------
Message no. 6942[Branch from no. 6915]
Posted by Kelli Lemon (kmlemon) on Saturday, March 11, 2006 7:41pm
Subject: Re: SQL format
That's the way I usually do it also, it seems simpler
Kelli
------------------------------------------------------------