#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    1
    Rep Power
    0

    Using Operating System Environment Variables in SQLPLUS


    I'm looking for a way to use the Operating System Environment Variables inside of a sql script. I'd like to create a generic script that will run on all machines regardless of directory structure. I know that in SQLPLUS I can call on Operating System commands using ! followed by the Operating System command. But I'd like to use a SQL command using a Operating System variable. Example:

    spool $ORACLE_BASE/assistants/dbca/logs/CreateDB.log
    startup nomount pfile="$ORACLE_BASE/admin/orc3/pfile/init.ora";
  2. #2
  3. No Profile Picture
    ......@.........
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2004
    Posts
    1,345
    Rep Power
    56
    You really can't. Here's how to do it in Unix with ksh:
    The $BANNER_HOME and $ORACLE_HOME are environment variables defined for all users....
    UID = user
    UIPW= user/passwd
    Code:
    UID=$1
    UIPW=$1/$2
    ONE_UP=$3
    
    SQLDIR=$BANNER_HOME/pnm/dbprocs
    CTLLOC=$BANNER_HOME/data
    SORTDIR=$BANNER_HOME/sortdir
    OUTDIR=$BANNER_HOME/out
    UPUID=$(echo $UID | tr "[:lower:]" "[:upper:]")
    
    export SQLDIR CTLLOC SORTDIR OUTDIR
    
    PrntCode=$(echo "$UIPW
               set pages 0
               SELECT gjbjobs_prnt_code
               FROM   gjbjobs
               WHERE  gjbjobs_name = 'UZPGLTT'; " |
               $ORACLE_HOME/bin/sqlplus -s )
    You can do something like this in perl, but again the same script will not work on any platform without serious coding effort.

IMN logo majestic logo threadwatch logo seochat tools logo