Help with Executing sql in Shell Script

UNIX for Beginners Q & A
1

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.

2

Welcome to the forum.

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

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"

3

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 :slight_smile:

4

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:

#!/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.