RE: SQLPLUS format question

From: Rich Amick <rAmick_at_dotster.com>
Date: Mon, 14 Aug 2006 11:54:01 -0700
Message-ID: <19775.63831155581648.scalix0.corp.dotster.net@MHS>

Don't use the semi colon - use the slash after starting the spool.  

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Johan Muller
Sent: Monday, August 14, 2006 11:51 AM
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


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

Received on Mon Aug 14 2006 - 13:54:01 CDT