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

Thread: SQLPlus and Unix scripting with passing variables.

  1. #1
    Join Date
    Jun 2007
    Posts
    1

    SQLPlus and Unix scripting with passing variables.

    I have a Unix (Solaris) script that I am trying to embed a SQLPlus script in it as I am trying to receive values from my Oracle database and write them into script variables to be used elsewhere in my script. However, I am getting an error when it calls the "HERE" file which contains the SQLPlus script. I'm a newbie when it comes to Unix scripting and SQLPlus, but how can I call SQLPlus from within another script... I get the feeling that I can't use the "HERE" file method as an embedded script within a larger script.

    So is there a way to query Oracle from within another script and save the results to variables? Or am I missing something?

    Any advice would be greatly appreciated and I could post an example if that would help to clarify.

    Thanks again,

    Danielle

  2. #2
    Join Date
    Jun 2007
    Location
    İstanbul/Turkey
    Posts
    25
    To take some value from the database and put it into your variable in your unix script you can do the following;
    For example in your unix script write;
    /oracle/bin/sqlplus "/as sysdba" @yoursqlscript.sql

    -In your sql script
    use ;
    spool /textfile.txt in the beginning and
    use;
    spool off after the line which you get the value of whatever you wanted from the database;
    For ex :
    spool /textfile.txt
    select value from numbers where number=1;
    spool off;
    Now ,you have 1 in your textfile.Then use "cat" or awk with the textfile to take the needed value written in the textfile and put it into your unix script variable.
    This is a solution for this.

    then use this;
    a=`cat textfile.txt | awk '{print $0}'`
    echo $a
    so you got 1 in your unix variable a .

    If you have a big query which returns a lot of columns,you can use awk command to take specific values from any line,any word,etc.. of a textfile

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