## 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);

Advertisements

### 4 Responses to Database Command-Line Tools: MS SQL Server vs. Oracle

1. […] This post was mentioned on Twitter by mssqlserver, Raúl Montoya G.. Raúl Montoya G. said: RT @mssqlserver: Database Command-Line Tools: MS SQL Server vs. Oracle « devioblog http://bit.ly/9lz4MK […]

2. […] thus write a stored procedure to generate the data access classes. The stored procedure can then be invoked during the build process to reflect changes in the data […]

3. […] Generation using Oracle SQL*Plus I mentioned the requirements for code generation by calling stored procedures from sqlplus in a previous post, but plus needs more fine-tuning to create nice source code […]

4. Sarit Hod says:

There is a free tool “SQLS*Plus” (on http://www.memfix.com ) which is like SQL*Plus for SQL Server.

Very flexible with data formatting (set lines size, pagesize, etc), variables (var, &, &&), spool, HTML output, etc – ways better than isql, osql or sqlcmd

I downloaded SQLS*Plus from http://www.sqlsplus.com