Re: EXCEPTION SPOOLING

Sybrand -

OK ! OK ! I get the drift.

2 of the top 6 contributors, you and Jonathan Lewis, to this Newsgroup cannot be wrong.

I did not mean to offend any of you TRUE ORACLE GURUs by using Oracleguru tag and I do not profess to be as knowledgeable as some of you guys. That was not the intent. Every week, I learn new Oracle features and add to my Oracle knowledge from gurus like you and Thomas Kyte, Jurij Modic, Arjan Van Bantem and many many more.

I apologize to all of you and many more contributors who were offended by my use of Oracleguru. Believe me, if you guys were not here I will not be surfing this newsgroup either.

As I said in my reply to Jonathan Lewis, I have a non-dynamic web site www.oracleguru.net, a business entity in Harris County, Texas simply named Oracleguru and an email address oracleguru_at_mailcity.com.

I cannot simply divorce myself from these entities. I was lucky to have these names for future business. My intent was to get the word out about my business entity, just like some of you have done.

From now onwards, I will contribute to this and tools newsgroup as Suresh Bhat, but I may sign my posts with my business entity name (unfortunately there is no Inc. or the word Company in my business entity), Oracleguru, www.oracleguru.net, oracleguru_at_mailcity.com. However, in misc newsgroup where mostly job postings are posted I reserve the right to use Oracleguru as my tag.

Now, for the question at hand. I answer the posts with a practical point of view with examples. Many of us do not have time to experiment with some of the new features ( which sometimes takes days), because of deadlines, budget constraints, not much support from Oracle MetalLink or whatever.

I said PL/SQL does not support commands like SPOOL, START, HOST that you can use in SQL*Plus. That did not mean that you cannot do it in other ways in PL/SQL. It may be available in the next release, who knows ? Your interpretation of the original question was different than mine.

By the way, Sybrand, when you log into SQL*Plus, ECHO is off by default.

And also, if you have FEEDBACK ON, you will also get "table created", "index created" messages etc. which the original poster did not want. In my mind these are just slips and not necessarily blantanly .... Take it as a joke. OK !

Thanks a lot.

Suresh

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in article <36EEC68A.9E36F775_at_sybrandb.demon.nl>...
> No, this is untrue.
>
> First of all, there is no reason why this can't be done using simple
sqlplus.
> Simply make sure echo is off
> and feedback is on. Make sure there is a statement
> prompt Table xyz
> before every create table statement.
> Make sure there is a spool command before the operation starts, and this
will
> be spooled to the file regardless of echo
> This is how many Oracle provided dictionary script work.
>
> Then PL/SQL does support spool as long as you do it by using the utl_file
> package, it does support host as long you do it by way of the dbms_pipe
> package, and the dbms_sql package will simply execute ANY pl/sql
statement
> Finally, as you can raise your own exceptions, you can use exceptions to
trap
> statement errors, no problem.
> Also you could use the format_call_stack function from the dbms_utility
package
> anywhere.
> I would recommend reading Oracle PL/SQL programming by Steven Feuerstein,
> published by O'Reilly to both of you, or better to anyone.
> I will refrain from commenting on the email-address of the original
poster,
> though it does.. (Yes!) I will postpone that to the next time he posts
replies
> which are so blatantly incorrect and show his ignorance. I have 10 years
> experience and I don't advertise myself as 'guru'. Sorry, I really don't
want
> to insult you, but a bit more modesty may suit you better. I know I'm not
the
> first to send such a flame to you, and I saw how you replied. A few more
IMOs
> and there would have been no problem.
>
> Regards,
>
> Sybrand Bakker, Oracle DBA
>
>
> "Oracleguru, Suresh Bhat" wrote:
>
> > Hi -
> >
> > AFAIK PL/SQL does not support commands like SPOOL, HOST or START that
you
> > can use in SQL*Plus.
> >
> > Furthermore, EXCEPTION processing in PL/SQL referes to exceptions
because
> > of a database operation such as NO_DATA_FOUND, ZERO_DIVIDE etc. and not
> > necessarily statement errors that you are after.
> >
> > Suresh
> >
> > chandrasekar_at_my-dejanews.com wrote in article
> > <7c7r18$c9a$1_at_nnrp1.dejanews.com>...
> > > Hi all,
> > > I've a reasonable requirement for which I am unable to find a
> > solution .
> > >
> > > Here my problem goes :
> > >
> > > "I've a create script which creates 75 tables (ALL CREATE STATEMENTS
ARE
> > GIVEN
> > > ONE AFTER OTHER ). Once if I run the script,it should write the
errors(if
> > any)
> > > in a file. I don't want to spool the entire operation . I want to
spool
> > only
> > > the error message(if any) . Is there a way to do this using PL*SQL
> > procedures
> > > which in turn calls the script and writes the error messages ."
> > >
> > > ALL YOUR INNOVATIVE IDEAS ARE WELCOME
> > >
> > > Thankx in advance
> > > chandrasekar
> > >
> > >
> > > -----------== Posted via Deja News, The Discussion Network
==----------
> > > http://www.dejanews.com/ Search, Read, Discuss, or Start Your
Own
> >
> > >
>
Received on Wed Mar 17 1999 - 11:50:04 CST