UNIX for Beginners Questions & Answers

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.

Welcome to the forum.

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

Moderator's Comments:

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"

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

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.