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
so that it is effectively executing :
select proc_time_min*60 from eod_run_time where eod_type=
'EOD';
Maybe something in a glogin.sql script ?
You can try something like
sqlplus -s / <<EOF
set serveroutput on size 100000
declare
v_char varchar2(2000);
begin
select substr(dump('${EOD_TYPE}')
dbms_output.put_line(v_cha
end;
.
/
EOF
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)