Solved

How to modify the header in spool with Linux/Oracle?

Posted on 2013-12-13
5
494 Views
Last Modified: 2013-12-16
Hello, making a spool from the attached script,
should change the headers of the output, which now shows:

2013/09/24 17:30:00! 10027! 0! PSM_4SA4______I! 223.040253! # ..! 0! # ..! 0! # ..! 0! # ..!
2013/09/24 17:30:00! 10024! 0! PSM_4SA1______I! 167.887207! # ..! 0! # ..! 0! # ..! 0! # ..!

Open in new window


to show me the column names, separated by "!" :

UTCTime! pointnumber! pointname! valor_inst!tlq_inst!valor_prom!tlq_prom!valor_max!tlq_max!utctime_max!valor_min!tlq_min!utctime_min
2013/09/24 17:30:00! 10027! 0! PSM_4SA4______I! 223.040253! # ..! 0! # ..! 0! # ..! 0! # ..!
2013/09/24 17:30:00! 10024! 0! PSM_4SA1______I! 167.887207! # ..! 0! # ..! 0! # ..! 0! # ..!

Open in new window


What option would I use?
thanks
Test.sh
0
Comment
Question by:carlino70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
sventhan earned 500 total points
ID: 39716674
Try this

Modify your script like this ... Add a another select for selecting all your headers as shown below ....

--host echo Copying out ANALOG_01
spool $OUTPUT/ANALOG_01_$FECHA.txt
select UTCTime! pointnumber! pointname! valor_inst!tlq_inst!valor_prom!tlq_prom!valor_max!tlq_max!utctime from dual
union all
SELECT
TO_CHAR(H.UTCTIME,'YYYY/MM/DD HH24:MI:SS')||'!'||TO_CHAR(H.POINTNUMBER)||'!'||S.POINTNAME||'!'||TO_CHAR(H.VALUE_INST)||'!'||ge_pkt_conv_funcs.f_convert_tlq(H.TLQ_INST)||'!'||TO_CHAR(H.VALUE_PROM)||'!'||ge_pkt_conv_funcs.f_convert_tlq(H.TLQ_PROM)||'!'||TO_CHAR(xa_time_cnv.utc_to_loc(H.UTCTIME_MAX),'YYYY/MM/DD HH24:MI:SS')||'!'||TO_CHAR(H.VALUE_MAX)||'!'||ge_pkt_conv_funcs.f_convert_tlq(H.TLQ_MAX)||'!'||TO_CHAR(xa_time_cnv.utc_to_loc(H.UTCTIME_MIN),'YYYY/MM/DD HH24:MI:SS')||'!'||TO_CHAR(H.VALUE_MIN)||'!'||ge_pkt_conv_funcs.f_convert_tlq(H.TLQ_MIN)||'!'
FROM ANALOG_01 H, xaJsdb.AnalogPoint S
WHERE H.POINTNUMBER = S.POINTNUMBER AND UTCTIME >= trunc(sysdate)-$T_ATRAS-1 AND UTCTIME < trunc(sysdate)-$T_ATRAS
ORDER BY UT
0
 
LVL 18

Expert Comment

by:sventhan
ID: 39716679
You can add quotes if you get any errors  like below for the header SQLs

select "UTCTime!", "pointnumber!" etc  from dual
0
 

Author Comment

by:carlino70
ID: 39716819
Thanks sventhan, look at this:

select 
'UTCTIME!POINTNUMBER!POINTNAME!VALOR_INST!TLQ_INST!VALOR_PROM!TLQ_PROM!UTCTIME_MAX!VALOR_MAX!TLQ_MAX!UTCTIME_MIN!VALOR_MIN!TLQ_MIN!'
from dual
union all
SELECT
TO_CHAR(H.UTCTIME,'YYYY/MM/DD HH24:MI:SS')||'!'||TO_CHAR(H.POINTNUMBER)||'!'||S.POINTNAME||'!'||TO_CHAR(H.VALOR_INST)||'!'||ge_pkt_conv_funcs.f_convert_tlq(H.TLQ_INST)||'!'||TO_CHAR(H.VALOR_PROM)||'!'||ge_pkt_conv_funcs.f_convert_tlq(H.TLQ_PROM)||'!'||TO_CHAR(xa_time_cnv.utc_to_loc(H.UTCTIME_MAX),'YYYY/MM/DD HH24:MI:SS')||'!'||TO_CHAR(H.VALOR_MAX)||'!'||ge_pkt_conv_funcs.f_convert_tlq(H.TLQ_MAX)||'!'||TO_CHAR(xa_time_cnv.utc_to_loc(H.UTCTIME_MIN),'YYYY/MM/DD HH24:MI:SS')||'!'||TO_CHAR(H.VALOR_MIN)||'!'||ge_pkt_conv_funcs.f_convert_tlq(H.TLQ_MIN)||'!'
FROM ANALOG_01 H, xaJsdb.AnalogPoint S
WHERE H.POINTNUMBER = S.POINTNUMBER AND H.UTCTIME >= trunc(sysdate)-1 AND H.UTCTIME < trunc(sysdate)
ORDER BY 1;

Open in new window

It works with "ORDER BY 1", instead "ORDER BY UTCTIME". Also all the headers must be contiguous inside single quote.
Regards.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39721348
Even though this has already been closed out I wanted to offer an alternate solution.

the PROMPT command outside of the SELECT.

In the original script you posted, after the SPOOL and before the SELECT:
prompt UTCTime! pointnumber! pointname! valor_inst!tlq_inst!valor_prom!tlq_prom!valor_max!tlq_max!utctime_max!valor_min!tlq_min!utctime_min

Open in new window

0
 

Author Comment

by:carlino70
ID: 39721377
Thanks slightwv, It is a good alternative idea.

Regards
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question