We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Passing variables to sql in a unix shell script

pjd1
pjd1 asked
on
Medium Priority
5,284 Views
Last Modified: 2013-12-12
Hi

Hopefully this is the correct area for this question- was not sure if it should come under UNIX or Oracle.

Been struggling all morning with this and wondered if anyone could help.  Basically I'm creating a variable from a sql statement and then passing this variable into another sql statement as a parameter and it does not work.  If I create the variable manually i.e EOD_TYPE="'EOD'" and then pass the parameter to the 2nd sql statement as ${EOD_TYPE} it works, if I use a select to create the variable i get the following error:
SP2-0042: unknown command "'EOD'" - rest of line ignored.

It's quite a short script so I'll paste it below for reference.

thanks in advance

Paul


#!/bin/ksh

#EOD_TYPE="'EOD'"

EOD_TYPE=`sqlplus -s /   <<EOF
set head off
set feedback off
set pages
select decode(to_char(bus_date,'Day'),'Friday','''EOW''','''EOD''') from trading_area where trga_code='EUR';
EOF`

EOD_RUN_TIME=`sqlplus -s /   <<EOF
set head off
set feedback off
set pages
select proc_time_min*60 from eod_run_time where eod_type=${EOD_TYPE};
EOF`

echo $EOD_RUN_TIME
Comment
Watch Question

Commented:
Try without the curly braces:

select proc_time_min*60 from eod_run_time where eod_type=$EOD_TYPE;

Author

Commented:
sorry - still does not work - get the same error.

Paul

Commented:
Which sqlplus is causing error?  If the first one works correctly does it set the value of EOD_TYPE correctly?  Include echo $EOD_TYPE after the first sqlplus in your script.

Author

Commented:
both sql's work OK - it's the 2nd one that fails if the variable is created via the 1st sql.  If I put the echo in I get 'EOD' which is what I would expect.
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Great that worked - there were 3 spaces at the begining.  I've used tr -d to remove the spaces and all is now well.

thanks
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.