Re: SQLPLUS format question

From: Johan Muller <johan.muller_at_gmail.com>
Date: Mon, 14 Aug 2006 14:01:09 -0500
Message-ID: <3b9442840608141201q524ba3f5sc134b27bbdf46475@mail.gmail.com>

Nope - still does it (includes the / though :) )

"[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
 24 /
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 offexit
[oracle_at_nmsdevel2 sql]$
"

On 8/14/06, Rich Amick <rAmick_at_dotster.com> wrote:
>
> 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
>

-- 
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 - 14:01:09 CDT