Re: sqlplus and stdout... SEEING WHAT RAN??

j..._at_cyberpine.com wrote:
> Good day.
>
> What's the best way to see what sqlplus commands were executed in a
> script output? I've tried ECHO ON and SET SERVEROUTPUT ON in my
POSIX
> script, but neither show me the commands executed as they are
> excecuted...
>
>
> the POSIX script:
>
> # Simple Appworx Demo Job
> # Author Juan Pineiro
> set -x
> echo on
> pwd
> id
> sqlplus<<!eof
> user1/pw1_at_db1
> SET ECHO ON
> SET SERVEROUTPUT ON
> desc table1;
> exit
> !eof
>
>
> The script output captured by my scheduler:
>
> + echo on
> on
> + pwd
> /dir1/run
> + id
> uid=119(user1) gid=111(xxx) groups=106(yyy)
> + sqlplus
> + 0< /var/tmp/sh25073.1
>
> SQL*Plus: Release 9.2.0.1.0 - Production on Fri May 13 10:13:38 2005
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> Enter user-name:
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.5.0 - Production
>
> SQL> SQL> SQL> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> f1 NOT NULL NUMBER(10)
> f2 NOT NULL NUMBER(10)
> f3 NOT NULL NUMBER(10)
>
> SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0
-
> 64bit Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.5.0 - Production
>
>
> What Id like to see in this above output is :
>
> desc table1;
>
> So that developers can tell what sqlplus was trying to execute.
>
> Thanks for any help or information.

AFAIK you cannot the way you want it. All the stuff between !eof is Standard Input (stdin) and what you see after the command is run is whatever written to Standard Output (stdout). Anything sent back from the database is written to stdout.. which does not include the actual command unless there is a syntax error in the query.

Unless someone else can suggest a fancy way, you can insert a host command just before the query,

sqlplus user/pwd<<EOF
host echo desc user_objects
desc user_objects
exit
EOF Not pretty though!

Regards
/Rauf Received on Fri May 13 2005 - 10:48:37 CDT