Creates an XML report of selected tables. This report could be used to generate an HTML documentation of the database (e.g. using the XSLT command). This report can also be generated from within the Database Object Explorer

The resulting XML file can be transformed into a HTML documentation of your database schema. Sample stylesheets can be downloaded from http://www.sql-workbench.net/xstl.html. If you have XSLT stylsheets that you would like to share, please send them to <support@sql-workbench.net>.

	To see table and column comments with an Oracle database, you need to enable remarks reporting for the JDBC driver, otherwise the driver will not return comments.

The command supports the following parameters:

The command WbGrepSource can be used to search in the source code of the specified database objects.

The command basically retrieves the source code for all selected objects and does a simple search on that source code. The source code that is searched is identical to the source code that is displayed in the "Source" tab in the various DbExplorer panels.

The search values can be regular expressions. When searching the source code the specified expression must be found somewhere in the source. The regex is not used to match the entire source.

The command supports the following parameters:

The functionality of the WbGrepSource command is also available through a GUI at Tools → Search in object source

The command WbGrepData can be used to search for occurances of a certain value in all columns of multiple tables. It is the commandline version of the (client side) Search Table Data tab in the DbExplorer. A more detailed description on how the searching is performed is available that chapter.

	To search the data of a table a SELECT * FROM the_table is executed and processed on a row by row basis. Although SQL Workbench/J only keeps one row at a time in memory it is possible that the JDBC drivers caches the full result set in memory. Please see the chapter Common problems for your DBMS to check if the JDBC driver you are using caches result sets.

The command supports the following parameters:

This defines an internal variable which is used for variable substitution during SQL execution. Details can be found in the chapter Variable substitution.

The syntax for defining a variable is: WbVarDef variable=value

The variable definition can also be read from a file. The file should list each variable definition on one line (this is the format of a normal Java properties file). Lines beginning with a # sign are ignored. The syntax is WBVARDEF -file=<filename>

You can also specify a file when starting SQL Workbench/J with the parameter -vardef=filename.ext. When specifying a filename you can also define an encoding for the file using the -encoding switch. The specified file has to be a regular Java properties file. For details see see Reading variables from a file.

This removes an internal variable from the variable list. Details can be found in the chapter Variable substitution.

This list all defined variables from the variable list. Details can be found in the chapter Variable substitution.

The WbConfirm command pauses the execution of the current script and displays a message. You can then choose to stop the script or continue. The message can be supplied as a parameter of the command. If no message is supplied, a default message is displayed.

This command can be used to prevent accidental execution of a script even if confirm updates is not enabled.

This command has no effect in batch mode.

If you want to run a stored procedure that has OUT parameters, you have to use the WbCall command to correctly see the returned value of the parameters.

Consider the following (Oracle) procedure:

CREATE OR REPLACE procedure return_answer(answer OUT integer)
IS
BEGIN
  answer := 42;
END;
/

To call this procedure you need to supply a placeholder indicating that a parameter is needed.

SQL> WbCall return_answer(?);
PARAMETER | VALUE
----------+------
ANSWER    | 42

(1 Row)
Converted procedure call to JDBC syntax: {call return_answer(?)}
Execution time: 0.453s
SQL>

CREATE PROCEDURE ref_cursor_example(pid number, person_result out sys_refcursor, addr_result out sys_refcursor) is
BEGIN
    OPEN person_result FOR
      SELECT *
      FROM person
      WHERE person_id = pid;

    OPEN addr_result FOR
      SELECT a.*
      FROM address a JOIN person p ON a.address_id = p.address_id
      WHERE p.person_id = pid;
END;
/

WbCall ref_cursor_example(42, ?, ?);

CREATE OR REPLACE FUNCTION refcursorfunc()
  RETURNS refcursor
AS
$$
DECLARE
    mycurs refcursor;
 BEGIN
    OPEN mycurs FOR SELECT * FROM PERSON;
    RETURN mycurs;
 END;
$$ LANGUAGE plpgsql;
/

WbCall refcursorfunc();

With the WbInclude command you run SQL scripts without actually loading them into the editor, or call other scripts from within a script. The format of the command is WbInclude -file=filename;. For DBMS other then MS SQL, the command can be abbreviated using the @ sign: @filename; is equivalent to WbInclude -file=filename;. The called script way may also include other scripts. Relative filenames (e.g. as parameters for SQL Workbench/J commands) in the script are always resolved to the directory where the script is located, not the current directory of the application.

The reason for excluding MS SQL is, that when creating stored procedures in MS SQL, the procedure parameters are identified using the @ sign, thus SQL Workbench/J would interpret the lines with the variable definition as the WbInclude command. If you want to use the @ command with MS SQL, you can configure this in your workbench.settings configuration file.

	If the included SQL script contains SELECT queries, the result of those queries will not be displayed in the GUI

The long version of the command accepts additional parameters. When using the long version, the filename needs to be passed as a parameter as well.

Only files up to a certain size will be read into memory. Files exceeding this size will be processes statement by statement. In this case the automatic detection of the alternate delimiter will not work. If your scripts exceed the maximum size and do use the alternate delimiter you will have to use the "long" version so that you can specify the actual delimiter used in your script.

The command supports the following parameters:

Execute my_script.sql

@my_script.sql;

Execute my_script.sql but abort on the first error

wbinclude -file="my_script.sql" -continueOnError=false;

If you manage your stored procedures in Liquibase ChangeLogs, you can use this command to run the necessary SQL directly from the XML file, without the need to copy and paste it into SQL Workbench/J. This is useful when testing and developing stored procedures that are managed by a Liquibase changeLog.

This is NOT a replacement for Liquibase. WbRunLB will only extract SQL statements stored in <sql> or <createProcedure> tags. It will not convert any of the Liquibase tags to "real" SQL. WbRunLB will NOT update the Liquibase log table (DATABASECHANGELOG) nor will it check if the specified changeSet(s) have already been applied to the database. It is merely a convenient way to extract and run SQL statements stored in a Liquibase XML file!

The attribute splitStatements for the sql tag is evaluated. The delimiter used to split the statements follows the usual SQL Workbench/J rules (including the use of the alternate delimiter).

WbRunLB supports the following parameters:

The default fetch size for a connection can be defined in the connection profile. Using the command WbFetchSize you can change the fetch size without changing the connection profile.

The following script changes the default fetch size to 2500 rows and then runs a WbExport command.

WbFetchSize 2500;
WbExport -sourceTable=person -type=text -file=/temp/person.txt;

WbFetchSize will not change the current connection profile.

To send several SQL Statements as a single "batch" to the database server, the two commands WbStartBatch and WbEndBatch can be used. All statements between these two will be sent as a single statement (using executeBatch()) to the server.

Note that not all JDBC drivers support batched statements, and the flexibility what kind of statements can be batched varies between the drivers as well. Most drivers will not accept different types of statements e.g. mixing DELETE and INSERT in the same batch.

To send a group of statements as a single batch, simply use the command WbStartBatch to mark the beginning and WbEndBatch to mark the end. You have to run all statements together either by using "Execute all" or by selecting all statements (including WbStartBatch and WbEndBatch) and then using "Execute selected". The following example sends all INSERT statements as a single batch to the database server:

WbStartBatch;
INSERT INTO person (id, firstname, lastname) VALUES (1, 'Arthur', 'Dent');
INSERT INTO person (id, firstname, lastname) VALUES (2, 'Ford', 'Prefect');
INSERT INTO person (id, firstname, lastname) VALUES (3, 'Zaphod', 'Beeblebrox');
INSERT INTO person (id, firstname, lastname) VALUES (4, 'Tricia', 'McMillian');
WbEndBatch;
COMMIT;

To save the contents of a BLOB or CLOB column into an external file the WbSelectBlob command can be used. Most DBMS support reading of CLOB (character data) columns directly, so depending on your DBMS (and JDBC driver) this command might only be needed for binary data.

The syntax is very similar to the regular SELECT statement, an additional INTO keyword specifies the name of the external file into which the data should be written:

WbSelectBlob blob_column
INTO c:/temp/image.bmp
FROM theTable
WHERE id=42;

Even if you specify more then one column in the column list, SQL Workbench/J will only use the first column. If the SELECT returns more then one row, then one outputfile will be created for each row. Additional files will be created with a counter indicating the row number from the result. In the above example, image.bmp, image_1.bmp, image_3.bmp and so on, would be created.

WbSelectBlob is intended for an ad-hoc retrieval of a single LOB column. If you need to extract the contents of several LOB rows and columns it is recommended to use the WbExport command.

You can also manipulate (save, view, upload) the contents of BLOB columns in a result set. Please refer to BLOB support for details.

Normally SQL Workbench/J prints the results for each statement into the message panel. As this feedback can slow down the execution of large scripts, you can disable the feedback using the WbFeedback command. When WbFeedback OFF is executed, only a summary of the number of executed statements will be displayed, once the script execution has finished. This is the same behaviour as selecting "Consolidate script log" in the options window. The only difference is, that the setting through WbFeedback is temporary and does not affect the global setting.

The SET command is passed on directly to the driver, except for the parameters described in this chapter as they have an equivalent JDBC call which will be executed instead.

Oracle does not have a SQL set command. The SET command that is available in SQL*Plus is a specific SQL*Plus command and will not work with other client software. Most of the SQL*Plus SET commands only make sense with SQL*Plus (e.g. formatting of the results). To be able to run SQL scripts that are intended for Oracle SQL*PLus, any error reported from the SET command when running against an Oracle database will silently be ignored and only logged as a warning.

The following options for the SET command are only available when being connected to an Oracle database.

In the connection profile two options can be specified to define the behaviour when running commands that might change or update the database: a "read only" mode that ignores such commands and a "confirm all" mode, where you need to confirm any statement that might change the database.

These states can temporarily be changed without changing the profile using the WbMode command.

	This changes the mode for all editor tabs, not only for the one where you run the command.

Parameters for the WbMode command are:

The following example will turn on read only mode for the current connection, so that any subsequent statement that updates the database will be ignored

WbMode readonly;

To change the current connection back to the settings from the profile use:

WbMode reset;

The command WbGenerateDrop can be used to generate a SQL script for a table that will drop all foreign keys referencing that table, then a DROP statement for that table and the statements to re-created the foreign keys referencing that table.

This is useful if you need to re-create a table but don't want to manually delete all referencing foreign keys, especially if the DBMS does not support a cascading DROP.

This is also available in the DbExplorer's context menu as "Generate DROP script".

The command supports the following parameters.

If neither -outputFile nor -outputDir is specified, the output is written to the message panel.

WbGenerateScript re-creates the SQL for objects in the database. It is the commandline version of the Generate Script option in the DbExplorer

The command supports the following parameters.

Describe shows the definition of the given table. It can be abbreviated with DESC. The command expects the table name as a parameter. The output of the command will be several result tabs to show the table structure, indexes and triggers (if present). If the "described" object is a view, the message tab will additionally contain the view source (if available).

DESC person;

If you want to show the structure of a table from a different user, you need to prefix the table name with the desired user DESCRIBE otheruser.person;

This command lists all available tables (including views and synonyms). This output is equivalent to the left part of the Database Object Explorer's Table tab.

You can limit the displayed objects by either specifying a wildcard for the names to be retrieved: WbList P% will list all tables or views starting with the letter "P"

The command supports two parameters to specify the tables and objects to be listed. If you want to limit the result by specifying a wildcard for the name and the object type, you have to use the parameter switches:

This command will list all stored procedures available to the current user. The output of this command is equivalent to the Database Explorer's Procedure tab.

You can limit the list by supplying a wildcard search for the name, e.g.:

WbListProcs public.p%

This command will list all stored triggers available to the current user. The output of this command is equivalent to the Database Explorer's Triggers tab (if enabled)

This command will show the source for a single stored procedure (if the current DBMS is supported by SQL Workbench/J). The name of the procedure is given as an argument to the command:

WbProcSource theAnswer

Lists the available catalogs (or databases). It is the same information that is shown in the DbExplorer's "Database" dropdown.

The output of this command depends on the underlying JDBC driver and DBMS. For MS SQL Server this lists the available databases (which then could be changed with the command USE <dbname>)

For Oracle this command returns nothing as Oracle does not implement the concept of catalogs.

This command calls the JDBC driver's getCatalogs() method and will return its result. If on your database system this command does not display a list, it is most likely that your DBMS does not support catalogs (e.g. Oracle) or the driver does not implement this feature.

This command ignores the filter defined for catalogs in the connection profile and always returns all databases.

Lists the available schemas from the current connection. The output of this command depends on the underlying JDBC driver and DBMS. It is the same information that is shown in the DbExplorer's "Schema" dropdown.

This command ignores the filter defined for schemas in the connection profile and always returns all schemas.

With the WbConnect command, the connection for the script that is currently be exected can be changed.

When this command is run in GUI mode, the connection is only changed for the remainder of the script execution. Therefor at least one other statement should be executed together with the WbConnect command. Either by running the complete script of the editor or selecting the WbConnect command together with other statements. Once the script has finished, the connection is closed and the "global" connection (selected in the connect dialog) is active again. This also applies to scripts that are run in batch mode or scripts that are started from within the console using WbInclude.

When this command is entered directly in the commandline of the console mode, the current connection is closed and the new connection is kept open until the application ends, or a new connection is established using WbConnect on the commandline again.

The command supports the following parameters:

If none of the parameters is supplied when running the command, it is assumed that any value after WbConnect is the name of a connection profile, e.g.:

WbConnect production

will connect using the profile name production, and is equivalent to

WbConnect -profile=production

Transforms an XML file via a XSLT stylesheet. This can be used to format XML input files into the correct format for SQL Workbench/J or to transform the output files that are generated by the various SQL Workbench/J commands.

Parameters for the XSLT command:

To run an operating system command use WbSysExec followed by a valid command for your operating system.

To run the program ls the following call can be used:

WbSysExec ls

To run Windows commands that are internal to cmd.exe such as DIR, you must call cmd.exe with the /c switch to make sure cmd.exe is terminated:

WbSysExec cmd /c dir /n

If you need to specify a working directory for the program, or want to specify the commandline arguments individually, a second format is available using the standard SQL Workbench/J parameter handling:

To run an internal Windows command using the second format, use the following syntax:

WbSysExec -program='cmd.exe' -argument='/c' -argument='dir /n' -dir='c:\temp\'

WbSyOpen can be used to open a file with the default application of the operating system.

WbExport -file=c:/temp/person.txt -sourceTable=person -type=text -header=true;
WbSysOpen c:/temp/person.txt;

To turn on support for Oracle's DBMS_OUTPUT package you have to use the (SQL Workbench/J specific) command ENABLEOUT. As an alternative you can also use the SQL*Plus command set serveroutput on. In contrast to SQL*PLus set serveroutput on must be terminated with a semicolon (or the alternate delimiter).

After running ENABLEOUT the DBMS_OUTPUT package is enabled, and any message written with dbms_output.put_line() is displayed in the message panel after executing a SQL statement. It is equivalent to calling the dbms_output.enable() procedure.

You can control the buffer size of the DBMS_OUTPUT package by passing the desired buffer size as a parameter to the ENABLEOUT command: ENABLEOUT 32000;

	Due to a bug in Oracle's JDBC driver, you cannot retrieve columns with the LONG or LONG RAW data type if the DBMS_OUTPUT package is enabled. In order to be able to display these columns support for DBMS_OUTPUT has to be switched off.

To disable the DBMS_OUTPUT package again, use the (SQL Workbench/J specific) command DISABLEOUT. This is equivalent to calling dbms_output.disable() procedure.

Not all configuration parameters are available through the Options Dialog and have to be changed manually in the file workbench.settings. Editing the file requires to close the application. Using WbSetConfig configuration properties can be changed permanently without restarting SQL Workbench/J

If you want to e.g. disable the use of Savepoints in the SQL statements entered interactively, the following command will turn this off for PostgreSQL:

WbSetConfig workbench.db.postgresql.sql.usesavepoint=false

For a list of configuration properties that can be changed, please refer to Advanced configuration options

If you supply only the property key, the current value will be displayed. If no argument is supplied for WbSetConfig all properties are displayed. You can also supply a partial property key. WbSetConfig workbench.db.postgresql will list all PostgreSQL related properties. You can directly edit the properties in the result set.