Spooling in Oracle, MySQL, Postgres and Sybase

To spool the content of an answer of your query you have to:

connect do DB using, (eventualy format your output fo fullfill your needs,) turn spooling on by providing filename where results will be written, submit your query, turn spooling off

1) Oracle:

sqlplus <user_name>[/password][@<connect_identifier>]
spool FILENAME
query
spool off

2) MySQL:

a) using ‘INTO OUTFILE’ phrase:

log in to MySQL and perform this query:

select * from Table_name into outfile ‘filename’;

File will be written in /srv/database/mysql/DB_NAME

b) from OS:

mysql  -e “select * from Table_name” > filename

File will be written in path where mysql was executed (of course if the user has parmissions to write files there).

or

mysql  < input_file > filename

In this case you have to prepare input file with appropiate query/queries and file will be written as above.

3) PostgreSQL:

a) from psql:

\o filename
select * from Table_name;

File will be written in path where mysql was executed (of course if the user has parmissions to write files there).

b) from OS:

psql -o filename -c ‘select * from Table_name;’

4) Sybase:

only from OS:

isql -Uuser -Ppassword < input_file > filename

In this case you have to prepare input file with appropiate query/queries and file will be in path provided in ‘filename’ (of course if the user has parmissions to write files there).

Of course you can earlier provide a file containing above commands and execute it by crontab or in some shell script and spooling will be done without your intentional loging to DB.

One Comment

  1. vijay says:

    In sybase,
    can i spool to a file, how oracle does.?

    regards,
    Vijay

Leave a Reply

Your email address will not be published. Required fields are marked *

*

question razz sad evil exclaim smile redface biggrin surprised eek confused cool lol mad twisted rolleyes wink idea arrow neutral cry mrgreen

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>