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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell Script (ksh) - SQLPlus query filter using a string variable
# 1  
Old 06-13-2014
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.

Code:
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 subj_name='${FOLDER_NM}';
exit;
EOF)
 
echo FOLDER_CHECK: ${FOLDER_CHECK}

This is giving me the following trace and error. The evaluation of ${FOLDER_NM} is not happening.

Code:
+ REPO_DB=DEV1
+ FOLDER_NM=U_nmalencia
+ + sqlplus -s /nolog
+ 0<<
CONNECT user/pswd@ETLDEV1
set echo off heading off feedback off
select subj_name
from subject
where subj_name="${FOLDER_NM}";
exit;
FOLDER_CHECK=where subj_name="${FOLDER_NM}"
                *
ERROR at line 3:
ORA-00904: "${FOLDER_NM}": invalid identifier


I also tried...

Code:
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 subj_name=\'${FOLDER_NM}\';
exit;
EOF)

and got the following error:

Code:
FOLDER_CHECK=where subj_name=\'U_nmalencia\'
                *
ERROR at line 3:
ORA-00911: invalid character


Stumped in Atlanta....

Last edited by Don Cragun; 06-13-2014 at 05:19 PM.. Reason: Add CODE tags.
# 2  
Old 06-15-2014
Try :
Code:
where subj_name='${FOLDER_NM}';

or

Code:
where subj_name='$FOLDER_NM';

# 3  
Old 06-16-2014
I initially tried using the code as you have suggested, with and without the curley braces.

Code:
where subj_name='${FOLDER_NM}';

Neither worked. I turned tracing on and I am seeing the single quotes are being evaluated as double quotes and the variable is not being replaced.
# 4  
Old 06-16-2014
try:
Code:
EOF
)

# 5  
Old 06-16-2014
do
Code:
FOLDER_CHECK=`sqlplus -s uname/pwd@sid  <<EOF
set echo off 
set heading off 
set feedback off
select subj_name
from subject
where subj_name=$FOLDER_NM;
exit
EOF`

This User Gave Thanks to Makarand Dodmis For This Post:
# 6  
Old 06-16-2014
I was able to get it to work correctly using the following code:

Code:
REPO_DB=DEV1
FOLDER_NM='U_TEST'
FOLDER_CHECK=$(sqlplus -s /nolog  <<EOF
CONNECT user/pswd@${REPO_DB}
set echo off heading off feedback off
select subj_name
from subject
where subj_name=`echo "'${FOLDER_NM}'"`;
exit;
EOF)
echo FOLDER_CHECK: ${FOLDER_CHECK}

---------- Post updated at 10:31 AM ---------- Previous update was at 09:49 AM ----------

I also tried the code that Makarand Dodmis used, with a slight change to use the CONNECT command and it also worked correctly. Thank you Makarand, I like this code better because it seems more efficient....

Code:
FOLDER_CHECK=`sqlplus -s /nolog <<EOF
CONNECT user/pswd@${REPO_DB}
set echo off
set heading off
set feedback off
select subj_name
from subject
where subj_name='$FOLDER_NM';
exit
EOF`
echo ${FOLDER_CHECK}

This User Gave Thanks to nkm0brm For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need to filter string between specific string in ksh

My argument has data as below. 10.9.9.85 -rwxr-xr-x user1 2019-10-15 17:40 /app/scripts/testingscr5.scr 127869538 -rwxr-xr-x user1 2019-10-15 17:40 /app/scripts/testingscr56scr 127869538 ....... (note all these between lines will start with hyphen '-' ) -rwxr-xr-x user1 2019-10-15 17:40... (3 Replies)
Discussion started by: mohtashims
3 Replies

2. Shell Programming and Scripting

Passing value of variable to a query within shell script

I have a script in which i connect to database to run a query and get the result of the query to a temp file. This works fine , now what i want is there is flat file which contains the value to be used in the query. I want to read this file line by line and then run the query for each value in that... (7 Replies)
Discussion started by: gpk_newbie
7 Replies

3. 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

4. Shell Programming and Scripting

How to read query string from shell script?

Hi, I am new this shell scripting. I have one html page which is executing shell script. That web form is passing some query strings to the script. Now how can I read query string in shell script and parse it in variables. I tried with below shell script but its not working. #!/bin/sh ... (6 Replies)
Discussion started by: jdp
6 Replies

5. 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

6. Shell Programming and Scripting

Using a shell script variable in a mysql query using 'LIKE'

Hello. I am writing a simple script that reads a text file and removes records from a mysql database. The items in the text file are of the format: firstname.middle.lastXXX, where XXX is a 3 digit number. The table has an email field that will match the firstname.middle.last. So, I thought I... (1 Reply)
Discussion started by: bricoleur
1 Replies

7. Shell Programming and Scripting

In a csh script, can I set a variable to the result of an SQLPLUS select query?

Can someone tell me why I'm getting error when I try to run this? #!/bin/csh -f source ~/.cshrc # set SQLPLUS = ${ORACLE_HOME}/bin/sqlplus # set count=`$SQLPLUS -s ${DB_LOGIN} << END select count(1) from put_groups where group_name='PC' and description='EOD_EVENT' and serial_number=1;... (7 Replies)
Discussion started by: gregrobinsonhd
7 Replies

8. UNIX for Advanced & Expert Users

Set shell variables from SQLPLUS query results

Hi All, I needed to get the result of two sqlplus queris into shell variables. After days of looking for the ultimate solution to this problem.. i found this... sqlplus -s USER/PASS@DB <<EOF | awk '{if(NR==1) printf("%s ", $1); if(NR==2) printf("%s ", $1);}' | read VAR1 VAR2 set head off... (2 Replies)
Discussion started by: pranavagarwal
2 Replies

9. Shell Programming and Scripting

error in passing a variable to sqlplus from a shell script

hi, I am using a shell script from where i will be conecting to sqlplus.. i am having a problem in passing a variable to sqlplus query.. i will be assigning the variable in the unix environment..whenever i am trying to pass a variable having the contents greater than 2500 characters, i am... (3 Replies)
Discussion started by: kripssmart
3 Replies

10. Shell Programming and Scripting

How to pass variable to SQLPLUS in a ksh script?

Hi, I am writing a ksh script which will use sqlplus to run a sql and pass 2 variables as the SQL request. In the ksh script, I have 2 variables which are $min_snap and $max_snap holding 2 different numbers. Inside the same script, I am using SQLPLUS to run an Oracle SQL script,... (6 Replies)
Discussion started by: rwunwla
6 Replies
Login or Register to Ask a Question