UNIX for Advanced & Expert Users

Hi evrybody!!!!

I have a problem with this shell script

INICIO=$(sqlplus -s user/user@db1 << END | awk '{printf $1}'
set head off
set feed off
select to_char(min(create_dt) , 'HH24') from table_name where trunc(create_dt)=trunc(sysdate-2);
END)

I want to recover, in INICIO, the min time from a group of records but the problem is I am using the to_char command with (') symbol, as you see in the example 'HH24'. With Oracle it works OK, directly by sqlplus. But with sh scripts it doesn't. I tried with '' double single quoted or " double quoted and \' but the problem continues, could somebody have an advice to solve the problem?

Thanks

José

I made a couple of changes because I'd
also like you to try this version just
to see what awk prints out.

#!/usr/bin/ksh
sqlplus -s user/user@db1 << END | awk '{printf $1}'
set head off
set feed off
select to_char(min(create_dt) , 'HH24') from table_name where
trunc(create_dt)=trunc(sysdate-2);
/
END

exit 0

I added the "/" to terminate the script.
I'm assuming your trying to get the output
of awk into the variable INICIO. For this, it may
be better to use the "read" command. I'll have
to think about that some more.

If it's a problem with the ' in a shell script, have you tried escaping them with a \ in front of them? Also, could the numbers of embedded ('s in the script be a problem also? I haven't tried this, but how about:

sql_job () {
sqlplus -s user/user@db1 << END
set head off
set feed off
select to_char(min(create_dt) , 'HH24') from table_name where
trunc(create_dt)=trunc(sysdate-2);
/
END
}

INICIO=`sql_job | awk '{print $1}'`

echo $INICIO


This is just a guess... like I said, I didn't try this script, but this is how I would have tried it...

HTH
--
LF

I thought about it a bit more and
here is what I'm thinking...

INICIO=$(sqlplus -s user/user@db1 << END
set head off
set feed off
set serveroutput on
declare mydate char(whatever_the_length);
begin
select to_char(min(create_dt) , 'HH24') from table_name where
trunc(create_dt)=trunc(sysdate-2);
dbms_output.put(mydate);
end;
/
END)

...this should simply write the result
of the select to stdout which should then
be assigned to INICIO. Note that I added
a few things to the sql and this gets rid
of the " | awk " redirection stuff.

I was thinking too fast this morning
without coffee...

See corrections...

INICIO=$(sqlplus -s user/user@db1 << EOF
set head off
set feed off
set serveroutput on
declare mydate char(whatever_the_length);
begin
select to_char(min(create_dt) , 'HH24') into mydate from table_name where
trunc(create_dt)=trunc(sysdate-2);
dbms_output.put(mydate);
end;
/
EOF)

...I forgot the "into mydate" in the select.
Also, I changed END to EOF since "END" is a
SQL keyword it's probably best not to use
it (even though the script terminator "/"
should have precluded any misinterpretation).
Sorry about that