1

I have a sql script which creates a spool file.

When I do not have data, I am getting an empty file created. I do not want the file to get created in that case.

How can I do this?

set termout off;
set newpage 0;
set space 0;
set linesize 255;
set pagesize 0;
set echo off;
set feedback off;
set heading off;
set verify off;
set trimspool on;



UNDEFINE p_xml_filename


DEFINE p_xml_filename=&1

spool &p_xml_filename

SELECT * FROM emp;

spool off;
set feedback on
set verify on
set heading on
set echo on
exit
Mat
  • 202,337
  • 40
  • 393
  • 406
saniya mapari
  • 71
  • 1
  • 10

1 Answers1

0

Instead of handling it in SQL*Plus, I would rather do it at OS level. In your scenario, you want to spool the file only if data exists. But, to do that, you need to check the COUNT of rows in the table, thus it is an overhead to the process.

Alternatively, I would:

  • Let the spool happen always
  • Do a grep of the spool file to check the content
  • Remove the file if no content found. rm filename
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Thanks ...This works but instead of using grep I put this condition if [ -s "$exportFileName" ] then echo "$exportFileName has some data." # do something as file has data else echo "$exportFileName is empty." rm $exportFileName fi – saniya mapari Apr 06 '15 at 12:24
  • Thanks for the feedback. Yes, you could do that too. – Lalit Kumar B Apr 06 '15 at 12:25