Shell Programming and Scripting

have you actually debugged so far as to try running the following on the command-line on your own machine?

Code:

$ORACLE_HOME/bin/sqlplus schema/'pwd'@db

I think you'd find that pwd would be a literal string and not what you'd expect it to be...

Generally, you'd want to do something like the following:

Code:

sqlplus -s ${sql_connstr}${my_data} <<-SQL_SCRIPT|egrep -v "^$|rows selected" >|${out_file} 
  ..
SQL_SCRIPT

Now, if you're concerned about your flurry of set commands effecting what's being output to your file, you could play around and manage these to squelch any echoes, etc, or you could also just extend the egrep -v command above to suppress most of the noise.

General comment. No time to post specifics.

By using a unix shell "here" document Oracle is in interactive mode and will give you SQL> prompts etc. which obfuscate your output.

If you invoke sqlplus and ask it to run a SQL program from a file it will all become less complicated. Within the program use "set" statements to turn off (not on) feedback etc. and then use Oracle "spool" commands to output your enquiry to file. There are many examples on this site.

Code:

sqlplus /NOLOG program_name.sql

to methyl's point, I believe: yes...an external SQL file is useful in terms of providing a single point of SQL behavior. However, and possibly to the OP's use of the here doc, it's often more portable to embed the SQL into the script via the here doc.

Overall, I guess you might say, pick a paradigm and go with it, rather than cobble together approaches. Using a here doc to then call a SQL file may not simplify things.

First, try to manually connect to your database first. If it works, try the code below.

If you want to store the value in a file. Please see the following code:

Code:

$ORACLE_HOME/bin/sqlplus -s schema/'pwd'@db <<  EOF > /home/count/infoc.txt
set echo on;
set feedback on;
@/home/count/qry.sql;
EOF

If you want the output the result to the $RESULT parameter:

Code:

RESULT=`$ORACLE_HOME/bin/sqlplus -s schema/'pwd'@db <<  EOF
set echo on;
set feedback on;
@/home/count/qry.sql;
EOF`
echo $RESULT

Then remove the spool line from your SQL file