Database Command-Line Tools: MS SQL Server vs. Oracle

SQL Server provides a command-line tool called sqlcmd (2005, 2008) (deprecating osql (up to 2000), see this blog for comparison) to access a database from the command line and execute SQL scripts.

It implements a number of parameters, among them -q and -Q to pass a SQL statement to  be executed. Any results generated by the script are redirected to stdout, and can be written to file using standard redirection mechanism ( >, | ).

The equivalent in the Oracle world is sqlplus (the product name is historically written as SQL*Plus).

Sqlplus does not provide a -q parameter. It rather accepts a (.sql) file name parameter using the notation @path\to\filename.sql (The <<EOF notation to pass a SQL script directly does not work under Windows), and output formating has to be set up in that file. File output is typically configured using the SPOOL command.

I found that the minimum requirement for output formating and spooling is:

set serveroutput on format wrapped;
spool c:\path\to\output.txt
... SQL statements ...
spool off
exit

If you plan to output results using dbms_output.put_line(), you probably need to call dbms_output.enable(1000000);

Advertisement