Unix/Linux Go Back    


Shell Programming and Scripting Unix shell scripting - KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and 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 Linux or Unix Question    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 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!Linux

Last edited by jim mcnamara; 03-15-2013 at 09:06 AM..
Sponsored Links
    #2  
Old Unix and Linux 03-15-2013
ctsgnb ctsgnb is offline Forum Advisor  
Registered User
 
Join Date: Oct 2010
Last Activity: 7 April 2015, 6:37 PM EDT
Location: France
Posts: 2,931
Thanks: 81
Thanked 626 Times in 597 Posts
did you try


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

Sponsored Links
    #3  
Old Unix and Linux 03-15-2013
franzpizzo's Unix or Linux Image
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 Unix and Linux 03-15-2013
anbu23's Unix or Linux Image
anbu23 anbu23 is offline Forum Advisor  
Advisor
 
Join Date: Mar 2006
Last Activity: 16 March 2015, 4:56 AM EDT
Location: Bangalore,India
Posts: 2,136
Thanks: 14
Thanked 150 Times in 143 Posts
Check whether you have tab in BD_RC_CNT

Code:
echo "$BD_RC_CNT"

Sponsored Links
    #5  
Old Unix and Linux 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.Linux
Sponsored Links
    #6  
Old Unix and Linux 03-15-2013
anbu23's Unix or Linux Image
anbu23 anbu23 is offline Forum Advisor  
Advisor
 
Join Date: Mar 2006
Last Activity: 16 March 2015, 4:56 AM EDT
Location: Bangalore,India
Posts: 2,136
Thanks: 14
Thanked 150 Times in 143 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 Unix and Linux 03-16-2013
Showdown Showdown is offline
Registered User
 
Join Date: Mar 2013
Last Activity: 2 April 2015, 5:50 AM EDT
Posts: 39
Thanks: 1
Thanked 1 Time in 1 Post
Agreed with CTSGNB u can filter this in oracle itself using trim...
Sponsored Links
Closed Linux or Unix Question

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux 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 04:29 AM.