Passing variables to sql in a unix shell script


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




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';

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};

Who is Participating?
gmyersConnect With a Mentor Commented:
I've a feeling that you've got some extra characters at the start of EOD_TYPE
so that it is effectively executing :

select proc_time_min*60 from eod_run_time where eod_type=


Maybe something in a glogin.sql script ?

You can try something like

sqlplus -s / <<EOF
set serveroutput on size 100000
  v_char varchar2(2000);
  select substr(dump('${EOD_TYPE}'),1,250) into v_char from dual;

to see what SQL is getting in that variable

Ideally you want something like
Typ=96 Len=3: 69,79,68
(ie a three character value with just ASCII characters)
Try without the curly braces:

select proc_time_min*60 from eod_run_time where eod_type=$EOD_TYPE;
pjd1Author Commented:
sorry - still does not work - get the same error.

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.
pjd1Author 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.
pjd1Author 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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.