Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Problems with SQLPLUS in shellscript

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Problems with SQLPLUS in shellscript
# 1  
Old 09-23-2008
Problems with SQLPLUS in shellscript
Hi All,

I need to run one sql query from my shellscript.

query is something like this

SELECT DISTINCT 1 FROM ******* WHERE ***** ('CRE','CRA')
AND TO_CHAR (***** , 'DD:MM:YYYY') = TO_CHAR ( SYSDATE, 'DD:MM:YYYY')
AND ****** IN ( 0) AND ******** =0;

I can't call sql file inside shellscript (i need to write the sql query inside shell script itself) . and i need to store the output of sql to a unix variable.

How is this possible in unix ?

THanks in advance
# 2  
Old 09-23-2008
You can use SQLPLUS on the command line or in shell .

sqlplus -S <user_id>/<password>@<instance_name> @<sql_file>

"-S" will run the SQL in silent mode so that it returns just the query output and nothing else.

Note : sql file should contain "exit ;" statement to close the connection .

Hope this helps .
# 3  
Old 09-23-2008
Thanks for the reply.

HOw can i store the output to a shell variable ?
# 4  
Old 09-23-2008
Search for sqlplus on this forum, there are a lot of threads regarding this topic here.

Regards
# 5  
Old 09-23-2008
VAR=`sqlplus -S <user_id>/<password>@<instance_name> @<sql_file>

your sql code here


`

VAR will have the output.

or

you can use the Spool option in sqlplus, which will store the output into a flat file.
# 6  
Old 09-24-2008
Here is script

Quote:
echo "in script"

X=`sqlplus -s user/password@servername <<eof
set serveroutput on;
set heading off;
set feedback off;
set linesize 1000;
declare
out_value number; --use variable type as per ur requirement

BEGIN
SELECT DISTINCT 1 into out_value FROM ******* WHERE ***** ('CRE','CRA')
AND TO_CHAR (***** , 'DD:MM:YYYY') = TO_CHAR ( SYSDATE, 'DD:MM:YYYY')
AND ****** IN ( 0) AND ******** =0;

dbms_output.put_line(out_value);
EXCEPTION WHEN NO_DATA_FOUND THEN
out_value:=null;
END;
/
EXIT;
eof`
echo "after plsql block"
echo "value after query is $X"
Last edited by Dhruva; 09-25-2008 at 01:32 AM..
# 7  
Old 09-24-2008
Quote:
Originally Posted by Dhruva
Here is script
Worked well thanks...However we had to remove the exception block to get it done...Don't know the exact reason behind it..

Neways great work..
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Avoid $ symbol while calling sqlplus in shellscript.

Hi All, we have requirement, i am created a shell script , inside i am connecting sqlplus and execute the query. below my code for your reference. get_sqlid () { sqlid=$( sqlplus -s $PBDW_USERID/$PBDW_PW@$PBDW_SID <<EOF DEFINE TBLNAME=$1 set feedback off set serverout on size... (4 Replies)
Discussion started by: KK230689
4 Replies

2. Shell Programming and Scripting

Telnet shellscript

cat << EOF | telnet alt1.aspmx.l.google.com 25 HELO verify-email.org MAIL FROM: <check@verify-email.org> RCPT TO: <test@gmail.com> quit EOF Hello, I'm trying to get the result of that execution, and can not see the result or bring it to a txt ... the direct command in ssh running the result... (5 Replies)
Discussion started by: c0i0t3
5 Replies

3. Shell Programming and Scripting

Sqlplus error - sqlplus -s <login/password@dbname> : No such file or directory

i am using bash shell Whenever i declare an array, and then using sqlplus, i am getting sqlplus error and return code 127. IFS="," declare -a Arr=($Variable1); SQLPLUS=sqlplus -s "${DBUSER}"/"${DBPASS}"@"${DBASE} echo "set head off ; " > ${SQLCMD} echo "set PAGESIZE 0 ;" >> ${SQLCMD}... (6 Replies)
Discussion started by: arghadeep adity
6 Replies

4. Shell Programming and Scripting

Help with shellscript

I am new in shell script i want to convert .txt file in the format axsjdijdjjdk to a x s j d i j d j j d k (5 Replies)
Discussion started by: sreejithalokkan
5 Replies

5. Shell Programming and Scripting

Needed shellscript for the following

hi all, i need the shell script for he below requirement i had the input file as a_20121217_035120( frmat is a_date_hhmmss) a_20121217_035128 a_20121217_035456 a_20121217_035767 a_20121217_035178 a_20121217_035189 a_20121217_035220 my output should be a_20121217_035456... (0 Replies)
Discussion started by: hemanthsaikumar
0 Replies

6. Shell Programming and Scripting

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... (3 Replies)
Discussion started by: rockf1bull
3 Replies

7. Shell Programming and Scripting

Need help with shellscript

Hello. I am a novince at writing shell scripts but here is the question. I have to write a shell script that does the following: Once executed via crontab, the script should do the following: a. get date/time stamp in for format 10-MAR-05 and b. execute shell script my_script.sh (which... (2 Replies)
Discussion started by: jigarlakhani
2 Replies

8. UNIX for Advanced & Expert Users

ftp in Shellscript

Can I do something like this from a shellscript ?: ftp 12.34.56.78 <<! username password put a.c bye ! It does not go through as the login fails. Is there any alternative to do the above requirement? Thanks in advance. Gowrish (3 Replies)
Discussion started by: ggowrish
3 Replies

9. Shell Programming and Scripting

Create Shellscript

I am new to UNIX. I got the file from Oracle, with two columns (Table Name and Column Name). I need to create the shell script where the result suppose to include plain text, <table_name>, <Column_name> from the file. Plain text will be the statements to create index in Oracle. something like... (1 Reply)
Discussion started by: newuser100
1 Replies
Login or Register to Ask a Question