Using Shell Variables in SQLPLUS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Using Shell Variables in SQLPLUS

  1. #1
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174

    Using Shell Variables in SQLPLUS

    I am trying to find a way to use a variable in my shell (Korn) in a simple select statement in sqlplus.

    For example:

    $ SHELLVAR=10
    $ sqlplus '/ as sysdba'
    SQL> select name from v$archived_log
    2> where seqeunce# = $SHELLVAR
    3> /

    This is basically what I want to do. In the way is it written here, I get an error from SQLPLUS.

    Is there any way to use the value of shell variable in sqlplus?

  2. #2
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    I think I found my answer ....

    I should use positional parameters! As in:

    sqlplus '/ as sysdba' @myscript.sql $SHELLVAR

    And myscript.sql would like this:

    select name from v$archived_log
    where sequence# = &1

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    or

    $ export SHELLVAR=222
    $ sqlplus /@yourdb < select * from xyz where id = $SHELLVAR;
    EOF
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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