Results 1 to 4 of 4

Thread: shell query

  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: shell query

    Hi

    ive put the following into a script file

    #!/bin/ksh

    cd /mount/backups

    export ORACLE_SID=wmstst

    sqlplus system/manager@wmstst<<EOF

    set feedback off

    set heading off

    set pagesize 2000

    set linesize 120

    set echo off

    set term off

    select '/opt/oracle/admin/wmstst/scripts/initwmstst.ora' from sys.dual;

    select name from v$controlfile;

    select member from v$logfile;

    select name from v$tempfile;

    select name from v$datafile;

    exit

    EOF


    when i execute it i get the following error messages

    Connected to:

    Oracle Database 11g Release 11.2.0.1.0 - 64bit Production



    SQL> 2 3 from v

    *

    ERROR at line 3:

    ORA-00942: table or view does not exist


    when i test each select line manually with sqlplus it runs and i get results back, put into a script it doesnt work.

    im running it on Oracle 11g

    under Redhat enterprise server 2.6

    any idea or clues much appreciated

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    you need to "e****** the dollar signs; like in v$logfile because the shell is treat "$logfile"
    as OS environmental variable (like $PATH)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    In shell, '$' starts the name of environment variable. As the value of e.g. $CONTROLFILE is not set, table name is transformed to 'V'. If this is not what you want, use '\' (backslash) as an escape character.
    Code:
    select name from v\$controlfile;
    Or, put those SQL/SQL*Plus statements into different (.sql) file.

  4. #4
    Join Date
    Mar 2011
    Posts
    2
    thanks both

    changed my scripts to the following

    #!/bin/ksh
    cd /mount/backups
    export ORACLE_SID=wmstst
    sqlplus -s system/manager@wmstst<<EOF
    set feedback off
    set heading off
    set pagesize 2000
    set linesize 120
    set echo off
    set term off
    select '/opt/oracle/admin/wmstst/scripts/initwmstst.ora' from sys.dual;
    select name from v\$controlfile;
    select member from v\$logfile;
    select name from v\$tempfile;
    select name from v\$datafile;
    exit
    EOF


    returned correctly

    [oracle@redhat3 linux_pw]$ ./pw.sh

    /opt/oracle/admin/wmstst/scripts/initwmstst.ora

    /opt/oracle/db/wmstst/oradata01/control01.ctl
    /opt/oracle/db/wmstst/oraindex01/control02.ctl
    /opt/oracle/db/wmstst/oradata01/control03.ctl

    /opt/oracle/db/wmstst/oradata01/redo01a.rdo
    /opt/oracle/db/wmstst/oraindex01/redo01b.rdo
    /opt/oracle/db/wmstst/oradata01/redo02a.rdo
    /opt/oracle/db/wmstst/oraindex01/redo02b.rdo
    /opt/oracle/db/wmstst/oradata01/redo03a.rdo
    /opt/oracle/db/wmstst/oraindex01/redo03b.rdo

    /opt/oracle/db/wmstst/oradata01/temp01.dbf

    /opt/oracle/db/wmstst/oradata01/system01.dbf
    /opt/oracle/db/wmstst/oradata01/sysaux01.dbf
    /opt/oracle/db/wmstst/oradata01/undotbs01.dbf
    /opt/oracle/db/wmstst/oradata01/locdata01.dbf
    /opt/oracle/db/wmstst/oraindex01/locindex01.dbf
    /opt/oracle/db/wmstst/oradata01/users01.dbf


    again many thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •