By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

SQL*Plus and Shell Programming

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.