How to get a numeric value from Oracle to UNIX variable without spaces? | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

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

Shell Programming and Scripting


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 03-15-2013
Arun Mishra Arun Mishra is offline
Registered User
 
Join Date: Dec 2012
Last Activity: 11 June 2014, 4:30 AM EDT
Posts: 51
Thanks: 4
Thanked 0 Times in 0 Posts
How to get a numeric value from Oracle to UNIX variable without spaces?

Hi,

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

Code:
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:

Code:
$ 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!

Last edited by jim mcnamara; 03-15-2013 at 09:06 AM..
Sponsored Links
    #2  
Old 03-15-2013
ctsgnb ctsgnb is offline Forum Advisor  
Registered User
 
Join Date: Oct 2010
Last Activity: 1 October 2014, 9:32 AM EDT
Location: France
Posts: 2,930
Thanks: 81
Thanked 624 Times in 596 Posts
did you try


Code:
...
Select trim(count(*)) ...
...

Sponsored Links
    #3  
Old 03-15-2013
franzpizzo's Avatar
franzpizzo franzpizzo is offline
Registered User
 
Join Date: Feb 2013
Last Activity: 6 February 2014, 9:56 AM EST
Posts: 68
Thanks: 0
Thanked 12 Times in 12 Posts
Hi
try this: mind the spaces: "read|space|BD_RC_CNT|space|<|space|<( etc..."

Code:
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)

    #4  
Old 03-15-2013
anbu23's Avatar
anbu23 anbu23 is offline Forum Advisor  
Advisor
 
Join Date: Mar 2006
Last Activity: 8 October 2014, 1:35 AM EDT
Location: Bangalore,India
Posts: 2,089
Thanks: 12
Thanked 131 Times in 126 Posts
Check whether you have tab in BD_RC_CNT

Code:
echo "$BD_RC_CNT"

Sponsored Links
    #5  
Old 03-15-2013
Arun Mishra Arun Mishra is offline
Registered User
 
Join Date: Dec 2012
Last Activity: 11 June 2014, 4:30 AM EDT
Posts: 51
Thanks: 4
Thanked 0 Times in 0 Posts
Thanks, Both the solutions worked perfectly. And yes when i issued the command echo "$BD_RC_CNT" it showed as below

Code:
$ echo "$BD_RC_CNT"
         6

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

Thanks again for the help.
Sponsored Links
    #6  
Old 03-15-2013
anbu23's Avatar
anbu23 anbu23 is offline Forum Advisor  
Advisor
 
Join Date: Mar 2006
Last Activity: 8 October 2014, 1:35 AM EDT
Location: Bangalore,India
Posts: 2,089
Thanks: 12
Thanked 131 Times in 126 Posts
It might be single tab, space and 6. You can check that using cat

Code:
echo "$BD_RC_CNT" | cat -vet

Sponsored Links
    #7  
Old 03-16-2013
Showdown Showdown is offline
Registered User
 
Join Date: Mar 2013
Last Activity: 8 July 2014, 10:00 AM EDT
Posts: 38
Thanks: 1
Thanked 1 Time in 1 Post
Agreed with CTSGNB u can filter this in oracle itself using trim...
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
How to get Oracle variable in UNIX? Arun Mishra Shell Programming and Scripting 7 02-27-2013 10:12 AM
trim spaces in unix for variable manish8484 Shell Programming and Scripting 6 02-09-2012 07:04 AM
delete spaces in the variable in unix script? MARY76 Shell Programming and Scripting 14 01-17-2007 11:08 AM
How to pass unix variable to oracle chiru UNIX for Dummies Questions & Answers 1 06-12-2006 04:10 PM
Replace spaces with 0's having numeric values. videsh77 Shell Programming and Scripting 1 04-15-2005 01:22 AM



All times are GMT -4. The time now is 12:31 AM.