Home » SQL & PL/SQL » Client Tools » SQL PLUS set echo off command (Oracle 9i)
SQL PLUS set echo off command [message #418628] Mon, 17 August 2009 21:33 Go to next message
me_arindam
Messages: 26
Registered: March 2008
Location: India
Junior Member
Hi,

My question is very simple.

In my SQL script I am using SET ECHO OFF command to suppress the SQL queries and to get the output only.

Like below:

SQL> set echo off
SQL> set markup html on
SQL> spool on
<br>
SQL&gt; spool test.html
<br>
SQL&gt; select cm_name from cm_details where cm_id in (904,707);
<br>
<p>
<table WIDTH='90%' BORDER='5'>
<tr>
<th scope="col">
CM_NAME
</th>
</tr>
<tr>
<td>
Tek Conversion
</td>
</tr>
<tr>
<td>
Rec SAM
</td>
</tr>
</table>
<p>
SQL&gt; spool off
<br>
SQL&gt; set markup html off
<br>
SQL>

But the HTML file which is being generated is having both. The SQL query as well as the output. I want to get the output only.

Please help.
Re: SQL PLUS set echo off command [message #418632 is a reply to message #418628] Mon, 17 August 2009 22:20 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
your answer is contained below
SQL> help set

 SET
 ---

 Sets a system variable to alter the SQL*Plus environment settings
 for your current session. For example, to:
     -   set the display width for data
     -   customize HTML formatting
     -   enable or disable printing of column headings
     -   set the number of lines per page
 In iSQL*Plus, you can also use the Preferences screen to set
 system variables.

 SET system_variable value

 where system_variable and value represent one of the following clauses:

   APPI[NFO]{OFF|ON|text}                   NUM[WIDTH] {10|n}
   ARRAY[SIZE] {15|n}                       PAGES[IZE] {14|n}
   AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}      PAU[SE] {OFF|ON|text}
   AUTOP[RINT] {OFF|ON}                     RECSEP {WR[APPED]|EA[CH]|OFF}
   AUTORECOVERY {OFF|ON}                    RECSEPCHAR {_|c}
   AUTOT[RACE] {OFF|ON|TRACE[ONLY]}         SERVEROUT[PUT] {ON|OFF}
     [EXP[LAIN]] [STAT[ISTICS]]               [SIZE {n | UNLIMITED}] [FOR[MAT]
   BLO[CKTERMINATOR] {.|c|ON|OFF}             {WRA[PPED] |
   CMDS[EP] {;|c|OFF|ON}                       WOR[D_WRAPPED] |
   COLSEP {_|text}                             TRU[NCATED]}]
   CON[CAT] {.|c|ON|OFF}                   *SHIFT[INOUT] {VIS[IBLE] |
   COPYC[OMMIT] {0|n}                         INV[ISIBLE]}
   COPYTYPECHECK {ON|OFF}                  *SHOW[MODE] {OFF|ON}
   DEF[INE] {&|c|ON|OFF}                   *SQLBL[ANKLINES] {OFF|ON}
   DESCRIBE [DEPTH {1|n|ALL}]               SQLC[ASE] {MIX[ED] |
     [LINENUM {OFF|ON}] [INDENT {OFF|ON}]     LO[WER] | UP[PER]}
   ECHO {OFF|ON}                           *SQLCO[NTINUE] {> | text}
  *EDITF[ILE] file_name[.ext]              *SQLN[UMBER] {ON|OFF}
   EMB[EDDED] {OFF|ON}                      SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
   ESC[APE] {\|c|OFF|ON}                   *SQLPRE[FIX] {#|c}
   FEED[BACK] {6|n|ON|OFF}                 *SQLP[ROMPT] {SQL>|text}
   FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}  SQLT[ERMINATOR] {;|c|ON|OFF}
  *FLU[SH] {ON|OFF}                        *SUF[FIX] {SQL|text}
   HEA[DING] {ON|OFF}                      *TAB {ON|OFF}
   HEADS[EP] {||c|ON|OFF}                  *TERM[OUT] {ON|OFF}
   INSTANCE [instance_path|LOCAL]          *TI[ME] {OFF|ON}
   LIN[ESIZE] {80|n} ({150|n} iSQL*Plus)    TIMI[NG] {OFF|ON}
   LOBOF[FSET] {1|n}                       *TRIM[OUT] {ON|OFF}
   LOGSOURCE [pathname]                    *TRIMS[POOL] {OFF|ON}
   LONG {80|n}                              UND[ERLINE] {-|c|ON|OFF}
   LONGC[HUNKSIZE] {80|n}                   VER[IFY] {ON|OFF}
   MARK[UP] HTML [OFF|ON]                   WRA[P] {ON|OFF}
     [HEAD text] [BODY text] [TABLE text]   XQUERY {BASEURI text|
     [ENTMAP {ON|OFF}]                        ORDERING{UNORDERED|
     [SPOOL {OFF|ON}]                                  ORDERED|DEFAULT}|
     [PRE[FORMAT] {OFF|ON}]                   NODE{BYVALUE|BYREFERENCE|
   NEWP[AGE] {1|n|NONE}                            DEFAULT}|
   NULL text                                  CONTEXT text}
   NUMF[ORMAT] format


 An asterisk (*) indicates the SET option is not supported in iSQL*Plus.
Re: SQL PLUS set echo off command [message #418635 is a reply to message #418632] Mon, 17 August 2009 23:21 Go to previous messageGo to next message
me_arindam
Messages: 26
Registered: March 2008
Location: India
Junior Member
Hi,

Thanks for your help. So if I want to get the output only, how can I get this?

I don't want to have the sql query printed.
Re: SQL PLUS set echo off command [message #418637 is a reply to message #418628] Mon, 17 August 2009 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
oracle@bcm-laptop:~/sql$ cat id9.sql
sqlplus -s dbadmin/admindb
set term off echo off markup html on
spool html.lis
SELECT EXTRACT (DAY    FROM (END_DATE-BEG_DATE))*24*60*60+
       EXTRACT (HOUR   FROM (END_DATE-BEG_DATE))*60*60+
       EXTRACT (MINUTE FROM (END_DATE-BEG_DATE))*60+
       EXTRACT (SECOND FROM (END_DATE-BEG_DATE)) DELTA
from holder
/
spool off
exit
oracle@bcm-laptop:~/sql$ vi id9.sql
oracle@bcm-laptop:~/sql$ sh -x id9.sql
+ sqlplus -s dbadmin/admindb
<p>
<table border='1' width='90%' align='center' summary='Script output'>
<tr>
<th scope="col">
DELTA
</th>
</tr>
<tr>
<td align="right">
 2768398.5
</td>
</tr>
</table>
<p>
Re: SQL PLUS set echo off command [message #418638 is a reply to message #418635] Mon, 17 August 2009 23:34 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
May be, this can help you.

regards,
Delna
Re: SQL PLUS set echo off command [message #418646 is a reply to message #418628] Tue, 18 August 2009 00:01 Go to previous message
Michel Cadot
Messages: 59126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SET ECHO OFF only applies to command inside a script not to interactive command.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Previous Topic: SQL Script to check status of last command
Next Topic: How to pass date dynamically in prompt.
Goto Forum:
  


Current Time: Thu Sep 18 08:56:14 CDT 2014

Total time taken to generate the page: 0.11270 seconds