Unix/Linux Go Back    


UNIX for Beginners Questions & Answers If you're not sure where to post a Unix or Linux question, post it here. All unix and Linux beginners welcome in this forum!

Help with Executing sql in Shell Script

UNIX for Beginners Questions & Answers


Reply    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 01-13-2018   -   Original Discussion by Naren.N
Naren.N's Unix or Linux Image
Naren.N Naren.N is offline
Registered User
 
Join Date: Jan 2018
Last Activity: 13 January 2018, 10:58 PM EST
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Help with Executing sql in Shell Script

Hello~

I have a requirement to write a shell script which will connect to the oracle database and run a select count(*) query on a table.

The script should succeed only when the count returns a number greater than zero. If the count returns zero, the script should fail.

Can someone please walk me through the steps ?

Cheers,
Thanks in advance.
Sponsored Links
    #2  
Old Unix and Linux 01-13-2018   -   Original Discussion by Naren.N
RudiC's Unix or Linux Image
RudiC RudiC is offline Forum Staff  
Moderator
 
Join Date: Jul 2012
Last Activity: 22 May 2018, 4:11 AM EDT
Location: Aachen, Germany
Posts: 12,724
Thanks: 412
Thanked 3,911 Times in 3,597 Posts
Welcome to the forum.

The phrasing of your request leads to the assumption it might be homework.

Moderator's Comments:
Help with Executing sql in Shell Script Do not post classroom or homework problems in the main forums. Homework and coursework questions can only be posted in this forum under special homework rules.

Please review the rules, which you agreed to when you registered, if you have not already done so.

More-than-likely, posting homework in the main forums has resulting in a forum infraction. If you did not post homework, please explain the company you work for and the nature of the problem you are working on.

If you did post homework in the main forums, please review the guidelines for posting homework and repost.

Thank You.

The UNIX and Linux Forums.


Should the assumption be wrong, please post some evidence, e.g. the company you work for, or the project you're involved in.

Having said that, there are some helpful links on the bottom left of this page under "More UNIX and Linux Forum Topics You Might Find Helpful"
Sponsored Links
    #3  
Old Unix and Linux 01-13-2018   -   Original Discussion by Naren.N
Naren.N's Unix or Linux Image
Naren.N Naren.N is offline
Registered User
 
Join Date: Jan 2018
Last Activity: 13 January 2018, 10:58 PM EST
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Thank you.

I work for a company that offers Life,Travel,Auto and Home Insurance. I am an ETL developer and i majorly work on Informatica and sql but occasionally use Unix. Currently we are in Agile environment and i am working on a story where i need to create a shell script(with the requirement i have mentioned) and run the script before the Informatica job runs. I have worked on writing the shell scripts to pull or push a file, create a parameter file but not en corporate a sql query in the shell script.

Hence the post Linux
    #4  
Old Unix and Linux 01-14-2018   -   Original Discussion by Naren.N
drysdalk's Unix or Linux Image
drysdalk drysdalk is offline
Registered User
 
Join Date: Feb 2017
Last Activity: 23 February 2018, 5:25 AM EST
Location: United Kingdom
Posts: 242
Thanks: 12
Thanked 83 Times in 73 Posts
Hi,

The approach typically taken here is to use the Oracle-provided sqlplus command-line client, with your SQL query embedded in-line inside your shell script.

So for example, something like this:



Code:
#!/bin/bash

output=`sqlplus -silent username/password <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF
SELECT * FROM table WHERE column='value';
EXIT;
EOF`

echo The result of the Oracle SQL query was: "$output"

All of the above assumes you have a properly-configured Oracle command-line environment, there are already environment variables and/or config files in existence for the client that define the details of your Oracle database instance, and so on. Likewise, the options you SET depend entirely on what format you want your output in and how you want sqlplus to behave.

But in terms of demonstrating the general approach, the above gives you a fairly straightforward example that you can them customise. You may very well find you need to parse the output of the SQL query further in order to use it for whatever you want to do next, but hopefully this is enough to get you started.
Sponsored Links
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Regarding executing sql query in shell script abhii UNIX for Dummies Questions & Answers 2 03-27-2013 03:25 AM
Executing a shell script from a PL / SQL Block sudhird Shell Programming and Scripting 1 06-21-2010 05:56 AM
executing a SQL query in shell script lijju.mathew Shell Programming and Scripting 1 05-17-2007 11:29 AM
Executing Sql Query Using Shell Script ragha81 Shell Programming and Scripting 14 07-31-2006 02:15 PM
Executing a SQL query from a shell script 98_1LE UNIX for Dummies Questions & Answers 3 03-23-2002 02:29 AM



All times are GMT -4. The time now is 04:16 AM.