Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to get 'create or replace package' to indicate an error to SQL*Plus?

Re: How to get 'create or replace package' to indicate an error to SQL*Plus?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1997/02/27
Message-ID: <3314f081.3602009@nntp.mediasoft.net>#1/1

create a procedure like:

create or replace procedure test_error( p_name in varchar2 ) as

        n number;
begin

        select count(*) into n
          from user_errors
         where name = upper(p_name);
 
        if ( n > 0 ) then
                raise_application_error( -20000, n || ' Errors on ' || p_name);
        end if;

end;
/

and then code your script as:

#!/bin/sh  

sqlplus scott/tiger <<EOF
create or replace package foo
as

        --this is an error;
        x number;

end;
/
whenever sqlerror exit failure
exec test_error( 'foo' )
EOF
echo $?

use the procedure test_err to check for errors and let it raise a database error. The problem is that the create or replace statement 'succeeded'. the compilation failed but the statement was 'successful'....

If you uncomment the comment int he package above, the script will exit with 1, otherwise 0....

On 26 Feb 1997 20:08:08 GMT, dcs_at_proton.chem.yale.edu (Dave Schweisguth) wrote:

>Hi all,
>
>We have here a good-sized pile of PL/SQL packages, which we keep in files
>like this:
>
>-- snip --
>create or replace package foo
>is
> procedure bar;
>end;
>/
>show errors;
>
>create or replace package body foo
>is
> procedure bar
> is
> begin
> end;
>end;
>/
>show errors;
>-- snip --
>
>Each file is named for the package it contains. To compile the package, one
>says "@foo" in SQL*Plus.
>
>I'd like to be able to compile a file in the above format on the Unix command
>line and detect whether it has compiled correctly using the sqlplus'
>executable's return value. The following two ditties
>
>> sqlplus -S userid/password <<EOF
>whenever sqlerror exit failure
>select sysdate from dual;
>exit
>EOF
>> echo $?
>0
>
>> sqlplus -S userid/password <<EOF
>whenever sqlerror exit failure
>select sysdate from my butt;
>exit
>EOF
>> echo $?
>1
>
>do what I expect, but this
>
>> sqlplus -S userid/password <<EOF
>whenever sqlerror exit failure
>@foo
>exit
>EOF
>(error message to the effect that the above package body has errors)
>> echo $?
>0
>
>does not: it exits successfully, even though the package body has an error.
>(I used the package above, and the error is that the body is empty.)
>
>Is there a way for me to get SQL*Plus to notice when a compilation fails
>and exit with an appropriate status?
>
>Solaris 2.5, Oracle 7.3.2, SQL*Plus 3.3.2.
>
>Thanks,
>
>--
>| Dave Schweisguth For purposes of complying with |
>| dcs_at_proton.chem.yale.edu (MIME OK) the New Jersey Right to Know Act: |
>| http://proton.chem.yale.edu/~dcs/ Contents partially unknown. |
>| Yale Depts. of MB&B & Chemistry Phone: 203-432-5208 Fax: 203-432-6144 |

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com


statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Thu Feb 27 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US