SQL>
SQL>
SQL> @f7.sql
SQL> --
SQL> drop table t;
 
Table dropped.
 
SQL> drop package pkg_load;
 
Package dropped.
 
SQL> --
SQL> create table t (a number(2), b varchar2(20));
 
Table created.
 
SQL> --
SQL> create or replace package pkg_load as
  2    procedure pr_t_load (t varchar2, x out varchar2);
  3  end;
  4  /
 
Package created.
 
SQL> show errors
No errors.
SQL> --
SQL> create or replace package body pkg_load as
  2    procedure pr_t_load (t varchar2, x out varchar2) is
  3      e_too_big_number exception;
  4      pragma exception_init(e_too_big_number, -1438);
  5    begin
  6      insert into t (a, b)
  7      select rownum, t
  8      from all_objects
  9      where rownum <= 10;
 10      x := 'Success';
 11    exception
 12      when e_too_big_number then
 13        x := 'Failure';
 14    end;
 15  end;
 16  /
 
Package body created.
 
SQL> show errors
No errors.
SQL>
SQL>

$
$
$ cat -n f7.sh
     1  #!/usr/bin/bash
     2  PLSQLCALL=$2
     3
     4  sqlplus -s test/test <<END
     5  whenever sqlerror exit failure
     6  set serveroutput on timing on lines 150 feedback on
     7  declare
     8  xx varchar2(100);
     9  begin
    10  $PLSQLCALL;
    11  dbms_output.put_line('proc status: '||xx);
    12  if xx <> 'Success' then
    13    raise_application_error (-20001,'Failed');
    14  end if;
    15  end;
    16  /
    17  exit;
    18  END
    19
$
$
$ ./f7.sh secload "test.pkg_load.pr_t_load('SOME_TEXT_HERE', xx)"
proc status: Success
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.01
$
$

SQL>
SQL>
SQL> truncate table t;
Table truncated.
SQL>
SQL> alter table t modify (a number(1));
Table altered.
SQL>
SQL>

$
$ ./f7.sh secload "test.pkg_load.pr_t_load('SOME_TEXT_HERE', xx)"
proc status: Failure
declare
*
ERROR at line 1:
ORA-20001: Failed
ORA-06512: at line 7

$
$