Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Help to merge multiple .sql files
Top Forums Shell Programming and Scripting Help to merge multiple .sql files Post 302373581 by durden_tyler on Friday 20th of November 2009 10:38:12 PM
Old 11-20-2009
Quote:
Originally Posted by snvniranjanrao
...
Code:
************************************************************************
Shell Script: 
#!/bin/ksh
ODATE=`secure_sqlplus @$./get_daily_odate.sql | tail -2 | head -1 | tr -s ' ' ''`
CHKENDTM=`secure_sqlplus @$./check_batch_endtime.sql \"A\" \"$ODATE\"  | tail -2 | head -1 | tr -s ' ' '' | tail -2c`
************************************************************************
Contents of get_daily_odate.sql: 
select to_char((sysdate-1),'mm/dd/yyyy') from dual
/
************************************************************************
Contents of check_batch_endtime.sql: 
-- This SQL is used to get the end time of the batch for a odate
-- &1 -> batch zone, &2 -> odate
select count(*) from adf_batch_hist
where trunc(odate) = to_date('&2','mm/dd/yyyy') and batch_zone = '&1'
and (start_time is not null or to_char(start_time) <> '')
and (end_time is not null or to_char(end_time) <> '')
/
************************************************************************

What I am trying to do is instead of two separate .sql files, I will have both of these queries in a single .sql file.
Accordingly the shell script would call this single .sql file in both the statements. However, the first line of the shell script should execute only first query and the second line of the shell script should execute only second query.
Does that mean you want to connect to the database via sqlplus twice - one for the date and then for the count ?
Why would you want to do that ?

If you are connecting to the same schema (which appears to be the case), then connect just once, spool the results of both queries into a file and use the gazillion utilities of your shell to process that file.

Code:
$
$ # SQL script with all your queries.
$ cat myqueries.sql
set feed off pages 0 verify off trimspool on
select to_char((sysdate-1),'mm/dd/yyyy') from dual;
select count(*) from emp
where deptno = &1;

$
$ # Korn shell script that connects just once, runs all queries, and spools to a file
$ cat getdatecount.ksh
#!/bin/ksh
sqlplus -s /nolog <<EOF >getdatecount.log
connect test/test
@myqueries.sql 30
exit
EOF

ODATE=`head -1 getdatecount.log`
echo "ODATE = $ODATE"
CHKENDTM=`awk '{if (NR==2){print $1}}' getdatecount.log`
echo "CHKENDTM = $CHKENDTM"

$
$ ./getdatecount.ksh
ODATE = 11/19/2009
CHKENDTM = 6
$
$

tyler_durden
Last edited by durden_tyler; 11-20-2009 at 11:50 PM..
 

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Merge multiple .so files

Hi all, I am developing an application in Tcl, inwhich i have to load many modules written in C. I am converting those C modules into shared object(.so) files, and wrap it with my application using SWIG, for which i had the interface file. Now my question is, i have two different... (2 Replies)
Discussion started by: senthilvnr
2 Replies

2. Shell Programming and Scripting

Merge Multiple Files and Transpose

Looking to join three files and then transpose some columns from multiple rows into a single row. File Info: FIELD TERMINATED BY '^' ENCLOSED BY '~' LINE TERMINATED BY '\r\n' FIRST FILE (FOOD_DES.txt) ~01001~^~0100~^~Butter, salted~^~BUTTER,WITH... (2 Replies)
Discussion started by: mkastin
2 Replies

3. UNIX for Advanced & Expert Users

merge two column multiple files into one

Hi I have multiple files each with two columns and I need to combine all those file into a tab delimited file. (multiple entry with same name separated by a comma) The content of the files are as follows: --- file1.txt: name var1 aaa xx aaa gg bbb yy ddd zz --- file2.txt ... (8 Replies)
Discussion started by: mary271
8 Replies

4. UNIX for Dummies Questions & Answers

Merge multiple files

Hi All, How can I merge 3rd column of multiple files into 1 file, the column header in the merged file being the name of the file from which the 3rd column was taken. The first 2 columns of all the files are exactly same. Thanks for your help ! (3 Replies)
Discussion started by: newbie83
3 Replies

5. UNIX for Dummies Questions & Answers

Merge columns from multiple files

Hi all, I've searched the web for a long time trying to figure out how to merge columns from multiple files. I know paste will append columns like so: paste file1 file2 file3 file4 file5 ... But this becomes inconvenient when you want to append a large number of files into a single file. ... (2 Replies)
Discussion started by: torchij
2 Replies

6. Shell Programming and Scripting

Merge the multiple text files into one file

Hi All, I am trying to merge all the text files into one file using below snippet cat /home/Temp/Test/Log/*.txt >> all.txt But it seems it is not working. I have multiple files like Output_ServerName1.txt, Output_ServreName2.txt I want to merge each file into one single file and... (6 Replies)
Discussion started by: sharsour
6 Replies

7. Shell Programming and Scripting

Create Multiple UNIX Files for Multiple SQL Rows output

Dear All, I am trying to write a Unix Script which fires a sql query. The output of the sql query gives multiple rows. Each row should be saved in a separate Unix File. The number of rows of sql output can be variable. I am able save all the rows in one file but in separate files. Any... (14 Replies)
Discussion started by: Rahul_Bhasin
14 Replies

8. Shell Programming and Scripting

Merge columns from multiple files

Hello and Good day I have a lot of files with same number of rows and columns.$2 and $3 are the same in all files . I need to merge $2,$3,$6 from first file and $6 from another files. File1: $1 $2 $3 $4 $5 $6... (8 Replies)
Discussion started by: ali.seifaddini
8 Replies

9. Shell Programming and Scripting

Merge multiple files with common header

Hi all, Say i have multiple files x1 x2 x3 x4, all with common header (date, time, year, age),, How can I merge them to one singe file "X" in shell scripting Thanks for your suggestions. (2 Replies)
Discussion started by: msarguru
2 Replies

10. UNIX for Beginners Questions & Answers

Merge Multiple html files into one

Hi all I have written some code to write my output in html. As i have multiple servers, need to generate single html file. but my code is generating html file for each server. I have merged the files using below code. cat /home/*_FinalData.html > /home/MergedFinalData.html But how to... (1 Reply)
Discussion started by: Snehasish
1 Replies

LEARN ABOUT DEBIAN

oarhold

oarhold(1)							   OAR commands 							oarhold(1)

NAME

       oarhold - hold a job in OAR batch scheduler.

SYNOPSIS

       oarhold [--array][job_ids][--sql "sql syntax"][-r][-h][-V]

DESCRIPTION

       Ask OAR to not schedule jobs until oarresume command will be executed.

OPTIONS

       -r, --running
	   Manage not only Waiting jobs but also Running one (can suspend the job)

       --array
	   Hold array job(s) passed as parameter (all the sub-jobs of the given array job(s))

       --sql
	   Hold jobs which repond to the SQL where clause on the table jobs (ex: "project = 'p1'")

       -V, --version
	   Print OAR version number.

       -h, --help
	   Print help command message.

	   oarsub(1), oardel(1) oarstat(1), oarnodes(1), oarresume(1)

COPYRIGHTS

	Copyright 2008 Laboratoire d'Informatique de Grenoble (http://www.liglab.fr). This software is licensed under the GNU Library General Public License. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

oarhold 							    2012-05-23								oarhold(1)
All times are GMT -4. The time now is 12:46 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy