RE: SQLPLUS format question

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Mon, 14 Aug 2006 16:07:29 -0400
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC0F40E76B@ENYC11P32005.corpny.csfb.com>

The trick is to enter the statement (so that it goes into the SQL buffer), followed by a carriage return at the next SQL*Plus prompt. That will take you out of SQL input mode. You then issue your spool command, and finally a / at the next prompt. Here's an example:  

P_BAUMGA2_at_DNYTP102.BLAH.COM> set pages 0 feedb off trimspool on P_BAUMGA2_at_DNYTP102.BLAH.COM> select
  2 'alter index '||owner||'.'||index_name||' rebuild;'   3 from dba_indexes where index_owner='XXX'

  4                                       <--Just hit carriage return here

P_BAUMGA2_at_DNYTP102.BLAH.COM> spool foo.sql P_BAUMGA2_at_DNYTP102.BLAH.COM> /

alter index XXX.A_PK rebuild;
alter index XXX.B_PK rebuild;
alter index XXX.C_PK rebuild;
alter index XXX.D_PK rebuild;
alter index XXX.E_PK rebuild;

Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Johan Muller Sent: Monday, August 14, 2006 2:51 PM
To: Oracle L (E-mail); oracle-l-bounce_at_freelists.org Subject: SQLPLUS format question

Run the following query , trying to build a dynamic sql statement, but CANNOT suppress the statement itself from spooling (Redhat EL 4/Ora 10.1.3):

"

set ECHO OFF
set TERMOUT OFF
set FEEDBACK OFF
set VERIFY OFF
set PAGESIZE 0
set LINESIZE 80
set HEADING OFF
SPOOL /u01/app/oracle/admin/prod/sql/alarm_hist_csv.sql SELECT 'SELECT '
FROM dual
UNION ALL
SELECT DECODE(ROWNUM, 1, SUBSTR (output2, INSTR (output2, '||') + 2), output2) output   FROM (SELECT DECODE (data_type,

                          'NUMBER', '||',
                          'DATE', '||',
                          '||''"''||'
                         )
               || 'RTRIM(' 
               || column_name
               || ')'
               || DECODE (data_type,
                          'NUMBER', '||'',''',
                          'DATE', '||'',''',
                          '||''",''' 
                         )  output2
          FROM user_tab_columns
         WHERE table_name = UPPER ('ALARM_HIST_TBL')
         ORDER BY column_id) 

UNION ALL
SELECT 'FROM ' || UPPER ('ALARM_HIST_TBL')|| ';' FROM dual;

spool off

"

Here is the output:

"

[oracle_at_nmsdevel2 sql]$ cat alarm_hist_csv.sql SQL> SELECT 'SELECT '
  2 FROM dual
  3 UNION ALL
  4 SELECT DECODE(ROWNUM, 1, SUBSTR (output2, INSTR (output2, '||') + 2), output2) output   5 FROM (SELECT DECODE (data_type,

  6                            'NUMBER', '||',
  7                            'DATE', '||',
  8                            '||''"''||'
  9                           ) 
 10                 || 'RTRIM('
 11                 || column_name
 12                 || ')'
 13                 || DECODE (data_type,
 14                            'NUMBER', '||'',''',
 15                            'DATE', '||'',''', 
 16                            '||''",'''
 17                           )  output2
 18            FROM user_tab_columns
 19           WHERE table_name = UPPER ('ALARM_HIST_TBL')
 20              ORDER BY column_id) 

 21 UNION ALL
 22 SELECT 'FROM ' || UPPER ('ALARM_HIST_TBL')|| ';'  23 FROM dual;
SELECT
RTRIM(ALARM_ID)||','

||RTRIM(ARHANDLE)||','
||RTRIM(WACN)||','
||RTRIM(SYSTEM)||','
||RTRIM(RFSS)||',' 
||RTRIM(SITE)||','
||'"'||RTRIM(IPADDRESS)||'",'
||RTRIM(SEVERITY)||','
||'"'||RTRIM(CLEARED)||'",'
||'"'||RTRIM(ACKNOWLEDGED)||'",'
||'"'||RTRIM(ACK_TEXT)||'",'
||RTRIM(TIME_DETECTED)||','
||RTRIM(NUM_SEEN)||','
||RTRIM(LAST_SEEN)||','
||'"'||RTRIM(ALIAS_NAME)||'",'
||RTRIM(ELEM_TYPE)||','
||RTRIM(ELEM_INST)||','
||RTRIM(ALARM_CODE)||','
||'"'||RTRIM(ALARM_DESC_TEXT)||'",' 
||'"'||RTRIM(ALARM_DIAG_TEXT)||'",'
||RTRIM(ACK_TIME)||','
||'"'||RTRIM(ACK_USERID)||'",'
||RTRIM(CLEAR_TIME)||','
||'"'||RTRIM(CLEAR_USERID)||'",'
||RTRIM(AGENCY_ID)||','
||RTRIM(CATEGORY)||','

FROM ALARM_HIST_TBL;
SQL>
SQL> spool off
"

What gives? I know I'm overlooking the obvious, but which is it?

-- 
Johan Muller
Oracle DBA
(214) 676 2147 anytime. 



"Democracy is two wolves and a lamb voting on what to have for lunch.

Liberty is a well-armed lamb contesting the vote." --Benjamin Franklin




==============================================================================
Please access the attached hyperlink for an important electronic communications disclaimer: 

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Aug 14 2006 - 15:07:29 CDT