RE : sqlplus from Unix...checking for errors etc.

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Mon, 09 Oct 2000 14:21:21 +0200
Message-Id: <10644.118778@fatcity.com>

John,

  I think that 1 and 2 are the same question, since one of the safest way to check for Oracle availability it to look for connection errors.   When you whant to return a value to a shell variable, you should write everything so as to return a single value, eg

  #!/bin/sh
  dbname=`echo 'set pause off echo off pagesize 0 feedback off recsep off

              whenever sqlerror exit sql.sqlcode
              select name from v$database;
              exit 0' | sqlplus -s system/manager`

  if [ $? -eq 0 ]
  then
   echo $dbname
  fi

 The 'whenever sqlerror' ensures that you can check $? against 0 - however, as shell errors normally are between 1 and 255 identifying the exact Oracle error may be difficult. Note that by setting what follows echo between double quotes instead of single ones, you can pass shell variables to SQL*Plus - don't forget to escape characters which have a meaning for the shell (*, $ ...).
If you want to get SEVERAL values, what I suggest you is to concatenate them with whichever character you want, and then apply 'cut' to the result inside your shell script. In your example, I would add the suitable 'select from dual' to concatenate your two variables.

-- 
HTH,

  Stephane Faroult
  email: sfaroult_at_oriolecorp.com 
  Oriole Corporation
  Voice:  +44  (0) 7050-696-269 
  Fax:    +44  (0) 7050-696-449 
  Performance Tools & Free Scripts
------------------------------------------------------------------
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
------------------------------------------------------------------


> 

> 

> I want to run sqlplus from a Korn Shell script.

> 

> I see 3 problems. :

> 

> 1. Can I check Oracle is available before running sqlplus?

> 

> 2. How should I check for sqlplus errors?

> 

> 3. How can I return variables from sqlplus to the shell script?

> 

> The sql is simple(see below). I want to return var_status and

> var_error_message to the Korn Shell script

> 

> 

> var_status        VARCHAR2(25);

> var_error_message VARCHAR2(100);

> 

> 

> BEGIN 

> 

> var_status := get_customer_status('CUSTOMER', 0, 'PHASE1',

Received on Mon Oct 09 2000 - 07:21:21 CDT