Re: ksh select value into variable from v$ view

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Sun, 24 Jul 2005 19:03:36 +0200
Message-ID: <00a001c59071$a4b42220$2200a8c0@IBME1D11967173>

It seems that you cannot comunicate with the Oracle instance via ipc on uwin(I don't know what it is:)),
try it via OracleNet:

sqlplus system/manager_at_PRO9IGP

HTH
Dimitre Radoulov

Original Message ----- From: "Ram K" <lambu999_at_gmail.com> To: "Radoulov, Dimitre" <cichomitiko_at_gmail.com> Cc: "oracle-l" <oracle-l_at_freelists.org> Sent: Sunday, July 24, 2005 6:52 PM Subject: Re: ksh select value into variable from v$ view

Thank you.

I have installed both uwin and cygwin at my home machine on WIndows.

I am able to log into Sql plus from cygwin, while trying to log into sqlplus from uwin generates the following error:

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Jul 24 12:44:32 2005

Enter user-name: system
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

I tried modifying the path ( adding most of the path in cygwin), set
$ORACLE_SID in ksh and so on, but still same problem.

The thing is ksh is able to see Oracle running:

$ ps -ef | grep ora

daemon 54912 7040 03:14:38 ? 0 d:\oracle\ora92\bin\ORACLE.EXE PRO9IGP

root 9984 25856 12:43:09 ? 0 "D:\oracle\ora92\bin\sqlplusw.exe" <---- Windows sqlplus

root 52736 9346 12:53:38 tty10 0 grep ora

On 7/24/05, Radoulov, Dimitre <cichomitiko_at_gmail.com> wrote:

> Yes.
> here is an example for both of them:
>
> $ func () {
> sqlplus -s  '/ as sysdba' << !
> set echo off feed off head off time off timing off
> $stmt
> exit
>  !
> }
> export stmt="select sysdate from dual;"
> $ func | tail -1| while read var; do export VAR=$var;echo $VAR; done
> 24-JUL-05
>
>
> Regards
> Dimitre Radoulov
>
>
>
> ----- Original Message -----
> From: "Ram K" <lambu999_at_gmail.com>
> Cc: "oracle-l" <oracle-l_at_freelists.org>
> Sent: Sunday, July 24, 2005 11:29 AM
> Subject: Re: ksh select value into variable from v$ view
>
>
> > Hi all,
> >
> >   Trying to learn.
> >
> >   1) Is it possible to pass a variable (or more than one) to the SQL
> > from shell script.
> >
> >   2)  Is it possible to return more than one value from the SQL to the
> > script?
> >
> > Thanks,
> > Ram.
> >
> >        ---------
> >
> > On 7/22/05, Barbara Baker <barb.baker_at_gmail.com> wrote:
> >> Solaris 9  Oracle 9.2.0.4
> >> Hi!  I'm trying to grab a process id from the database and store it in
> >> a variable.  This syntax works if I'm not using a v$ view.  If I use a
> >> v$ view, it ignores what's after the $.  I've tried v\$ and v_\$ , but
> >> neither work.
> >>
> >> Any ideas?
> >> Thanks!
> >>
> >> SCRIPT:
> >>
> >> #!/bin/ksh
> >> set -xv
> >>        VALUE=`sqlplus -s scott/tiger <<END
> >>        set pagesize 0 feedback off verify off heading off echo off
> >> trimspool on
> >>        col timecol new_value timestamp noprint
> >>        select to_char(sysdate,'.MMDDYY') timecol from dual;
> >>        spool persist&timestamp
> >>        select process from v\$process p, v\$session s
> >>        where p.addr=s.paddr
> >>        and s.username='PERSIST';
> >>        spool off;
> >>        exit;
> >>        END`
> >>        if [ -z "$VALUE" ]; then
> >>          echo "No rows returned from database"
> >>          exit 0
> >>        else
> >>          echo $VALUE
> >>        fi
> >>
> >>
> >> WHEN I RUN IT:
> >>
> >>        ENDVALUE=
> >>        select process from v p, v s
> >>                                 *
> >> ERROR at line 1:
> >> ORA-00942: table or view does not exist
> >>        if [ -z "$VALUE" ]; then
> >>
> >>
> >> THIS WORKS:
> >>
> >> select max(adno) from ad;
> >> spool off;
> >>        exit;
> >>        END`
> >> + + sqlplus -s scott/tiger
> >> + 0< /tmp/sh3845.2
> >> VALUE=
> >>   6687530
> >> --
> >> http://www.freelists.org/webpage/oracle-l
> >>
> >
> > --
> > Thanks,
> > Ram.
> > --
> > http://www.freelists.org/webpage/oracle-l
>
>

-- 
Thanks,
Ram. 

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jul 24 2005 - 12:05:34 CDT