Xref: alice comp.databases.oracle.server:24123
Path: alice!news-feed.fnsi.net!priori!logbridge.uoregon.edu!enews.sgi.com!EU.net!news0.Belgium.EU.net!newsr.Belgium.EU.net!not-for-mail
From: Bram Stieperaere <bse@N_O_S_P_A_Msodexho-pass.be>
Newsgroups: comp.databases.oracle.server
Subject: Re: How to know if a procedure exists?
Date: Tue, 07 Jul 1998 10:37:25 +0200
Organization: EUnet Belgium, Leuven, Belgium
Lines: 41
Message-ID: <35A1DE45.D07D7281@N_O_S_P_A_Msodexho-pass.be>
References: <6ns2o0$f02$1@news.rain.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Mailer: Mozilla 4.03 [en] (Win95; I)

declare

lv_ProcExists integer;

begin
    select        count(*)
        into       lv_ProcExists
        from     sys.all_objects
        where   (owner = '<schemaname>') and
                    (object_type = 'PROCEDURE') and
                    (object_name = '<name of procedure>') and
                    (status = 'VALID')
    ;
    dbms_output.put_line(lv_ProcExists);
end;

Jacques Sireude wrote:

> Hi,
>     What is the sql statement to know if a procedure does exists in the
> database?
>
> Thanks for any help.
>
> Jacques.
>
> ----------------------------------------------------------------------------
> ----------------
> Jacques SIREUDE
> 6585 PICASSO Rd Apt#3
> ISLA VISTA CA 93117
> USA
> tel/fax: 1-805-685-9208
> e-mail: sireude@eiffel.com
>              jacques.sireude@enst-bretagne.fr
> http://www-eleves.enst-bretagne.fr/~sireude
> ----------------------------------------------------------------------------
> ----------------