Help with storing the output of multiple sql queries to a file
Hi All,
I have a file queries.txt as follows :
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 :
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 :
When I open the result.txt, it is as follows :
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
I'm not sure how the SELECT 1+1 FROM DUAL; example illustrates the issue. What I might suggest is that you use a -S flag on your sqlplus command to get rid of much of the output that would be written to $0.log and then within the SQL code, put in some definitions such as:-
....which will minimise the output. You may be better running the queries in a shell loop calling sqlplus several times and writing the output to a file, then manipulating the result, something like this:-
Does that help, or have I missed the point completely?
If you are getting confusing output, can you run it with just one input line and don't remove /tmp/results.txt? If you can then share what is in that temporary file, then we can work on cleaning it up.
Robin
Lancashire, UK
Last edited by Don Cragun; 08-20-2014 at 07:11 AM..
Reason: Add missing ".
Your advice on definitions helped a lot! I used it in my code and the following code got me what exactly I wanted :
Thanks for taking the time out for wonderfully explaining the solution.
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)