Problems with storing oracle sqlplus query output shell script

Shell Programming and Scripting
1

Hello everyone,

I have a RHEL 5 system and have been trying to get a batch of 3-4 scripts each in a separate variables and they are not working as expected.

I tried using following syntax which I saw a lot of people on this site use and should really work, though for some reason it doesn't work for me.

Syntax -

testvar=`sqlplus -s foo/bar@SCHM <<EOF
set pages 0
set head off
set feed off
@test.sql
exit
EOF`

Now, I have access to the sqlplus command from the oracle bin folder and have also set the ORACLE_HOME and ORACLE_PATH variables exported (I echoed them just to ensure they are actually working).

However, I keep getting the error saying - "you need to set EXPORT for ORACLE_HOME" even though I have confirmed from everyone that I am indeed using the correct path.

Another question I have is once I get the script output (which is numeric number in bits or bytes) value in a variable (There will be 4-5 variables as there are 4-5 scripts), how do I convert into human readable output in either MBs or GBs.

Please guide me on this and I assure you that I will post everything here so that someone in future if gets stuck at the same issue, doesn't have waste time. (and your precious time won't go bad either...)

Thanks in advance,
Brian

2

Hi Brian.

The syntax is fine. Can you post the actual Oracle error (the one you quoted isn't familiar to me)?

Thanks.

Also, does sqlplus -s foo/bar@SCHM on its own take you to a SQL> prompt?

Other questions, about how you set up the Oracle environment, listeners, etc. depend on the actual ORA- error.

3

Seeing a right value from echo "$VAR" doesn't necessarily mean VAR is exported. You need to explicitly "export VAR".

4

Thanks Scott and binlib for the quick answers. I am in the mean time, making few modifications and will update this place once I am done, hopefully today.

What had happened was the user I am using for running script didn't have access to the sqlplus command and thus, I need to run it as a su user which will work.

Thanks again for quickly offering help.

Regards,
Brian.