Sponsored Content
Top Forums Shell Programming and Scripting How to get sqlplus column header once in csv file? Post 302794833 by a1_win on Tuesday 16th of April 2013 10:35:54 PM
Old 04-16-2013
How to get sqlplus column header once in csv file?

Hi All,

Could anyoone please let me know how do I get sqlplus column header once in csv file

Scripts are below:

Code:
cat concreq.sh

#!/bin/bash

. $HOME/.profile

while [ 1 -eq 1 ]; do
sqlplus apps/pwd <<-EOF
set lines 100 pages 100
col "USER_CONCURRENT_QUEUE_NAME" format a40;
--set termout off
--set arraysize 5
set echo off
set verify off
set heading on
set feed off
set colsep,;
@/test1/concreq.sql
EOF
sleep 15
done
$




Code:
$cat concreq1.sql

column tm new_value file_time noprint
select to_char(sysdate, 'MMDDYYYY') tm from dual ;
prompt &file_time
spool /test1/TEST1_CM_&file_time..csv append
col running head "Running" format 9999999
select to_char(r.ACTUAL_START_DATE,'MM-DD-YYYY HH24:MI:SS'),sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,sum(decode(r.phase_code,'P',1,0)) pending
from applsys.fnd_concurrent_requests r,applsys.fnd_concurrent_processes p,applsys.fnd_concurrent_queues q
Where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.max_processes >= 0 and (r.phase_code in ('R','P','I'))
group by r.ACTUAL_START_DATE
UNION ALL
SELECT (to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')),0,0 from dual
WHERE  NOT EXISTS
(select to_char(r.ACTUAL_START_DATE,'MM-DD-YYYY HH24:MI:SS'),sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,sum(decode(r.phase_code,'P',1,0)) pending
from applsys.fnd_concurrent_requests r,applsys.fnd_concurrent_processes p,applsys.fnd_concurrent_queues q
Where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.max_processes >= 0 and (r.phase_code in ('R','P','I'))
group by r.ACTUAL_START_DATE);
spool off;
-bash-3.2$



Code:
Current output after executing the concreq.sh script:


-bash-3.2$ cat TEST1_CM_04102013.csv

TO_CHAR(R.ACTUAL_ST, Running,   PENDING
-------------------,--------,----------
04-16-2013 19:03:32,       1,         0

TO_CHAR(R.ACTUAL_ST, Running,   PENDING
-------------------,--------,----------
04-16-2013 19:03:32,       1,         0

TO_CHAR(R.ACTUAL_ST, Running,   PENDING
-------------------,--------,----------
04-16-2013 19:03:32,       1,         0

$



Expected output should be:


Code:
-bash-3.2$ cat TEST1_CM_04102013.csv

TO_CHAR(R.ACTUAL_ST, Running,   PENDING
-------------------,--------,----------
04-16-2013 19:03:32,       1,         0
04-16-2013 19:03:32,       1,         0
04-16-2013 19:03:32,       1,         0
$

Thanks for your time!

Regards,
a1_win
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How can I add a header to a csv file

I have a csv file which has no header. the file has 15 fields and needs to go out with a header of 8 fields. The header content needs to have some variables and some fixed that i have set up: variable header fields OUTFILE_YEAR=`date '+%y'` DATE=`date '+%d%m%y'` TIME=`date '+%H:%M:%S'`... (6 Replies)
Discussion started by: Pablo_beezo
6 Replies

2. Shell Programming and Scripting

Append Header in CSV file

Hi, I create a csv file and the output looks like below Arun,E001 Sathish,E003 Now i need to include the below header and the output should like below Name,Number Arun,E001 Sathish,E003 Please guide me. Thanks (4 Replies)
Discussion started by: Sekar1
4 Replies

3. Shell Programming and Scripting

Add header to a .csv file

Hi, I am trying to add a header record to all the .csv files in a directory. I am using the below sed commnad sed -i '1 i \abc,sam,xyz,tip,pep,rip' xyz.csv but this is not adding the header and I am not getting any error,pls tell me if any thing is wrong in the code. Thanks, Shruthi (2 Replies)
Discussion started by: shruthidwh
2 Replies

4. Shell Programming and Scripting

Rename a header column by adding another column entry to the header column name URGENT!!

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (4 Replies)
Discussion started by: Vavad
4 Replies

5. UNIX for Dummies Questions & Answers

Rename a header column by adding another column entry to the header column name

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (1 Reply)
Discussion started by: Vavad
1 Replies

6. UNIX for Dummies Questions & Answers

Merge all csv files in one folder considering only 1 header row and ignoring header of all others

Friends, I need help with the following in UNIX. Merge all csv files in one folder considering only 1 header row and ignoring header of all other files. FYI - All files are in same format and contains same headers. Thank you (4 Replies)
Discussion started by: Shiny_Roy
4 Replies

7. UNIX for Dummies Questions & Answers

Creating a csv file with header in UNIX

I have a flat file that contains dynamic list of variables like a=1 b=2 c=3 . .. z=26 I need to convert the above into a csv file having the format below: a,b,c,..,z 1,2,3,..,26 Please note, I do not want a comma separating the last variable. I tried to refer the post... (4 Replies)
Discussion started by: vkumbhakarna
4 Replies

8. Shell Programming and Scripting

Matching the header of a .CSV file with dynamic field names

I have a .CSV file (frequency - weekly) whose header contains the year-week value in two of the columns which keeps changing every week. For an instance please see below. Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,201420... (4 Replies)
Discussion started by: dhruuv369
4 Replies

9. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

10. Shell Programming and Scripting

Script to validate header in a csv file

Hi All; I am struggling to write a script that validates file header. Header file would be like below with TAB separated TRX # TYPE REF # Source Piece Code Destination Piece Code every time I need to check the txt file if the header was same as above fields if validation success... (6 Replies)
Discussion started by: heye18
6 Replies
TM::Serializable::CSV(3pm)				User Contributed Perl Documentation				TM::Serializable::CSV(3pm)

NAME
TM::Serializable::CSV - Topic Maps, trait for parsing (and later dumping) CSV stream SYNOPSIS
# 1) bare bones my $tm = .....; # get a map from somewhere (can be empty) Class::Trait->apply ($tm, "TM::Serializable::CSV"); use Perl6::Slurp; $tm->deserialize (slurp 'myugly.csv'); # 2) exploiting the timed sync in/out mechanism my $tm = new TM::.... (url => 'file:myugly.csv'); # get a RESOURCEABLE map from somewhere $tm->sync_in; DESCRIPTION
This trait provides parsing and dumping from CSV formatted text streams. INTERFACE
Methods deserialize $tm->deserialize ($text) This method consumes the text string passed in and interprets it as CSV formatted information. What topic map information is generated, depends on the header line (the first line): o If the header line contains a field called "association-type", then all rows will be interpreted as assertions. In that the remaining header fields (in that order) are interpreted as roles (role types). For all rows in the CSV stream, the position where the "association-type" field was is ignored. The other fields (in that order) are affiliated with the corresponding roles. Example: association-type,location,bio-unit is-born,gold-coast,rumsti is-born,vienna,ramsti Scoping cannot be controlled. Also all players and roles (obviously) are directly interpreted as identifiers. Subject identifiers and locators are not (yet) implemented. o If the header line contains a field called "id", then all further rows will be interpreted as topic characteristics, with each topic on one line. The column position where the "id" field in the header is will be interpreted as toplet identifier. All further columns will be interpreted according to the following: o If the header column is named "name", the values will be used as topic names. o Otherwise if the value looks like a URI, an occurrence with that URI value will be be added to the topic. o Otherwise an occurrence with a string value will be added to the topic. Example: name,id,location,homepage "Rumsti",rumsti,gold-coast,http://rumsti.com "Ramsti",ramsti,vienna,http://ramsti.com serialize $tm->serialize [Since TM 1.53] This method serializes a fragment of a topic map into CSV. Which fragment can be controlled with the header line and options (see constructor). "header_line" (only for serialization) This string contains a comma separated list (CSV parseable) of headings. If one of the headings is "association-type", then the generated CSV content will contain associations only. Nothing else is implemented yet. The other headings control which roles (and in which order) should be included in the CSV content. If a particular role type has more than one player, then all players are included. NOTE: As this is inconsistent, this will have to change. "type" (only for serialization) If existing, then this controls which association type is to be taken. "baseuri" (only for serialization) If existing and non-zero, the base URI of the map will remain in the identifiers. Otherwise it will be removed. "specification" If existing (and when selecting only associations), this specification will be interpreted in the sense of "asserts" (see TM). Example: $tm->serialize (header_line => 'association-type,location,bio-unit', type => 'is-born', baseuri => 0); SEE ALSO
TM, TM::Serializable AUTHOR INFORMATION
Copyright 2010 Robert Barta. This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. http://www.perl.com/perl/misc/Artistic.html perl v5.10.1 2012-06-05 TM::Serializable::CSV(3pm)
All times are GMT -4. The time now is 04:39 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy