Re: SQL*Plus and Shell Programming

sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0401290047.74302772_at_posting.google.com>...
> phess_at_eng.utah.edu (Peter) wrote in message news:<471b84b1.0401281430.6a10eb00_at_posting.google.com>...
> > I run most of my SQL scripts via kornshell on AIX.
> >
> > I use the "here-document" to run some of the smaller ones.
> >
> > Example:
> > #!/bin/ksh
> >
> > # Analyze the table.
> > sqlplus ${SCHEMA_NM}/${SCHEMA_PASSWD}_at_${DB_NM} <<-ANALYZE_TABLE
> > SET TERMOUT ON
> > SET FEEDBACK ON
> > SET SQLBLANKLINES ON
> > SET TIMING ON
> > SET SERVEROUTPUT ON SIZE 1000000
> > SET ECHO ON
> > BEGIN
> > HP.ANALYZE_TABLE ( TABLE_NM => 'ADGPMP' );
> > END;
> > /
> > ANALYZE_TABLE
> >
> > As I look at it from my telnet window I don't see the feedback...
> >
> > Here is what I see:
> >
> > SQL> SET TERMOUT ON
> > SQL> SET FEEDBACK ON
> > SQL> SET SQLBLANKLINES ON
> > SQL> SET TIMING ON
> > SQL> SET SERVEROUTPUT ON SIZE 1000000
> > SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4
> > PL/SQL procedure successfully completed.
> >
> > Elapsed: 00:00:01.01
> >
> > This sure makes log file reading a pain!
> > Does anyone know how I can see the SQL or (PL/SQL in this case) that
> > is being executed?
> >
> > Thanks!
>
> either redirect or tee the output or include a spool command
> (preferred) in your script.
>
> Sybrand Bakker
> Senior Oracle DBA

Thanks for the response!

I usually have a shell script that calls this and logs the whole thing with either a redirect or a tee. The problem is that when I do that still all I see is: SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 . I don't want to spool because I am usually just calling one stored procedure. If I wanted to spool I would probably just call a .sql file via the command line.

How do I get the "here-document" to output the SQL commands to stdout?

Any ideas?

Thanks! Received on Thu Jan 29 2004 - 19:08:06 CET