I want to export the spool in two different format for the below given sample but unluckily, spool Main_Spool.log is not collecting all the output. Is there is any possibility/alternative way to do this ?
SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET ECHO OFF
SET VERIFY OFF
SPOOL Main_Spool.log
DECLARE
...................
BEGIN
<QUERY - 1>
DBMS_OUTPUT.PUT_LINE('First Output');
END;
/
DECLARE
................
BEGIN
<QUERY - 2>
DBMS_OUTPUT.PUT_LINE('Second Output');
END;
/
SPOOL OFF
/
SPOOL Main_Spool.log append
SPOOL CSV_Format_spool.csv
DECLARE
................
BEGIN
<QUERY - 2>
DBMS_OUTPUT.PUT_LINE('Third Output');
END;
/
SPOOL OFF
/
spool can only write to one file at a time. You will need to use OS commands to copy it, e.g.
host copy Main_Spool.log CSV_Format_spool.csv
Or spool everything to separate files and then combine them as you need using host commands:
host type file1 file2 > file3
By the way, the / character executes whatever is in the SQL buffer, so the script above will execute the 'Second Output' block twice.