SQLPlus and Unix scripting with passing variables.

[Danielle]

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

[Balki]

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