RE: SQLPLUS format question
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "'johan.muller@xxxxxxxxx'" <johan.muller@xxxxxxxxx>, "Oracle L (E-mail)" <oracle-l@xxxxxxxxxxxxx>, oracle-l-bounce@xxxxxxxxxxxxx
- Date: Mon, 14 Aug 2006 16:07:29 -0400
The trick is to enter the statement (so that it goes into the SQL buffer),
followed by a carriage return at the next SQL*Plus prompt. That will take you
out of SQL input mode. You then issue your spool command, and finally a / at
the next prompt. Here's an example:
P_BAUMGA2@xxxxxxxxxxxxxxxxx> set pages 0 feedb off trimspool on
P_BAUMGA2@xxxxxxxxxxxxxxxxx> select
2 'alter index '||owner||'.'||index_name||' rebuild;'
3 from dba_indexes where index_owner='XXX'
4 <--Just hit carriage return here
P_BAUMGA2@xxxxxxxxxxxxxxxxx> spool foo.sql
P_BAUMGA2@xxxxxxxxxxxxxxxxx> /
alter index XXX.A_PK rebuild;
alter index XXX.B_PK rebuild;
alter index XXX.C_PK rebuild;
alter index XXX.D_PK rebuild;
alter index XXX.E_PK rebuild;
Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Johan Muller
Sent: Monday, August 14, 2006 2:51 PM
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
==============================================================================
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
Other related posts:
