HELP. Oracle Call from ksh script

Shell Programming and Scripting
1

I have searched the forums and couldn't find my specific issue so I figure that I would post on it.

I am trying to run a simple sql script that spools to a flat file from a unix script.

I have tried to make the call outright from inside of the ksh script as such:

sqlplus username/pass@SID @script.sql

everytime I do I get the TNS invalid error.

So I went this route from within the script

sqlplus username/pass@sid

and I an now able to connect:

Here is my code

rm /tmp/blah.txt



sqlplus  user/pass@SID

spool /tmp/blah.txt
SELECT
  ta.foo
  ,cn.bar
  , count(*) cnt
FROM
  foo_bar ta
  , bar_foo cn
  WHERE
    ta.blah <= sysdate
    and ta.bleep=09
    and ta.foo=cn.foo
Group BY cn.bar,ta.bar
ORDER BY cnt DESC
spool off;

cat /tmp/blah.txt | mailx -s "HERE YOU GO" whoever@wherever.com
chmod 666 /tmp/blah.txt

Please help me.

2

You'll maybe need to adjust for the value of your SID, unless it's got WOODY and BUZZ as counterparts elsewhere on the system. :slight_smile:

Otherwise, you'd want to post your actual script. The one provided is not likely to work, since the shell doesn't interact with sqlplus quite this way. It'd take the form of either one of the following:

sqlplus user/pass@SID <<EO_SQL
spool /tmp/blah.txt
SELECT
ta.foo
,cn.bar
, count(*) cnt
FROM
foo_bar ta
, bar_foo cn
WHERE
ta.blah <= sysdate
and ta.bleep=09
and ta.foo=cn.foo
Group BY cn.bar,ta.bar
ORDER BY cnt DESC
;
spool off
-- note the moving of the ";" or GO string...
EO_SQL

sqlplus user/pass@SID @sql_file.sql 
# where sql_file.sql contains the SQL commands, including an exit after spool off...

...also, note that the ";" has to precede the "spool off" because ";" execs the SQL...whereas spool just serves to close the output file...

3

Adding the EO_SQL and moving the ; worked perfectly.

Thanks a million (pushing it, is there a way to hide the actual query from the output?)

4

set commands are your friend... They precede the actual SELECT statement. For example:

   set      colsep                              "|" 
   set      feedback                            off 
   set      heading                             on  
   set      pages                               0   
   set      lines                               200 
   set      underline                           off 
   set      verify                              off 
   set      termout                             off 
   set      trimspool                           on