1

How to stop terminal output in Unix while SQLPLUS and spooling the output in a file?

I tried serveroutput, echo, termout, feedback in OFF mode but no luck.

Also it is showing logon details as well .

sqlplus -s  $LOGON  << EOF
    WHENEVER OSERROR EXIT 9;
    WHENEVER SQLERROR EXIT 9;
    set serveroutput off;
    set echo off;
    set termout off;
    set feedback off;
    set heading off;
    set linesize 10000;
    set colsep "|";
    set trims on;
    set trimspool on;
    set pagesize 0;
    spool Summary.txt
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY';
    Select * from Customer;
    spool off;
    EXIT;
    EOF

PS : I am calling this statement in a Unix Script

sharan jain
  • 85
  • 1
  • 11
  • Possible duplicate of [Oracle SqlPlus - saving output in a file but don't show on screen](https://stackoverflow.com/questions/6813210/oracle-sqlplus-saving-output-in-a-file-but-dont-show-on-screen) – Andrei Odegov Feb 20 '19 at 08:28
  • @AndreiOdegov , Termout not working as per the link you provided – sharan jain Feb 20 '19 at 09:17

2 Answers2

2

set termout off; - this is correct option but it is not working with interactive mode.

Options:

  1. Save your script to file and execute with sqplus logon @file_name (better)
  2. redirect output to /dev/null or other file (sqlplus balbalb) > /dev/null
Arkadiusz Łukasiewicz
  • 6,241
  • 1
  • 11
  • 17
0

There are many new features for the entire Oracle 12 Release family that can help. For example, in 12.2 there is a new ‘history’ command which you can show and run your past commands in a session without having to re-enter it. To turn it on, you can either ‘set history on’ which defaults to 100 lines of history, or you can specify the number of lines that you want to keep. To view your history, you simply type ‘history’ at the command prompt and to execute a past command, you just enter ‘run LineNum’ for the SQL statement you want to execute.

There is also the ‘SET MARK CSV’ command as it’s a quick way to format a result set for Excel spreadsheets or loading data sets into other database types. The example below shows how to use ‘SET MARK CSV’, ‘SET ROWLIMT’ and ‘SET FEEDBACK ON SQL_ID’ commands.

Example 2 enter image description here

The new SQL Plus features and when they were released are listed below. Also, there is a new parameter ‘-f ‘(for fast), which you can pass to SQL Plus on the command line to speed up your session. Version 12.2 - HISTORY SET HIST[ORY] {ON | OFF | n} - - Can create CSV format SET MARK CSV ON [DELIMI{TER} character] [QUOTE {ON|OFF}] - SET STATEMENTC[ACHE] {0 | n} - SET FEEDBACK ONLY Instead of just [ON | OFF] - Sqlplus –f @test.sql Sets ARRAYSIZE 100, LOBPREFETCH 16384, PAGESIZE 50000, ROWPREFETCH 2, STATEMENTCACHE 20 Make its run faster Version 18c - SET LINESIZE WINDOW - SET FEEDBACK ON SQL_ID - SET ROWLIMIT n Version 19c - EZConnect Improvements Accepts list of name value pairs

Finally, the only improvement I could find in 19c for sqlplus is an eZconnect improvement where you can include tag/value pairs on the command line at connect time to modify other options. This gives a quick way to connect without having to edit the SQLNET.ora file. An example of using this feature is listed below:

sqlplus soe/soe@//hostname:1521/sales.company.com?connect_time=60& transport connect_timeout=30&retry_count=4

Janis
  • 21
  • 4