Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    84

    Unanswered: Returning to UNIX from SQL Plus

    Hi,
    I want my sql query output to be retuned to unix as a variable to be used in the shell script. Following is not helping me. Any suggestions?
    test.sql
    ---------------------------------------------------------------------------------
    set pages 0
    select name from v$database;
    exit;
    ----------------------------------------------------------------------------------
    test.sh
    ------------------------------------------------------------------------------------
    #!/usr/bin/ksh
    DBNAME=`sqlplus -s "/ as sysdba" @test.sql`
    export DBNAME
    echo $DBNAME
    ------------------------------------------------------------------------------------

  2. #2
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    you could try spooling the output to a file, and then extract the db name from that file
    Regards
    Dbabren

  3. #3
    Join Date
    Jan 2004
    Posts
    84
    I wish to use spooling it to a file as a last option!

  4. #4
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    ok, then try on your export line pipe the output into a grep eg ..

    instead of select name from v$database do select name, 'name' from v$database.

    then export dbname = ` sqlplus .....` | grep name | awk '{print $1}'

    syntax may be slightly out but the concept should be OK. The output from you test script should go to std output, so you should be able to manipulate it.
    Regards
    Dbabren

  5. #5
    Join Date
    Jul 2003
    Location
    England
    Posts
    152
    I've just tried your original script on one of my test sys and it worked fine ..
    Regards
    Dbabren

  6. #6
    Join Date
    Jan 2004
    Posts
    84
    Yes, it worked! I had missed a space!
    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
  •