Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: iterating over results from sqlplus
Top Forums Shell Programming and Scripting iterating over results from sqlplus Post 302519272 by gary_w on Tuesday 3rd of May 2011 01:41:31 PM
Old 05-03-2011
Here's a way to store the results of a query into a shell variable
Here's a neat little trick:
Code:
#  the command returns COUNT=<nbr> which is then eval'd to set a variable.
eval $(print "select 'COUNT='||count(*) as count from <schema>.<table>;
"|sqlplus -s / |grep COUNT=)

print "The count is $COUNT"

Output:
Code:
efs:/home2/myid $ test.ksh
The count is 79931
efs:/home2/myid$

If your variable contains a list (assuming a newline at the end of each line), then read it a line at a time:
Code:
# Read results a line at a time
cat $sqlresults | while read line
do
   # Process each line of SQL output.
   echo $line
done

Of course there are other ways to structure this but logically this is what you'll need to do.

If you will be calling sqlplus many times in the same script, its better to open it as a coprocess in order to keep overhead down. That may be out of scope for this question though. Here's an example I found that illustrates it better than I can explain it: Accessing SQL*Plus using a Korn Shell Coprocess
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Perl - Iterating a hash through a foreach loop - unexpected results

i've reworked some code from an earlier post, and it isn't working as expected i've simplified it to try and find the problem. i spent hours trying to figure out what is wrong, eventually thinking there was a bug in perl or a problem with my computer. but, i've tried it on 3 machines with the... (5 Replies)
Discussion started by: quantumechanix
5 Replies

2. UNIX for Advanced & Expert Users

Set shell variables from SQLPLUS query results

Hi All, I needed to get the result of two sqlplus queris into shell variables. After days of looking for the ultimate solution to this problem.. i found this... sqlplus -s USER/PASS@DB <<EOF | awk '{if(NR==1) printf("%s ", $1); if(NR==2) printf("%s ", $1);}' | read VAR1 VAR2 set head off... (2 Replies)
Discussion started by: pranavagarwal
2 Replies

3. Shell Programming and Scripting

sqlplus: could it be dirrected do not printout anything, beside results?

I try to prepare a sufficient function to execute a sql-statement, getting back ONLY retrieved results! What I can't figured out how to make the sqlplus not printing the 'Connected to ...', 'Disconnected from...' and the executed statements after 'SQL> '. I am under impression that having... (5 Replies)
Discussion started by: alex_5161
5 Replies

4. Shell Programming and Scripting

Problem iterating in while loop

I am facing a problem in replacing the file contents by iterating through the list. My present code: Code: #!/bin/bash# TFILE="/tmp/vinay/testb_1.txt" while read linedo aline="$line" echo $aline code=`echo $aline|cut -d ',' -f1` country=`echo $aline|cut -d... (5 Replies)
Discussion started by: av_vinay
5 Replies

5. UNIX and Linux Applications

linux sqlplus select results writes into file twice

Hello, This is my first post and its because I could not find solution for myself I decided to ask help here. What I want to do; I want to get some data from a table 1 on server 1 and insert those datas into a table 2 on server 2. ( lets say schema names are server1 and server 2 also ).... (10 Replies)
Discussion started by: azuahaha
10 Replies

6. Shell Programming and Scripting

Iterating through two arrays in a single loop

Hey everyone. Is it possible to use two arrays in a loop? Basically what I am trying to do is iterate through the elements in an array, and, based on a condition, use the current position in the array to point to that index in the array. Here's the loop structure I'm looking for: ... (1 Reply)
Discussion started by: msarro
1 Replies

7. Programming

Iterating and calloc questions.

Whilst creating the function readjust_descr I have stumble across what may be a problem or something that might just work. I was hoping someone could look at the code below and tell me if readjust_descr will clear all null pointers from the structure descr_list. struct descr descr_list =... (6 Replies)
Discussion started by: Errigour
6 Replies

8. Shell Programming and Scripting

Can ctag and cscope support recording search results and displaying the history results ?

Hello , When using vim, can ctag and cscope support recording search results and displaying the history results ? Once I jump to one tag, I can use :tnext to jump to next tag, but how can I display the preview search result? (0 Replies)
Discussion started by: 915086731
0 Replies

9. Shell Programming and Scripting

Iterating over a list using awk, when variable

Hi, I've recently started using Awk commands as i need it to pull information out of a file and give me the mean value of a series of numbers. Here is the code i run on my Infile and it works just fine. awk '{if ($1 == "Mam189") print $0}' Infile | awk '{if ($1 != $2) print $0}' | awk... (5 Replies)
Discussion started by: cavanac2
5 Replies

10. Shell Programming and Scripting

Sqlplus error - sqlplus -s <login/password@dbname> : No such file or directory

i am using bash shell Whenever i declare an array, and then using sqlplus, i am getting sqlplus error and return code 127. IFS="," declare -a Arr=($Variable1); SQLPLUS=sqlplus -s "${DBUSER}"/"${DBPASS}"@"${DBASE} echo "set head off ; " > ${SQLCMD} echo "set PAGESIZE 0 ;" >> ${SQLCMD}... (6 Replies)
Discussion started by: arghadeep adity
6 Replies

LEARN ABOUT XFREE86

times

times(1)                                                           User Commands                                                          times(1)

NAME

       times - shell built-in function to report time usages of the current shell

SYNOPSIS

   sh
       times

   ksh
       times

DESCRIPTION

   sh
       Print the accumulated user and system times for processes run from the shell.

   ksh
       Print the accumulated user and system times for the shell and for processes run from the shell.

       On this man page, ksh(1) commands that are preceded by one or two * (asterisks) are treated specially in the following ways:

       1.  Variable assignment lists preceding the command remain in effect when the command completes.

       2.  I/O redirections are processed after variable assignments.

       3.  Errors cause a script that contains them to abort.

       4.  Words,  following a command preceded by ** that are in the format of a variable assignment, are expanded with the same rules as a vari-
           able assignment. This means that tilde substitution is performed after the = sign and word splitting and file name generation  are  not
           performed.

ATTRIBUTES

       See attributes(5) for descriptions of the following attributes:

       +-----------------------------+-----------------------------+
       |      ATTRIBUTE TYPE         |      ATTRIBUTE VALUE        |
       +-----------------------------+-----------------------------+
       |Availability                 |SUNWcsu                      |
       +-----------------------------+-----------------------------+

SEE ALSO

       ksh(1), sh(1), time(1), attributes(5)

SunOS 5.10                                                          15 Apr 1994                                                           times(1)
All times are GMT -4. The time now is 02:00 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy