RE: SQLPLUS format question
- From: "Rich Amick" <rAmick@xxxxxxxxxxx>
- To: <johan.muller@xxxxxxxxx>, "'Oracle L \(E-mail\)'" <oracle-l@xxxxxxxxxxxxx>, <oracle-l-bounce@xxxxxxxxxxxxx>
- Date: Mon, 14 Aug 2006 11:54:01 -0700
Don't use the semi colon - use the slash after starting the spool.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Johan Muller
Sent: Monday, August 14, 2006 11:51 AM
To: Oracle L (E-mail); oracle-l-bounce@xxxxxxxxxxxxx
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@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
Other related posts:
