Redirect the output of a query in a file

Question

What can I do to redirect the query result in a text file in sqlplus

I tried: start requete.sql > resultat.txt but it doesn't work.

Solution

You are mixing shell and sqlplus commands. sqlplus doesn't understand redirections, you have to use the spool keyword.

For instance:

SQL> spool /tmp/result.txt
SQL> start requete.sql
SQL> spool off

From shell:

$ sqlplus scott/tiger @requete.sql > /tmp/result.txt

Comments

[apaul]

I've written /tmp/result.txt but the path is up to you. Just replace /tmp/result.txt by /path/to/the/file/you/want.txt. It's the same for the start or @ keywords: @/path/to/you/file.sql

[Aomine Daiki]

i have a problem spool dosen't work

[apaul]

Impossible. Even if you specfied a path whose access is denied, you'd get an error. Note that when you use spool and specify no more options, the result of query is also displayed on your screen. You'll have to give more info than just "doesn't work".

[Aomine Daiki]

this is the error: SP2-0606: Cannot create SPOOL file "/resultat.txt"

[apaul]

It cannot create the file because you try to write in the root (/). At this place, only the user root is allowed to create files and directories. Specify a path where your user has enough rights (in his home directory for instance)