How to get a numeric value from Oracle to UNIX variable without spaces?

Shell Programming and Scripting
1

Hi,

I am using the below code to get a numeric value from oracle to unix variable:

BD_RC_CNT=`sqlplus -s ${WMD_DM_CONNECT} <<EOF
set heading off
set pagesize 0
Select count(*) 
from wmd_bad_data 
where proc_id = ${PROC_ID} 
and file_id = ${FILE_ID} 
and file_dt = to_date(${DATADATE},'yyyymmdd');
exit
EOF`

I issued echo command and output is below:

$ echo $BD_RC_CNT
6

How ever when i tried to find the length of the variable it shows 3 instead of 1:

$ echo `expr length "$BD_RC_CNT"`
3

Can somebody please help in removing the extra characters ?
Thanks in advance!:slight_smile:

2

did you try

...
Select trim(count(*)) ...
...
3

Hi
try this: mind the spaces: "read|space|BD_RC_CNT|space|<|space|<( etc..."

read BD_RC_CNT < <(sqlplus -s ${WMD_DM_CONNECT} <<EOF
set heading off
set pagesize 0
Select count(*) 
from wmd_bad_data 
where proc_id = ${PROC_ID} 
and file_id = ${FILE_ID} 
and file_dt = to_date(${DATADATE},'yyyymmdd');
exit
EOF)
5

Check whether you have tab in BD_RC_CNT

echo "$BD_RC_CNT"
6

Thanks, Both the solutions worked perfectly. And yes when i issued the command echo "$BD_RC_CNT" it showed as below

$ echo "$BD_RC_CNT"
         6

these seem to be some spaces then 6. may be 2 space then 6.

Thanks again for the help.:slight_smile:

7

It might be single tab, space and 6. You can check that using cat

echo "$BD_RC_CNT" | cat -vet
8

Agreed with CTSGNB u can filter this in oracle itself using trim...

9

Try declaring the data type:

 
sqlplus -s ${WMD_DM_CONNECT} << EOF | read BD_RC_CNT
whenever sqlerror exit sql.sqlcode;
set serveroutput on;
DECLARE
   TempReturn Number(5);
BEGIN
  SELECT 6     
  INTO TempReturn
  FROM DUAL;
  dbms_output.put_line(TempReturn);
END;
/
EOF
 
echo 'Value:' ${BD_RC_CNT}
echo 'Length:' ${#BD_RC_CNT}
 

Value: 6
Length: 1