Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Help with storing the output of multiple sql queries to a file
Top Forums Shell Programming and Scripting Help with storing the output of multiple sql queries to a file Post 302913802 by SriRamKrish on Wednesday 20th of August 2014 03:05:06 AM
Old 08-20-2014
Help with storing the output of multiple sql queries to a file
Hi All,

I have a file queries.txt as follows :
Code:
SELECT COLUMN1 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN1 FROM SCDEMA2.TABLE2;
SELECT COLUMN2 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN2 FROM SCDEMA2.TABLE2;
SELECT COLUMN3 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN3 FROM SCDEMA2.TABLE2;
SELECT COLUMN4 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN4 FROM SCDEMA2.TABLE2;

Now I need to run all the queries in queries.txt and store the output of them in a file result.txt in the following format :

Code:
SELECT COLUMN1 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN1 FROM SCDEMA2.TABLE2; 0
SELECT COLUMN2 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN2 FROM SCDEMA2.TABLE2; 0
SELECT COLUMN3 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN3 FROM SCDEMA2.TABLE2; 0
SELECT COLUMN4 FROM SCHEMA2.TABLE1 MINUS SELECT COLUMN4 FROM SCDEMA2.TABLE2; 0

Now, as all the queries are minus queries (expected result : No Rows Returned), if now rows are returned, I should be able to write '0' instead of the actual output (which I haven't figured out yet)

This is what I have tried so far :

Code:
while read line; do
sqlplus $user/$pass << EOF >> $LOGDIR/$0.log
...
...
SET DEFINE OFF;
...
spool $SCRIPTDIR/result.txt
$line
spool off;
EXIT;
EOF
done < /ogc23788/scripts/sql_input.txt

When I open the result.txt, it is as follows :

Code:
 
02:34:04 SQL> SELECT 1+1 FROM DUAL;
       1+1
----------
         2
Elapsed: 00:00:00.01
02:34:04 SQL> spool off;

Note : I tried with a sample dummy query.

Now my concern is, is there any way, at all, with which I can save the ouput as '0' when 'No Rows Returned' and '1' when some rows returned?
Also how to append multiple outputs to a single file(in a format I have mentioned above)

Cheers.
Last edited by rbatte1; 08-20-2014 at 06:36 AM.. Reason: Capitalised first person singular
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Run SQL queries in DB2 and output to file

Hi, I new to Unix and scripting. Following is my requirement. Can someone tell me whether its possible or not. Also please let me know how to proceed further if this is possible. List of queries are stored in a file. For example, I have to run a query like this: Select * from &XYZ where... (0 Replies)
Discussion started by: simhasuri
0 Replies

2. Shell Programming and Scripting

Sending SQL Queries output to different Excel sheets

Hi, I need your help in sedning sql queries output to different excel sheets. My requirement is like this: Query1: Select name from table1 where status = 'Complete' Query2: Select name from table1 where status = 'Failed' Query3: Select name from table1 where status = 'Ignored' ... (4 Replies)
Discussion started by: parvathi_rd
4 Replies

3. Shell Programming and Scripting

Help! Paste Multiple SQL output result to exec command

Hi, I want to write the shell script to change multple file name (the file name is get from DB) e.g. cp db1.txt file1_new.txt cp db2.txt file2_new.txt cp db3.txt file3_new.txt I have write the script like this: VAR=`sqlplus -s $LOGON @<<ENDOFTEXT set termout off ... (0 Replies)
Discussion started by: jackyntk
0 Replies

4. Shell Programming and Scripting

sql output from multiple rows and columns as variables in a script

This is for an Oracle journal import. I was using a pl/sql package and oracle API's. Oracle added invoker rights to their API's and now my package won't run. I didn't want to use their API's anyway. The only reason i was using pl/sql and the API's (just a package) was to utilize a cursor. How... (2 Replies)
Discussion started by: lmu
2 Replies

5. Emergency UNIX and Linux Support

Insert data into sql queries from a file

Hello friends, I need to insert data from a file to another. I need this to form an sql query file which will consist of 50.000 INSERT INTO sentences. my sql query file will consist of 50.000 times the below line consecutively: insert into subscriber... (6 Replies)
Discussion started by: EAGL€
6 Replies

6. Shell Programming and Scripting

How to store results of multiple sql queries in shell variables in ksh?

Hi, I have a script where I make a sqlplus connection. In the script I have multiple sql queries within that sqlplus connection. I want the result of the queries to be stored in shell variables declared earlier. I dont want to use procedures. Is there anyway else. Thanks in advance.. Cheers (6 Replies)
Discussion started by: gonchusirsa
6 Replies

7. Shell Programming and Scripting

Storing multiple file paths in a variable

I am working on a script for Mac OS X that, among many other things, gets a list of all the installed Applications. I am pulling the list from the system_profiler command and formatting it using grep and awk. The problem is that I want to be able to use each result individually later in the script.... (3 Replies)
Discussion started by: cranfordio
3 Replies

8. 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

9. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

10. Shell Programming and Scripting

PSQL multiple queries output to file

Hi, I have a shell script containing multiple PSQL queries for which I want the output to be redirected to a text file. psql -U postgres -d database -o textfile.txt << EOF Query1; Query2; Query ....; EOF When executing the script, queries outputs are directed to textfile.txt, however... (2 Replies)
Discussion started by: nms
2 Replies

LEARN ABOUT DEBIAN

mdb-sql

MDBTools(1)															       MDBTools(1)

NAME

       mdb-sql - SQL interface to MDB Tools

SYNOPSIS

       mdb-sql [-HFp] [-d delimiter] [-i file] [-o file] [database]

DESCRIPTION

       mdb-sql is a utility program distributed with MDB Tools.

       mdb-sql allows querying of an MDB database using a limited SQL subset language.

OPTIONS

       -H     Supress header row.

       -F     Supress footer row.

       -p     Turn  off  pretty printing. By default results are printed in an ascii table format which looks nice but is not conducive to manipu-
	      lating the output with unix tools. This option prints output plainly in a tab separated format.

       -d     Specify an alternative column delimiter. If no delimiter is specified, columns will be delimited by a tab character if pretty print-
	      ing (-p) is turned off. If pretty printing is enabled this option is meaningless.

       -i     Specify an input file. This option allows an input file containing the SQL to be passed to mdb-sql.  See Notes.

       -o     Specify an output file. This option allows the name of an output file to be used instead of stdout.

COMMANDS

       mdb-sql in interactive mode takes some special commands.

       connect to <database>
	      If  no database was specified on the command line this command is necessary before any querys are issued. It also allows the switch-
	      ing of databases once in the tool.

       disconnect
	      Will disconnect from the current database.

       go     Each batch is sent to the parser using the 'go' command.

       reset A batch can be cleared using the 'reset' command.

       list tables
	      The list tables command will display a list of available tables in this database, similar to the mdb-tables utility on  the  command
	      line.

       describe table <table>
	      Will display the column information for the specified table.

       quit   Will exit the tool.

SQL LANGUAGE

       The  currently  implemented  SQL  subset is quite small, supporting only single table queries, no aggregates, and limited support for WHERE
       clauses. Here is a brief synopsis of the supported language.

       select:
	      SELECT [* | <column list>] FROM <table> WHERE <where clause>

       column list:
	      <column> [, <column list>]

       where clause:
	      <column> <operator> <literal> [AND <where clause>]

       operator:
	      =, =>, =<, <>, like, <, >

       literal:
	      integers, floating point numbers, or string literal in single quotes

NOTES

       When passing a file (-i) or piping output to mdb-sql the final 'go' is optional. This allow constructs like

       echo "Select * from Table1" | mdb-sql mydb.mdb

       to work correctly.

       The -i command can be passed the string 'stdin' to test entering text as if using a pipe.

ENVIRONMENT

       MDB_JET3_CHARSET
	      Defines the charset of the input JET3 (access 97) file. Default is CP1252. See iconv(1).

       MDBICONV
	      Defines the output charset. Default is UTF-8. mdbtools must have been compiled with iconv.

       MDBOPTS
	      semi-column separated list of options:

	      o  use_index

	      o  no_memo

	      o  debug_like

	      o  debug_write

	      o  debug_usage

	      o  debug_ole

	      o  debug_row

	      o  debug_props

	      o  debug_all is a shortcut for all debug_* options

HISTORY

       mdb-sql first appeared in MDB Tools 0.3.

SEE ALSO

       gmdb2(1) mdb-export(1) mdb-hexdump(1) mdb-prop(1) mdb-ver(1) mdb-array(1) mdb-header(1) mdb-parsecsv(1) mdb-schema(1) mdb-tables(1)

AUTHORS

       The mdb-sql utility was written by Brian Bruns.

BUGS

       The supported SQL syntax is a very limited subset and deficient in several ways.

0.7								   13 July 2013 						       MDBTools(1)
All times are GMT -4. The time now is 10:07 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy