469,592 Members | 2,022 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,592 developers. It's quick & easy.

SQL*Plus and Shell Programming

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!
Jul 19 '05 #1
2 12415
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
Jul 19 '05 #2
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!
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Ruben Schoenefeld | last post: by
3 posts views Thread by khangu | last post: by
3 posts views Thread by Peter | last post: by
2 posts views Thread by Ant | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.