Visit Our UNIX and Linux User Community

Problems with storing oracle sqlplus query output shell script

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Problems with storing oracle sqlplus query output shell script
# 1  
Old 12-04-2011
Problems with storing oracle sqlplus query output shell script

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

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,

Last edited by Scott; 12-04-2011 at 03:19 PM.. Reason: Added code tags
# 2  
Old 12-04-2011
Hi Brian.

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


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  
Old 12-04-2011
Seeing a right value from echo "$VAR" doesn't necessarily mean VAR is exported. You need to explicitly "export VAR".
# 4  
Old 12-05-2011
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.


Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell Script (ksh) - SQLPlus query filter using a string variable

Using ksh, I am using SQLPlus to execute a query with a filter using a string variable. REPO_DB=DEV1 FOLDER_NM='U_nmalencia' FOLDER_CHECK=$(sqlplus -s /nolog <<EOF CONNECT user/pswd_select@${REPO_DB} set echo off heading off feedback off select subj_name from subject where... (5 Replies)
Discussion started by: nkm0brm
5 Replies

2. Shell Programming and Scripting

How to pass Variable from shell script to select query for SqlPlus?

echo "set echo off"; echo "set feedback off"; echo "set linesize 4000"; echo " set pagesize 0"; echo " set sqlprompt ''"; echo " set trimspool on"; Select statement is mentioned below echo "select res.ti_book_no from disney_ticket_history res where res.ti_status =${STATUS} and... (7 Replies)
Discussion started by: aroragaurav.84
7 Replies

3. Shell Programming and Scripting

sed not storing output in shell script

Hello, Following my learning of shell scripting.. I got stuck yet again. If I execute this command on terminal: $ sed "s/off/on/g" file > fileAUX I successfully change the text off to on from file to fileAUX. But the same command is not working inside a shell script. I tested and... (2 Replies)
Discussion started by: quinestor
2 Replies

4. Shell Programming and Scripting

How to run a SQL select query in Oracle database through shell script?

I need to run a SQL select query in Oracle database and have to capture the list of retrieved records in shell script. Also i would like to modify the query for certain condition and need to fetch it again. How can i do this? Is there a way to have a persistent connection to oracle database... (9 Replies)
Discussion started by: vel4ever
9 Replies

5. Shell Programming and Scripting

Shell Script to execute Oracle query taking input from a file to form query

Hi, I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database. For instance: USER CITY --------- ---------- A CITY_A B CITY_B C ... (2 Replies)
Discussion started by: DevendraG
2 Replies

6. Shell Programming and Scripting

Shell script to query Oracle table

Hi, unix gurnis I need help for following requirement for writing a shell scritp. log in to oracle database, query one table total records (select count(*) from table1), pass the return value to a file. Thanks in advance (2 Replies)
Discussion started by: ken002
2 Replies

7. Shell Programming and Scripting

redirecting oracle sqlplus select query into file

So, I would like to run differen select queries on multiple databases.. I made a script wich I thought to be called something like.. ./ sql_file_name out.log or to enter select statement in a command line.. (aix) and I did created some shell script wich is not working.. it... (6 Replies)
Discussion started by: bongo
6 Replies

8. Shell Programming and Scripting

Formatting Oracle sqlplus output

a job extracts orcle data into unix as flat file. a single record breaks into two record in unix flat file. This is the case only for 6 records out of 60 lack records. (its not single record in two line. but its single record into record. ie., \n come into picture) can you tell me what... (6 Replies)
Discussion started by: Gopal_Engg
6 Replies

9. Shell Programming and Scripting

Query Oracle tables and return values to shell script that calls the query

Hi, I have a requirement as below which needs to be done viz UNIX shell script (1) I have to connect to an Oracle database (2) Exexute "SELECT field_status from table 1" query on one of the tables. (3) Based on the result that I get from point (2), I have to update another table in the... (6 Replies)
Discussion started by: balaeswari
6 Replies

10. Shell Programming and Scripting

run oracle query remotly with shell script

hello how to run shell script to excute oracle queries on remote db ? i have tried as following sqlplus -s user/password@remote_server "select query;" but not working also this one sqlplus -s user/password@remote_server `select query;` not working :( i add this line to run another... (4 Replies)
Discussion started by: mogabr
4 Replies

Featured Tech Videos