SQL> spool /tmp/myfile.lst Becuse the spool command interfaces with the OS layer, the spool command is commonly used within Oracle shell scripts. Your CSV file will then get created at your specified path.
In the above example I can re-write the script asīy adding the text highlighted in red you can get the output of sql_statement_1 and sql_statement_2 in main.log itself, which was missed out due to traditional nesting of spool in version of oracle client prior to 10g.įor more enhancement of SPOOL command (CREATE, REPLACE) you can visit Oracle Documentation on Spool command here. The 'spool' command is used within SQLPlus to direct the output of any query to a server-side flat file. In order to execute the Spool, you’ll need to run it as a script (for example, if you are using Oracle SQL Developer, you may press F5 to run the Spool as a script). Starting Oracle 10g Client you can use SPOOL APPEND command which will append to the end of existing file and this can be used a workaround to achieve the nesting of spools main.log, inner1.log and inner1_inner.log.īut you cannot achieve this using traditional spool command prior to 10g client, where nesting of spool command was not allowed and in above scenario the main.log won’t show the result of and. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard. So we could take the above data, and use the STRINGAGG function to list all the task names in one big comma separated list.
In above example you might want to have separate spool file for each of the scripts viz. If you want to stick with the comma separated string, you can find many string split functions online using CLR, CTE, XML. There may have been instances when you might have had numerous sql scripts, with each having their own spool and you might have wanted to invoke them using a main script. Have you ever tried to execute nested scripts with spool within each file.
0 kommentar(er)
