DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Shell script with an sql order : strange behavior ?

  1. #1
    Join Date
    Oct 2001
    Posts
    83
    Hello,

    I noticed the following thing :

    I have a shell script in which a sql order is executed :
    *********************************************************
    export ORACLE_SID=$1
    $ORACLE_HOME/bin/sqlplus /nolog << EOF
    connect / as sysdba
    spool sqltest.lst
    select * from sys.v$log;
    spool off
    EOF

    The resultat is the following :
    **********************************
    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    SQL> Connected.
    SQL> SQL> select * from sys.v
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist


    Doing the same thing with a non v$* views, the result is OK.
    (for v$*, I have to put the select order in a sql script and call it from the shell).

    Do you have an explanation of this behavior ?

    Thanks a lot ...


  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    your shell script is treating the $log part of you v$log table as a shell variable.

    use

    select *
    from v\$log


    Gert

  3. #3
    Join Date
    Apr 2002
    Posts
    135

    In you script file the $ symbol has to be preceded by a escape character.


    select * from sys.v\$log;



    Regards,
    deepa

    Good Judgement comes from Experience.
    Experience comes from Bad Judgement

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Even when you fix that escape $ character error, you'll still get "ORA-00942: table or view does not exist". That's because there is no such object as SYS.V$LOG.

    V$LOG is a public synonym, so it must not be preceeded with schema name. Either use

    SELECT * FROM v\$log

    or

    SELECT * FROM sys.v_\$log
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Oct 2001
    Posts
    83
    Thanks a lot for the reply ...

Posting Permissions

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


Click Here to Expand Forum to Full Width