Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> SQLPLUS format question

SQLPLUS format question

From: Johan Muller <johan.muller_at_gmail.com>
Date: Mon, 14 Aug 2006 13:50:37 -0500
Message-ID: <3b9442840608141150p34219cf2u8f531727b7922126@mail.gmail.com>


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:50:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US