Re: SQL Plus EXIT verb does it work?

Bob Hunchberger wrote:
>
> The following lines are the content of a sqlplus script. I want to pass the
> value of id back via sqlplus to the OS (in this case HP-UX) and I have not
> been able to get it to return the value. I want the unix shell script to
> check the return code of the sqlplus script I executed so I can do some
> processing. Currently, all I get when I do an echo $? is a 0 or a 1. The
> value of id is really -10. Is it possible to do this or am I dreaming?
>
> thanks for you help
>
> bob H.
> hunchberger_at_lessinc.com
>
> variable id number
> begin
> testa ('h',:id);
> end;
> /
> exit id
>
> Oracle7 Server Release 7.3.4.2.0 - Production
> With the distributed, replication, parallel query and Spatial Data options
> PL/SQL Release 2.3.4.2.0 - Production
>
> PL/SQL procedure successfully completed.
>
> Usage: { EXIT | QUIT }
> [ SUCCESS | FAILURE | WARNING | n | Variable ] [ COMMIT | ROLLBACK ]
> Disconnected from Oracle7 Server Release 7.3.4.2.0 - Production

Do it like this:

#!/bin/ksh
MyVar=$(sqlplus -s system/manager <<sqlEOF whenever sqlerror exit failure;
set feedback off;
set pages 0;
variable id number;
begin
select 1
into :id
from dual;
end;
.
/
print id
exit success;
sqlEOF)
print -u1 $MyVar;
exit 0;

......................................................................
......................................................................
......................................................................
......................................................................
......................................................................
......................................................................
......................................................................
......................................................................
......................................................................
......................................................................
......................................................................
......................................................................
......................................................................
......................................................................
......................................................................
......................................................................
......................................................................
......................................................................