Today I was running some SQL Queries and I wanted to redirect all the output of my SQL Queries to one file. Initially I thought it can be done similarly to how we usually do it in UNIX command prompt (simply redirecting output of a command to a file using >or >>) but it didn’t work.
Before searching on Internet, I first preferred to ask my senior for the solution and came to know the easiest solution for the problem and it is a very simple method. Let me share with you the solution.
Steps to redirect Output of any SQL query to a File:
Launch the SQL prompt, login with the correct username and password. Now execute the below commands one after another on the SQL prompt.
> [Run your all SQL Queries/Commands]
> spool off
After performing all the above steps you will get all your output into your specified file.
You must be thinking – what if you run one single .sql file from the SQL Command prompt instead of running each SQL Queries one after another where your .sql file will have all our SQL Queries? Sorry!! I also thought the same easy way of doing the above steps and also tried it but didn’t work.
Let me tell you what spool actually does so that you will understand the purpose of running each SQL queries one after another before we run spool off command and not from .sql script.
#1Our very first step is to set file using spool command.
#2 Now whatever you run after it on SQL prompt, SQL plus automatically redirect everything it sees on the Command prompt to the file specified in the step one.
#3 With the last command we are just asking SQL plus to stop appending whatever we are typing on the SQL prompt to a file specified in step #1.
So now if you will use .sql script to run all your SQL queries then in that case you will see only the command in a file which you have used for running .sql script.
In a file you will see just “SQL> @YourSqlScript.sql”
I know now you must be thinking how easy it is to redirect all output of the SQL queries to a file.
If you observe that the output in a file is not properly formatted then you have to use some more set commands to make it formatted. You have to run all those set commands before the step 1 mentioned above.
There are lot many commands available and few of them are –
set linesize 180 – this will increase the line size
set numwidth 30 – default width for Number columns
set maxdata 20 – max width for columns
set long 20 – default width for LONG columns
set longchunksize 40 – default width for LONG columns