SQL*Plus and Shell Programming

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Peter
    #1

    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_TABL E ( 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:

    SQLSET TERMOUT ON
    SQLSET FEEDBACK ON
    SQLSET SQLBLANKLINES ON
    SQLSET TIMING ON
    SQLSET SERVEROUTPUT ON SIZE 1000000
    SQLSQLSQLSQLSQL SQLSQL 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!
    Tags: None
    • sybrandb@yahoo.com
      #2
      Re: SQL*Plus and Shell Programming

      phess@eng.utah. edu (Peter) wrote in message news:<471b84b1. 0401281430.6a10 eb00@posting.go ogle.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_TABL E ( 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:
      >
      SQLSET TERMOUT ON
      SQLSET FEEDBACK ON
      SQLSET SQLBLANKLINES ON
      SQLSET TIMING ON
      SQLSET SERVEROUTPUT ON SIZE 1000000
      SQLSQLSQLSQLSQL SQLSQL 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

        Comment

        • Peter
          #3
          Re: SQL*Plus and Shell Programming

          sybrandb@yahoo. com wrote in message news:<a1d154f4. 0401290047.7430 2772@posting.go ogle.com>...
          phess@eng.utah. edu (Peter) wrote in message news:<471b84b1. 0401281430.6a10 eb00@posting.go ogle.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_TABL E ( 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:

          SQLSET TERMOUT ON
          SQLSET FEEDBACK ON
          SQLSET SQLBLANKLINES ON
          SQLSET TIMING ON
          SQLSET SERVEROUTPUT ON SIZE 1000000
          SQLSQLSQLSQLSQL SQLSQL 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: SQLSQLSQLSQLSQL SQLSQL 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!

            Comment

            Previous template Next
            Working...