Spool

Transformation for this command is pending

Description

The SPOOL command stores query results in a file, or optionally sends the file to a printer. (Oracle SQL Plus User's Guide and Reference)

Oracle Syntax

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

SnowSQL (CLI Client) !spool

!spool [<file_name>] | [off]

1. Spool without options

This case is functionally equivalent.

When the SPOOL command is not accompanied by any option, by default it creates a new file with the specified name and extension. The SPOOL command is replaced by the !spool command.

Oracle

Command

SPOOL temp
SPOOL temp.txt

SnowSQL (CLI Client)

Command

!spool temp
!spool temp.txt

2. Spool with write options

This case is not functionally equivalent.

Oracle allows 3 types of options when writing to a file through the SPOOL command, the CREATE and APPEND options create a file for writing from scratch and concatenate text to the end of an existing file (or create a new one if it doesn't exist) respectively. Snowflake does not support these options, however, its default behavior is to create a file and if it exists, concatenate the text in it. The REPLACE option, on the other hand, writes to the specific file replacing the existing content. To simulate this behavior in Snowflake it is recommended to delete the file where you want to write and start writing again, as shown in the following code

Oracle

Command

SPOOL temp.txt CREATE
SPOOL temp.txt APPEND
SPOOL temp.txt REPLACE

SnowSQL (CLI Client)

Command

!spool temp.txt
!spool temp.txt

!system del temp.txt
!spool temp.txt

3. Spool turn off

This case is functionally equivalent.

Oracle has two options to turn off results spooling, OFF and OUT. both are meant to stop rolling, with the difference that the second also sends the file to the computer's standard (default) printer. This option is not available on some operating systems. Snowflake only has the option to turn off results spooling

Oracle

Command

SPOOL OFF
SPOOL OUT

SnowSQL (CLI Client)

Command

!spool off
!spool off

Known Issues

No Known Issues.

Related EWIs

No related EWIs.