Oracle - Execute a bunch of SQL files

[Costas]

So the today experience was tremendous! Something like super LOL!

Had 59 files to be executed versus ORA dbase.. I think ok, is time to bring sql*plus in the game, crap, not compatible with half of the scripts (scripts made by vendor, each file is ~2pages SQL)...

Damn, what to do ? The KB says execute it carefully one by one to SQL DEVELOPER IDE!!!

What the hell are you talking about???????? #LOL

Found SQLcl (sql.exe), you can execute SQL and PL/SQL statements interactively Lives at SQL DEVELOPER at bin folder.. So there you go :

@echo off
 
echo Started %DATE% %TIME%
 
for %%f in (*.sql) do (

if %%f==y.sql (
echo 'y.sql' found, please execute it via SQL DEVELOPER then continue here by 'x.sql'
pause
exit
)

if %%f==x.sql (
echo 'x.sql' found, please execute it via SQL DEVELOPER then continue here by 'z.sql'
pause
exit
)

    echo **Executing %%f...
    ::log to file
    echo exit | C:\sqldeveloper\sqldeveloper\bin\sql.exe -s xxxx/##password###@xxxx.com:1000/XXXXX @%~dp0%%f > %~dp0%%f.log
    ::read log file to screen
    type %~dp0%%f.log
    echo:
    echo **Script %%f execution complete.
    echo:
    echo:
    ::timeout is case user want to break the execute, if the previous script failed
    timeout /t 5
)

echo Master finished...

 

[Costas]

this is to merge all .sql files to one .sql file

@echo off

::where the files for merging stored
set TxtPath=C:\Temp\hhhhhhhhhhh
 
::add this line on top of the file
echo SET timing ON; > C:\Temp\3\merge.txt

::append new line
echo. >> C:\Temp\3\merge.txt

::loop through files
for /r %TxtPath% %%a in (*.sql) do (
::add this line before each
echo prompt '**********************************Starting %%~nxaTT'; >> C:\Temp\3\merge.txt
::append file to merge.txt
more "%%a">> C:\Temp\3\merge.txt
::append new line
echo. >> C:\Temp\3\merge.txt
::append this line
echo COMMIT; >> C:\Temp\3\merge.txt
::append new line
echo. >> C:\Temp\3\merge.txt
)

 

[Costas]

Sql Developer - spool by IDE

normally we using spool command at sql*plus or either at sqldeveloper\sqldeveloper\bin\sql.exe

--

but this can be achieved also by SQL DEVELOPER (underneath using sql.exe) :

--SET TERMOUT OFF; --this is only for sql.exe dont display the output
SET sqlformat CSV;
spool C:\Temp\bb\objects_data.csv;
select row_id from xxxxx;
spool off;

the first 5000 lines will be outputted also on output window but most importantly ALL records exported to the mention file. DONT FORGET THE SEMICOLONS otherwise red messages will appear...

This can be important in cases we want to export a complicated query, as sometimes SQLDEVELOPER throw an error java.lang.NullPointerException when exporting result set to file.



the sqlformat can be :

DEFAULT
CSV
HTML
XML
JSON
ANSICONSOLE
INSERT
LOADER
FIXED
DELIMITED

more
https://oracle-base.com/articles/misc/sqlcl-format-query-results-with-the-set-sqlformat-command
https://docs.oracle.com/en/database...tml#GUID-0AA910C4-C22A-4A9E-BE13-AAA059CC7919



the 5000 UI limitation, can be adjusted by Tools > Preferences

​

more options

--source - https://www.thatjeffsmith.com/archive/2019/06/quick-tip-spooling-to-excel/
cd C:\Temp\bb --export to this directory
set echo on; --print SQL to CSV, otherwise use echo off
SET feedback off; --not spool messages like '10 rows selected'
SET sqlformat CSV;
spool objects_data.csv;
select row_id from xxxxx;
select row_id from yy;
select row_id from xx;
spool off;
--you can even start a new file will inherit same configuration declared on top
spool objects_data2.csv;
select row_id from eeeee;
spool off;

---

not recommended solutions by community :
https://forums.oracle.com/ords/apex...erexception-when-exporting-result-set-to-3459
https://www.thatjeffsmith.com/archive/2019/05/tutorial-exporting-query-results/