How to Assign SQL Query Results to Variables in Linux?

Shell Programming and Scripting
1

Hi,

I am new to linux...

How to Assign SQL Query Results to Variables in Linux,i want ti generate it in param files, Can anyone please explain me.

Ex:

SQL> Select * from EMP;
O/P: Emp_No	Emp_Name
       1	       AAA
       2	       BBB
       3	       CCC

[/CODE]

and I want expected output is.....

V1=1
V2=AAA these both variables should store in one flat file...and other two records need to store in another flat file....

--Thanks in Advance
$ravan

2 
#!/bin/bash

# Connect to DB and spool query result into a CSV file
sqlplus -s user/password@instance << EOF
set echo off head off feed off pagesize 0 trimspool on linesize 1000 colsep ,
spool output.csv
select emp_no, emp_name from emp;
spool off;
exit;
EOF

# Using while loop read values into variables from CSV file and create flat file for each records
counter=1
while IFS=, read V1 V2
do
   echo "${V1} ${V2}" > param_${counter}.txt
   counter=$(( counter + 1 ))
done < output.csv
3

Hi bipinajith

Thank you for quck reply...i have tried but i need the output like...

$PMFailureEmailUser=abcd@gmail.com,ssssss@gmail.com.com
$DBConnection_Bmd=Orcl
$$DBConnection_Bmd=Msg_ETL_Orcl
$$SOURCE_NAME='EMPLOYEE'
$$LOAD_TYPE='DELTA'
$$SUBJ_NAME=MICROSOFT
$$Script_Path=D:/PMRootDir/Scripts/CallProfile.sh
$$IS_TEMP_FLAG=N
$$Start_Task_Name=wkl_Map_PROCESS_LOG
$$Restart_Workflow=N
$$Param_File_Path=D:/PMRootDir/Param/masaga
$$Create_Param_Wf_Name=wf_Create_Paramfiles
$$Target_Schema_Type=C
$$Where_Clause=
$$Trgt_Load_Order=2

i need to assign each coloumn valuse to one variable like above and all these values need to append in one file (Param_Create.prm)...
i am trying with your previous example but i am getting error with '$$' symbols please tell me is there any other way we can concat that symobl to output result before appending to file...
Please help me on this....

Thx..Sravan

4

If you want to write $ sign to a file then you have to escape this sign. Otherwise it will be consider as a variable. See the difference below:

bash# echo "$sravana"

bash# echo "\$sravana"
$sravana
5

Hi bipinajith

I am trying to execute this script, it is executing but it is not generating values to actual flat file, but it wil generate values into .log file and .csv file, Please correct me if i went wrong in the code....

#!/bin/bash

# Connect to DB and spool query result into a CSV file
filepath="/sqlany_data/infa_shared/Scripts/masaga"
DBUSER='etl_user'
DBPWD='etl_dev'
DB='keymsga'
sqlplus -s $DBUSER/$DBPWD@$DB <<EOF  > ${filepath}/Masaga_Param_1226.log

set echo off head off pagesize 0 trimspool on linesize 1000 colsep ,
spool Masaga_Param_var.csv
select  EMAIL_USER_NAME,DB_CONN_SCHEMA  from Msg_Ks_Paramfiles where WORKFLOW_NAME='wf_Msg_Ks_Start_Build_Feat_Delta';

spool off;
exit;
EOF

while IFS=, read USER_NAME  DB_CONN_SCHEMA

do
   echo "{\$USER_NAME}"  > param_Ks_Build_Feat.txt
   echo "{\$$DB_CONN_SCHEMA}" > param_Ks_Build_Feat.txt
done < Masaga_Param_var.csv

and how i should pass query if i want o/p variable value like " $$DB_CONN_SCHEMA=ORACLE"...
Thanks...
Sravan

6

You don't need that while loop. You can format the result in the SQL itself and spool it to file: param_Ks_Build_Feat.txt Here is the modified code:

sqlplus -s $DBUSER/$DBPWD@$DB << EOF
set echo off head off pagesize 0 trimspool on linesize 1000 
spool param_Ks_Build_Feat.txt
select '$USER_NAME=' || EMAIL_USER_NAME || '$$DB_CONN_SCHEMA=' || DB_CONN_SCHEMA 
from Msg_Ks_Paramfiles where WORKFLOW_NAME='wf_Msg_Ks_Start_Build_Feat_Delta';
spool off;
exit;
EOF