Re: Hide Oracle error messages

From: <kchami_at_my-dejanews.com>
Date: Mon, 01 Jun 1998 17:29:10 GMT
Message-ID: <6kuod5$cr4$1@nnrp1.dejanews.com>

Hi Keith

Actually, the solution you suggested is the one I'm trying to put in place. But, since I'm far from being a PL/SQL guru, this solution didn't work the way I thought.

Following is the script I'm running. Would you spare me a couple of minutes to find out what's wrong in my approach?

---

create or replace procedure drop_index as

  err_code number;
  cid integer;

begin

        cid := dbms_sql.open_cursor;

        dbms_SQL.PARSE(cid, 'drop index whaterever', dbms_sql.v7);

    err_code := sqlcode;

        dbms_sql.close_cursor(cid);

exception
  when others then

	if err_code = 1418 then -- specified index does not exist
	  null;

    end if;

	dbms_sql.close_cursor(cid);
	  raise;

end;
/

---

The procedure is created successfully but acts exactly like if the defined exception didn't run. I have the follwoing output:

SQL> execute drop_index
begin drop_index; end;

*
ERROR at line 1:

ORA-01418: specified index does not exist
ORA:06512: at "EVA_SERVER_V09_KC.DROP_INDEX", line 15
ORA-06512: AT LINE 1

My problem now is that it seems that the 'sqlcode' does not return the actual error message. What should I use in PL/SQL to catch the errors numbers?

Thank you very much in advance,

Karina

In article <356fe101.10584507_at_read.news.global.net.uk>,   boulkenospam_at_globalnet.co.uk (Keith Boulton) wrote:
>
> On Fri, 29 May 1998 15:51:31 GMT, kchami_at_my-dejanews.com wrote:
>
> >Is there any way to hide ORA-.. error messages?
> >
> >I have a batch installation script that runs into SQL*Plus and generates
> >"normal" error messages, for example, when I drop and index before creating
> >it, just to check if it is not already there.
> >Therefore, the users running the script see these messages and are not able
to
> >make the difference between "normal" error messages and "abnormal" ones!
> >I would like to be able not to display the foreseeable error messages in
the
> >log. How can I do that?
> >
>
> Interestingly, oracle don't bother doing this, so if you use the
> database creation wizard on NT you get a 5MB (137000 lines) log file
> that you're supposed to check manually for 'unexpected' errors.
>
> The easiest way to suppress these messages is probably to wrap your
> drop statements in an anonymous pl/sql block using dbms_sql to issue
> the ddl and catching all exceptions with a null exception handler.
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon Jun 01 1998 - 12:29:10 CDT