Nested SQL queries within Shell script Post: 302419588

Sponsored Content

Top Forums Shell Programming and Scripting Nested SQL queries within Shell script Post 302419588 by shrutihardas on Friday 7th of May 2010 02:33:53 PM

05-07-2010

Registered User

Nested SQL queries within Shell script

Hi,

Would someone know if I can fire nested sql queries in a shell script? Basically what I am trying to do is as follows:

Code:

my_sql=$(sqlplus -s /nolog<<EOF|sed -e "s/Connected. *//g"
connect gidicm/gidicm@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.5.199.106)(PORT=1521))(CONNECT_DATA=(SID=PHYGD1)))'
set pages 0
select sync_cmpl
from gidicm.gid_inv_sync_hdr_t
where dc_id ='12'
and max to_char(sync_req_dttm,'YYYYMMDD')= '20100401'
and inv_sync_id =
(select max(inv_sync_id)
from gidicm.gid_inv_sync_hdr_t;)
EOF)

This code runs absolutely fine if I fire the following query:

Code:

select sync_cmpl
from gidicm.gid_inv_sync_hdr_t
where dc_id ='12'
and max to_char(sync_req_dttm,'YYYYMMDD')= '20100401'

But it fails to work with the additional select statement that I give. It is unable to recognize the additional select statement.

Is there a way to fire such nested queries? Or do I need to fire these 2 queries separately in this script, store the output of the first query in a variable and thenpass it on to another?

Thanks!

shrutihardas

View Public Profile for shrutihardas

Find all posts by shrutihardas

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

can nested SQl be run in Unix Script?

can nested SQl be run in Unix Script? I tried some and found only simply sql(one select) can work well in unix script.

2. UNIX for Dummies Questions & Answers

Sh Shell Script executing remote SQL queries

Hi there folks, I am trying to execute remote sql queries on an Oracle server. I would like to save the result of the executed sql queries on a text file, and send that text file as an attachment to an email address. Could anyone give me an idea on how the above could be achieved? Any help...

3. UNIX for Dummies Questions & Answers

shell script for sql queries

Hi All, I have written 4 sql queries . Now I want to write one SHELL SCRIPTING program for all these queries... i.e 1.select * from head; 2. select * from detail; 3. delete from head; 4. delete from detail; Please let me know how to write a shell script... Thank you

4. Shell Programming and Scripting

Script (with sql queries) not working using cron

Hi all, I have script, which performing sql queries and put output into file. When I run this script manually, its working fine, but when I want to schedule it with cron I am getting errors... I defined LD_LYBRARY_PATH and ,but no result. After I defined it, I am getting error: # more...

5. 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. Shell Programming and Scripting

Executing set of sql queries from shell script

Hi All, I tried executing set of queries from shell script but not able to capture the input query in the log file. The code looks something similar to below sqlplus user/pwd@dbname << EOF > output.log $(<inputfile.txt) EOF The above code is capturing the output of queries into...

7. Shell Programming and Scripting

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX?

Please share the doc asap as very urgently required.

8. Shell Programming and Scripting

run sql queries from UNIX shell script.

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX? :confused:

9. Shell Programming and Scripting

Using shell scripting for making queries on postgres sql

I have a situation where I have a list of airplanes that make a series of flights. Therefore I am looking up the different flights that each airplane makes based on a postgres sql query: select flightid from plane where airplane='DELTAx' As a result I get a series of flight numbers...

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

LEARN ABOUT SUSE

cpanplus::shell

CPANPLUS::Shell(3pm)					 Perl Programmers Reference Guide				      CPANPLUS::Shell(3pm)

NAME

       CPANPLUS::Shell

SYNOPSIS

	   use CPANPLUS::Shell; 	    # load the shell indicated by your
					    # config -- defaults to
					    # CPANPLUS::Shell::Default

	   use CPANPLUS::Shell qw[Classic]  # load CPANPLUS::Shell::Classic;

	   my $ui      = CPANPLUS::Shell->new();
	   my $name    = $ui->which;	    # Find out what shell you loaded

	   $ui->shell;			    # run the ui shell

DESCRIPTION

       This module is the generic loading (and base class) for all "CPANPLUS" shells. Through this module you can load any installed "CPANPLUS"
       shell.

       Just about all the functionality is provided by the shell that you have loaded, and not by this class (which merely functions as a generic
       loading class), so please consult the documentation of your shell of choice.

BUG REPORTS

       Please report bugs or other issues to <bug-cpanplus@rt.cpan.org<gt>.

AUTHOR

       This module by Jos Boumans <kane@cpan.org>.

COPYRIGHT

       The CPAN++ interface (of which this module is a part of) is copyright (c) 2001 - 2007, Jos Boumans <kane@cpan.org>. All rights reserved.

       This library is free software; you may redistribute and/or modify it under the same terms as Perl itself.

SEE ALSO

       CPANPLUS::Shell::Default, CPANPLUS::Shell::Classic, cpanp

perl v5.12.1							    2010-04-26						      CPANPLUS::Shell(3pm)