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