Export CSV in a planified task with a Batch working with SQLCL

Quentin03

Hello,

SQLCL is exactly what I need, but I've a big difficulty on one little thing :

I want to make a script (batch file) with Auto connection and just after an EXPORT CSV (on a remote desktop : not on the server).

So I'm using the pipe method with SQLCL in a Batch File:

    echo SET SQLFORMAT CSV <
    echo SPOOL export.csv <
    echo SELECT COUNT(*) FROM ARTICLE; <
    echo SPOOL OFF | C:\Work\Soft\sqlcl\bin\sql.exe login/[email protected]:1521/DB.SCH

It's working (no errors in console) but, impossible to find the file `export.csv` : when I change the destination `c:\...` it's working too but impossible to find the created file. It's working fine with SQL Developer and the file is created on my dekstop, so I don't understand why it's not the same case for SQLCL.

Gaz in Oz

Here's your script written so it actually produces export.csv with sqlcl:

F:\>sql -vSQLcl: Release 17.3.0.256.1818 ProductionF:\>type [email protected] offecho SET SQLFORMAT CSV ^& ^echo SET FEEDBACK OFF ^& ^echo SPOOL export.csv ^& ^echo SELECT COUNT^(*^) FROM dual ^& ^echo SPOOL OFF | sql -noupdates -s -l gaz/[email protected]
F:\>z>NULF:\>type export.csv"COUNT(*)"1F:\>

Note how you need to use the caret to escape special characters and for end-of-line continuation.

Use of -s SILENT and -l Only try logging in once, instead of the default three trys.

-noupdates to not bother going off into the internet and looking for later versions of sqlcl.

The >NUL redirect to suppress the echoing of the sql commands to screen.

Gaz in Oz

"working"... just because you see no errors does NOT mean it is working as you want.

Regardless... may be you can adapt this to your needs:

F:\>sqlplus -vSQL*Plus: Release 12.2.0.1.0 ProductionF:\>type [email protected] offSETLOCAL(echo set markup csv on ^& ^ echo set feedback 0 pages 0 ^& ^ echo select count^(*^) from dual; ^& ^ | sqlplus -s -l %1)ENDLOCALF:\>x gaz/[email protected]"COUNT(*)"1F:\>

The caret ("^") escapes brackets etc and the trailing caret at the end of line allows line continuation in batch fiies.

With sqlplus 12.2.0.1 the "set markup csv" was added.

Oracle Instant Client Downloads

Gaz in Oz

Here's your script written so it actually produces export.csv with sqlcl:

F:\>sql -vSQLcl: Release 17.3.0.256.1818 ProductionF:\>type [email protected] offecho SET SQLFORMAT CSV ^& ^echo SET FEEDBACK OFF ^& ^echo SPOOL export.csv ^& ^echo SELECT COUNT^(*^) FROM dual ^& ^echo SPOOL OFF | sql -noupdates -s -l gaz/[email protected]
F:\>z>NULF:\>type export.csv"COUNT(*)"1F:\>

Note how you need to use the caret to escape special characters and for end-of-line continuation.

Use of -s SILENT and -l Only try logging in once, instead of the default three trys.

-noupdates to not bother going off into the internet and looking for later versions of sqlcl.

The >NUL redirect to suppress the echoing of the sql commands to screen.

Quentin03

Thanks ! It's working fine. My bad knowledge in batch programming serve me.