Problems while spooling data from Oracle. It spools elapsed time instead of spooling result of sql query into output file

Question

I am trying to make a cron job that runs once a day. On invoking the shell script, it calls a Sql file. Which in turn Spools data into a file. This file is Picked by Shell Script and then mailed accordingly. Problem is when I try to spool data , it is writing elapsed time instead of writing actual result of query to the output file.

Here is the sql file I am using.

set define off
set numformat 99999999999999999999999999
set markup html on
set serveroutput on
set head on
set pages 3000
set echo off 

DECLARE
total integer :=0;
total = select count(*) from t_c_table1 vt, t_c_table2 ti WHERE vt.f_item_id = ti.f_item_id (+) AND (f_update_date < sysdate - 30)order by F_INSERT_DATE desc; 

IF total > 0 then 

spool /home/output.csv
select f_name, count (*) from t_c_table1 where F_INSERT_DATE < sysdate-100 group by f_item_provider_id;
spool off

END IF

I get output like Elapsed: 00:00:00.506 in the spooled csv file.

Where am i going wrong? Please Help. Thanks in Advance..

`spool` is a sqlplus command. You can't use that inside a SQL statement or a PL/SQL block — , Apr 16 '18 at 12:25

score 1 · Answer 1 · answered Apr 16 '18 at 12:43

Code you posted is wrong, it won't even compile in Oracle so I'm surprised that you got anything at all.

As there's no SET TIMING ON, I'm not sure what produced the elapsed time line in the spooled file. Maybe it is some old, previously created CSV file you're looking at?

Apart from the fact that SPOOL is a SQL*Plus command (so you can't invoke it in a PL/SQL procedure), the way you calculated the TOTAL variable's value is wrong - it should be part of the SELECT ... INTO statement.

SQL> declare
  2    total integer := 0;
  3  begin
  4    select count(*)
  5      into total
  6      from dept;
  7
  8    if total > 0 then
  9       spool test.csv
 10       select * from dept;
 11       spool off;
 12    end if;
 13  end;
 14  /
     spool test.csv
           *
ERROR at line 9:
ORA-06550: line 9, column 12:
PLS-00103: Encountered the symbol "TEST" when expecting one of the following:
:= . ( @ % ;


SQL>

If you want to spool data conditionally, you'll have to use the UTL_FILE package.

Or, you could do it "interactively" so that SQL*Plus asks you whether you want to spool data or not, as Alex Poole described in his answer here.

Problems while spooling data from Oracle. It spools elapsed time instead of spooling result of sql query into output file

1 Answers1