Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re: Shell scripting

RE: Re: Shell scripting

From: Kevin Lange <kgel_at_ppoone.com>
Date: Tue, 08 Oct 2002 11:23:36 -0800
Message-ID: <F001.004E3815.20021008112336@fatcity.com>


Ahhh .... I had not seen that part of the response. Well, now they have a sample of just how complicated it can get.

-----Original Message-----
Sent: Tuesday, October 08, 2002 12:14 PM To: Multiple recipients of list ORACLE-L

Kevin,

Thanks for the 'coprocess', I couldn't remember what it was called.

This is what I was describing in my previous email.

Jared

Kevin Lange <kgel_at_ppoone.com>
Sent by: root_at_fatcity.com
 10/08/2002 09:23 AM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: Re: Shell scripting


Actually Jared, you can by setting up a sqlplus coprocess and using unix pipes. The routine below will get a parameter from the v$parameter table using sqlplus.

Kevin


get_db_parm()
{
  unset vRETURNED_PARM
  unset vRESPONSE

  typeset -u vPARM

  vPARM=$1

  sqlplus -silent /NOLOG |&

  print -p 'set feedback off'
  print -p 'set echo off'
  print -p 'set heading off'
  print -p 'set pagesize 0'
  print -p 'connect internal'

  read -p vRESPONSE
  while [ "${vRESPONSE:-Z}" != 'Connected.' -a \

          "${vRESPONSE%ORA*}Z" != 'Z' ]
  do
    read -p vRESPONSE
  done

  if [ "${vRESPONSE:-Z}" = Z ] ; then

     return 1
  fi

  vTERMINATOR='ZZZZZ'

  print -p "SELECT UPPER(name)||':'||value t FROM v\$parameter"
  print -p "WHERE UPPER(name) = '"${vPARM}"'"
  print -p "UNION"
  print -p "SELECT '${vTERMINATOR}' t FROM dual"
  print -p "ORDER BY 1;"

  read -p vRESPONSE

  while [ "${vRESPONSE}" != ${vTERMINATOR} ]   do
    if [ "${vRESPONSE%%:*}" = $vPARM ] ; then

         vRETURNED_PARM=${vRESPONSE##*:}
      if [ "${vRETURNED_PARM%%\?*}Z" = Z ] ; then
        vRETURNED_PARM=${ORACLE_HOME}${vRETURNED_PARM#\?}
      fi

    fi
    read -p vRESPONSE
  done

  print -p "exit"

  # Flush Buffer after exiting
  while [ $? -eq 0 ]
  do
    read -p $vRESPONSE
  done

  vRETURNED_PARM=`echo $vRETURNED_PARM|sed "s/%/%%/g"`

  printf "${vRETURNED_PARM}\n"

  return 0
}

-----Original Message-----
Sent: Tuesday, October 08, 2002 10:24 AM To: Multiple recipients of list ORACLE-L

Omar,

If what you mean is:

"How do I send a value from sqlplus plus directly to a variable in my shell environment?"

You can't.

What you've done is the way it is usually done.

Another way to do it is to use the '&|' korn shell mechanism.

e.g.
  sqlplus -silent scott/tiger@$ORACLE_SID |&

This allows you to send commands straight to sqlplus from the ksh command line, and retrieve the results of queries from the cmd line.

It's not as easy as it appears. I wrote a set of shell functions once upon a time to do this, and it gets more complex than you expect.

The method you are currently using works ok.

Of course, you could use Perl with DBI and avoid all this subshell nonsense.

Jared

On Tuesday 08 October 2002 01:23, Cyril Thankappan wrote:
> Sure

>

> THanks Omar
>

> But I was looking for a
> 'non-file based' solution :)
>

> Thanks anyway
>

> On Mon, 07 Oct 2002 Omar Khalid wrote :
> >hi
> >
> >well i think i did this once, you can read
> >the output of the SQL query into shell variables by
> >first redirecting the output of the SQL query to an
> >OS file and then reading the file and loading
> >the data in the file into shell variables.
> >
> >/* here is sample code to redirect the output of sql query to OS
> >file */
> >
> >#!/bin/sh
> >
> >CMD_FILE=sql_input.sql
> >LOC_FILE=output.log
> >
> > SQLCMD="connect internal ;
> > select * from sys.dba_users ;"
> > echo "$SQLCMD" > ${CMD_FILE}
> >
> > svrmgrl < ${CMD_FILE} > ${LOC_FILE} /* redirecting the
> >out of
> >svrmgrl to OS file */
> >
> >regards
> >'_-_'_-_'_-_'_-_'_-_'_-_'_-_'_-_'_-_'
> >Omar Khalid
> >Software Engineer
> >LMK Resources
> >Voice: 111-101-101*780
> >Mobile: 0333-510-4465
> >Web: www.lmkr.com
> >'_-_'_-_'_-_'_-_'_-_'_-_'_-_'_-_'_-_'
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "Cyril
> > Thankappan" To: Multiple
> >recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > <cyril_thank_at_redif cc:
> > fmail.com> Subject: Shell
> >scripting
> > Sent by:
> > root_at_fatcity.com
> >
> >
> > 10/04/2002 09:18
> > PM
> > Please respond to
> > ORACLE-L
> >
> >
> >
> >
> >
> >
> >Hi
> >
> >I wanted to select a column from a v$ table
> >
> >struggled with it
> >and finally came with a workaround as follows
> >
> >------------------------------------
> >
> >archived_log='$archived_log'
> >begin_seq=`sqlplus -s /nolog <<EOF
> >connect / as sysdba
> >set head off
> >set echo off
> >set feedback off
> >set verify off
> >select max(sequence#)-1 from v$archived_log ;
> >exit
> >EOF`
> >echo $begin_seq
> >
> >---------------------------------------
> >
> >However, the question is how to
> >'directly' take the output into a shell variable?
> >there 'shud be' a better workaround than this !
> >
> >Thanks
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Cyril Thankappan
> > INET: cyril_thank_at_rediffmail.com
> >
> >Fat City Network Services -- 858-538-5051
> >http://www.fatcity.com
> >San Diego, California -- Mailing list and web hosting
> >services
> >---------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail
> >message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
> >in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You
> >may
> >also send the HELP command for other information (like
> >subscribing).
> >
> >
> >
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Omar Khalid
> > INET: okhalid_at_lmkr.com
> >
> >Fat City Network Services -- 858-538-5051
> >http://www.fatcity.com
> >San Diego, California -- Mailing list and web hosting
> >services
> >---------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail
> >message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
> >in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You
> >may
> >also send the HELP command for other information (like
> >subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  INET: kgel_at_ppoone.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  INET: kgel_at_ppoone.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Tue Oct 08 2002 - 14:23:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US