Forum Stats

  • 3,750,530 Users
  • 2,250,189 Discussions
  • 7,866,998 Comments

Discussions

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

Quentin03
Quentin03 Member Posts: 3
edited Nov 6, 2017 7:47AM in SQLcl

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.

Quentin0329cff59e-e611-431e-ab78-d0315cf0e0b5

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Nov 4, 2017 2:50AM Accepted Answer

    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.

    Quentin0329cff59e-e611-431e-ab78-d0315cf0e0b5

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Nov 4, 2017 2:51AM

    "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
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Nov 4, 2017 2:50AM Accepted Answer

    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.

    Quentin0329cff59e-e611-431e-ab78-d0315cf0e0b5
  • Quentin03
    Quentin03 Member Posts: 3
    edited Nov 6, 2017 7:47AM

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