How to get Oracle variable in UNIX?

Shell Programming and Scripting
1

Hello All,

I need to export various variables in UNIX. But the values of those variables will come from Oracle.

Say i have Oracle query:

 
Select
file_id, file_desc, file_freq_cd, load_table_nm, load_stored_procd_nm, load_proc_typ_nm, err_rec_cnt_thrsld_nbr, file_expc_rec_cnt From Table1 where file_id=1

The above query will always return one row.

i have variables to be exported for each of the columns selected above as below:

 
export FILE_ID=
export FILE_DESC=
export FILE_FREQ_CD_ID=
export LOAD_TBL_NM=
export LOAD_STORED_PROCD_NM=
export LOAD_PROCD_TYP_NM=
export LOWER_SHORT_NM=
export ERR_REC_CNT_THRSLD_NBR=
export FILE_EXPC_REC_CNT=

I am not sure how can i do this at once. Kindly help me on the same
Any help appreciated!

---------- Post updated at 01:52 PM ---------- Previous update was at 12:58 PM ----------

Can anybody please help on the above issue ?

2

Its not easy to understand what you like.

If output is like this: 34,23,645,82,110,412,8,65 you can try this:

echo "34,23,645,82,110,412,8,65" | awk -F, '
{ print "export FILE_ID="$1}
{ print "export FILE_DESC="$2}
{ print "export FILE_FREQ_CD_ID="$3}
{ print "export LOAD_TBL_NM="$4}
{ print "export LOAD_STORED_PROCD_NM="$5}
{ print "export LOAD_PROCD_TYP_NM="$6}
{ print "export LOWER_SHORT_NM="}
{ print "export ERR_REC_CNT_THRSLD_NBR="$7}
{ print "export FILE_EXPC_REC_CNT="$8}'


export FILE_ID=34
export FILE_DESC=23
export FILE_FREQ_CD_ID=645
export LOAD_TBL_NM=82
export LOAD_STORED_PROCD_NM=110
export LOAD_PROCD_TYP_NM=412
export LOWER_SHORT_NM=
export ERR_REC_CNT_THRSLD_NBR=8
export FILE_EXPC_REC_CNT=65

Your SQL example do miss input for LOWER_SHORT_NM

3

The sqlplus (part of the oracle distribution) command returns 3 lines, the capitalised field names , a separator line and the values.

$(echo $(sqlplus -s /nolog <<EOQ
connect ${USER}/${PASS}@${SID}
set linesize 1000
select
  file_id, 
  file_desc, 
  file_freq_cd, 
  load_table_nm, 
  load_stored_procd_nm, 
  load_proc_typ_nm, 
  err_rec_cnt_thrsld_nbr, 
  file_expc_rec_cnt 
From Table1 
where file_id=1 ;
quit
EOQ )
| grep -v '-----'|awk 'if(/^FILE_ID/){print "names=("$0")"}else{print "values=("$0")";})
index=0
for i in ${names[@]} ; do
  export $i=${values[$index]}
  index=$(($index + 1 ))
done

So the next step is to read the field names and values into 2 arrays and then step through the arrays assigning and exporting the values

I use grep to remove the unwanted separator line, then use awk to write out my array assignment statements, these are evaluated by the $(...) expansion.

Then we simply step through the 2 arrays...

WARNING: I have not tested this code, some errors will have crept in, but you get the idea...

4

You can turn off heading and seperator and skip the need to process them.
The code is tested using a different query to give an idea how this could be done:

#!/bin/bash
read READ_UN READ_SD <<< $(sqlplus -s $USERNAME/$PASSWORD <<SQL
set heading off
select user, sysdate from dual;
SQL
)
echo $READ_UN
echo $READ_SD
5

read thing doesnt work cero as i do have spaces in the values itself as in one column has a value arun mishra.. read will treat it as two fields instead of 1.
can i assign evrything to arrayvariable and fetch 1 by 1:

 
arrayvar=`sqlplus -s ${WMD_DM_CONNECT} <<-EOF
set wrap off
set linesize 30000
set feedback off
set pagesize 0
set verify off
select file_id, file_desc, file_freq_cd, load_tbl_nm, load_stored_procd_nm, load_proc_typ_nm, err_rec_cnt_thrsld_nbr, file_expc_rec_cnt,grth_pct,rec_trminator_txt
from wmd_file_lkup where file_shrt_nm='CGENSITE';
exit
EOF`
 
export FIA_ID=aarrayvar[0]
export FIA_DESC=aarrayvar[1]..
 
....
.....
and so on..

let me know if i make some sense.. and kindly suggest on the same.

6

I don't think the array approach will solve the problem with spaces.
An ugly solution that comes to my mind is to generate the export statements via sql and execute them. Enclose the fields with spaces with double quotes.

#!/bin/bash
eval $(sqlplus -s $USERNAME/$PASSWORD<<SQL
set heading off
set lines 10000
select 'export EXP_SD="'||to_char(sysdate,'YYYY MM DD HH24 MI')||'"; export EXP_UN='||user from dual;
SQL
)
echo "$EXP_SD"
echo "$EXP_UN"

Edit: this may fail if you generate very long lines, but you can query only a few fields in one go and have consecutive queries do the rest.

7

I have a faint memory from over ten years back that you can set the column separator in SQL. Do that, and set the shell's IFS variable accordingly, and the read thing should succeed.

8 
$ cat x
#!/bin/ksh

##  The command in $() returns COUNT=<nbr> which is then eval'd to create
##  and set a shell variable called COUNT. There is a carriage return after the
##  set heading off; command.
eval $(print "set heading off;
select 'COUNT='||count(*)||';SYSDATE='||sysdate from dual;" | sqlplus -s / )

print "The shell variable COUNT is   [$COUNT]"
print "The shell variable SYSDATE is [$SYSDATE]"

exit 0
$ ./x
The shell variable COUNT is   [1]
The shell variable SYSDATE is [27-FEB-2013]
$

See this for more info: