SQL*Plus and Shell Programming

[Peter]

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}@${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!

[sybrandb]

ph***@eng.utah.edu (Peter) wrote in message news:<47**************************@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}@${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

[Peter]

sy******@yahoo.com wrote in message news:<a1**************************@posting.google. com>...

ph***@eng.utah.edu (Peter) wrote in message news:<47**************************@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}@${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!