Spool file, only if data exists.

Shell Programming and Scripting
1

Hi All,

I have one Question, that is it possible to check the spool output?
What i mean to say is "spool the file only if data exists"

I am trying to fetch data from a sql query, and want to generate a file, on a condition that the file should be generated, only if the output of the sql query gives me atleast one record.

Thanks in advance.
spiabhi

2

Sounds like an Oracle SQLPlus thing. SQLPlus is not big on conditional language with presentation features like spool, especially with rdbms engine things like row count. Spool it always, grep for content, rm if none and log that you removed it.

3

Same idea as DGPickett proposed, but with different storage: create a temporary table, select into that, run a COUNT(*) against it, and spool out if that's more than 0 records. With a bit of PL/SQL it can be done in 1 call to the sqlplus client.

4

@pludi: controlling SQLPlus commands like spool with PL/SQL is hard, but SQLPlus can be tricked to act conditional.

column spool_destination noprint new_value spooldestination
SELECT DECODE(COUNT(*),0,'/dev/null','spoolfilename')  AS spool_destination
  FROM temporary_table_containing_resultset;
spool &spooldestination
SELECT * 
  FROM temporary_table_containing_resultset;
spool off
2 Likes
5

Yes, sometimes the data is the control, like NAN. Use plenty of comments for maintainability! :smiley:

6

I usually just do it this way, if I need to get a count for reporting or other uses:

   sqlplus -s "${USR}/${PSS}@${DB}" <<EOF > $OUT_FILE
      WHENEVER SQLERROR EXIT 1;
      SET ECHO OFF NEWPAGE 0 SPACE 0 PAGESIZE 0 FEED OFF HEAD OFF
      SELECT count(*)
        FROM TABLE_A A
            ,TABLE_B B
       WHERE A.X = B.X
         AND A.Y = B.Y
      ;
      EXIT
EOF

   rc=$?
   if [[ $rc -eq 0 ]]; then
      count=$(( `cat $OUT_FILE` ))
      echo "Total: $count"
   else
      exit $rc
   fi

   if [[ ${count} -gt 0 ]]; then
     # Do something...
   fi