3

I know you use the spool command when you are trying to write a report to a file in Oracle SQLplus.

What is the equivalent command in MySQL?

This is my code:

set termout off

spool ${DB_ADMIN_HOME}/data/Datareport.log @ ${DB_ADMIN_HOME}/Scripts.Datavalidation/Datareportscript.sql

spool off
exit

How can I write it in MySQL?

codeforester
  • 39,467
  • 16
  • 112
  • 140
rambokayambo
  • 341
  • 4
  • 10
  • 27

3 Answers3

13

In MySQL you need to use the commands tee & notee:

tee data.txt;
//SQL sentences...
notee;

teedata.txt == spooldata.txt

notee == spool off

IgniteCoders
  • 4,834
  • 3
  • 44
  • 62
Zeta
  • 913
  • 10
  • 24
  • 1
    Thanks for the right answer, I confirm it is like that, and the following MySQL documentation supports it. https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html – Dan Ortega Apr 24 '20 at 00:45
0

For the Oracle SQLPlus spool command, there is no equivalent in the mysql command line client.

To get output from the mysql command line client saved to a file, you can have the operating system redirect the output to a file, rather than to the display.

In Unix, you use the > symbol on the command line. (It seems a bit redundant here to give an example of how to redirect output.)

date > /tmp/foo.txt

That > symbol is basically telling the shell to take what is written to the STDOUT handle and redirect that to the named file (overwriting the file if it exists) if you have privileges.

Q: is set pagesize and set linesize used in mysql when you are trying to generate a report?

A: No. Those are specific to Oracle SQLPlus. I don't know of any equivalent functionality in the mysql command line client. The mysql command line client has some powerful features when its run in interactive mode (e.g. pager and tee), but in non-interactive mode, it's an inadequate replacement for SQLPlus.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • sorry i have another question...is setpagesize and setline size used in mysql when you are trying to generate a report? any assistance would be appreciated – rambokayambo Jun 25 '12 at 21:11
  • @rambokayambo: no, the mysql command line client does not have any equivalent functionality for the SQLPlus-specific `set pagesize` and `set linesize` – spencer7593 Jun 25 '12 at 21:34
-1

If I get what you are asking:

mysql dbname < ${DB_ADMIN_HOME}/Scripts.Datavalidation/Datareportscript.sql \
> ${DB_ADMIN_HOME}/data/Datareport.log

Use redirection.

jim mcnamara
  • 16,005
  • 2
  • 34
  • 51
  • so what command are you suing to spool this information to that directory – rambokayambo Jun 25 '12 at 15:55
  • what in this above syntax stands for the spool command in sqlplus? – rambokayambo Jun 25 '12 at 18:45
  • @rambokayambo: No, it's not at all equivalent to the SQLPlus `spool` command. It's just a way of redirecting all output sent to STDOUT. (The SQLPlus `spool` command can be used multiple times, to start and stop output being written to any number of output files.) – spencer7593 Jun 25 '12 at 21:39