A freeware/open source enhanced Oracle SQL*Plus© replacement.
Created by Jorgen Bosman 2001-2013
This program is for those of you who like the command-line interface of
but are tired of the limited screen output and editing capabilities.
This program is intended to keep the familiar command-line interface for
rapid typing of
sql statements, but allows you to get a better query result overview and
text editing and command history capabilities.
This program is intended to be largely compatible with Oracle's
SQL*Plus©, but not all
features have been implemented yet, and some may never be implemented...
It also supports connecting to ODBC sources, but not all features are
Installation is very easy, just extract jbsql.exe from the zip file and
place it anywhere you want to.
To run it, you need to have the Oracle 10g client (or higher) installed
on your pc and your Primary Oracle Home
must be pointing to the Oracle 10g home. Alternatively, you can extract
the jbsql.exe to your %ORACLE_HOME%\bin
directory, then you don't have to worry about your Oracle Homes.
To start jbsql, just double-click the
If you get errors like 'symbol xxx not found in oci32.dll', then you
don't have the Oracle 8i (or higher) home as your primary Oracle home. Use the
Oracle Home Selector to change it.
If you get "ORA-24315: illegal attribute type" every time you
get another Oracle error, then you don't have the Oracle 10g (or higher) home
as your primary Oracle home. Use the Oracle Home Selector to change it.
If you only want to connect to an ODBC source, then you need to have an
Oracle client installed, however, you can install the Oracle instantclient with OCI support. You can copy the instantclient files to the same directory as jbsql and it should work.
3 Logging on
When you start jbsql, you will be prompted to
log on to a database:
Just enter your database user name, password, database and datasource and click "OK".
This should be sufficient for most connections.
For Datasource ORACLE
If you want access as SYSDBA or SYSOPER, select it from the dropdown
list instead of NORMAL.
(Note that startup, shutdown and recover database is not supported!)
Your last 10 usernames and databases are remembered. You can quickly
reuse them by selecting them from the drop-down list.
You can also quickly access a database that's not in your tnsnames.ora by filling in the hostname:port:SID
in the database field.
This method only works for tcp/ip connections.
You can also use / as username and nothing as a password to connect
using your NT username.
If you leave the database empty, then you are connected to your local
database or to the database
where your LOCAL environment/registry variable is pointing to.
You can also give the connectstring as a
command-line parameter. This way you can create a shortcut to your favourite database.
e.g.: jbsql.exe scott/tiger@dbserver
You can also specify an command to run when you are logged in, for
example to run a sql script. Just specify the command on the commandline just after the connectstring.
e.g.: jbsql.exe scott/tiger@db @c:\temp\script.sql
or jbsql.exe scott/tiger "select 'Instance '||instance_name||' on host '||host_name
instance from v$instance;"
For Datasource ODBC
You can connect to any ODBC 3.0 compatible datasource.
First you have to configure an ODBC datasource with
the Windows ODBC datasource administrator tool.
If you have x64 windows and x86 jbsql, then
you need to configure your datasource with
You can connect using the command line parameter with user/password@odbc:datasource:
e.g. Jbsql.exe scott/tiger@odbc:db
Some datasources don’t require a username or
password. In that case you can use /@odbc:db
You can enter commands at the "SQL>" prompt. Oracle/ODBC
commands should be terminated by a ; or / character.
JBSql commands must not be terminated, but it's no
error if they are terminated.
When you execute a query, the query results are displayed at the bottom
in a listview. You can resize columns and scroll
through the records.
4.1 Oracle commands
You can execute all known standard Oracle DML and DDL commands, such as
select, update, delete, insert, create, drop, alter ...
For most DDL commands, jbsql will tell you
what it's doing and what it has done. All Oracle commands should be terminated
the ; or / character.
For an overview of all sql statements, see http://otn.oracle.com (sql reference)
4.1 ODBC commands
You can execute all known standard ODBC DML and DDL commands, such as
select, update, delete, insert, create, drop, alter ...
For most DDL commands, jbsql will tell you
what it's doing and what it has done. All ODBC commands should be terminated
the ; or / character. The exact syntax of the statements and the kind of
statements depends on the datasource.
4.2 JBSql Commands
exec[ute] stored_procedure (Oracle specific);
The execute command allows
you to execute a stored procedure/function/package.
This is identical to BEGIN stored_procedure; END;
ls [-i|-v|-t type] [name]
Shows a list of all user
-i : lists all invalid objects (Oracle specific)
-v : lists all valid objects (Oracle specific)
-t type : lists all objects of type t (object_type
like TYPE%) (Oracle specific)
name : list all objects with object_name like NAME
example: dir -i -t pa pck%
lists all invalid packages starting with PCK
Repeats the previous
Starts an external editor
containing the previous command. Or when you specify a file name, it starts the
editor with the specified file.
Shows a description of the
specified database object (tables, views, procedures, ...) (only tables and
views for ODBC)
show err[or] [object_type
object_name] (Oracle specific)
Shows the PL/SQL errors in the
last created/compiled object, or the errors for the specified object.
show par[ameter] name (Oracle specific)
Shows the initialization
parameters that contain name
@script [parameter1] [parameter2]
Runs the commands inside
the specified sql script. If no extension is given, .sql is assumed.
To specify a script inside a directory that contains spaces, surround the
script with " "
If the scripts accepts parameters (&1, &2, ...), you can specify them
after the script name.
@@script [parameter1] [parameter2]
Runs the commands inside
the specified sql script. If no extension is given, .sql is assumed.
To specify a script inside a directory that contains spaces, surround the
script with " "
If the scripts accepts parameters (&1, &2, ...), you can specify them
after the script name.
The double @@ looks for sql scripts inside the same directory as the calling
script. So it should be
used from within another script.
Writes all following sql
output to the specified file
off, turns off spooling and
closes the file.
set option value|on|off [option value|on|off
Gives a value to an option
(see Options for a list of options)
You can specify multiple options at once and options may be abbreviated. If the
starting characters match exactly one option, then that option is set.
set without parameters shows an overview of current option values
Gives a value to a
variable. This variable can be used inside scripts by using &variable or
If you do not specify =value, then the current value is shown.
Removes the specified
variable [num[ber]|char|date] [for[mat] format] [def[ault]
default] [prompt text|nopr[ompt]]
Prompts the user to enter a
value for a variable.
Refer to the following list
for a description of each term or clause:
Represents the name of the variable in which
you wish to store a value.
Makes the datatype of variable the
datatype NUMBER. If the reply does not match the datatype, ACCEPT gives an
error message and prompts again.
Makes the datatype of variable the
Makes reply a valid DATE format. If the reply
is not a valid DATE format, ACCEPT gives an error message and prompts again.
Specifies the input format for the reply. If the
reply does not match the specified format, ACCEPT gives an error message and
prompts again for a reply. The format element must be a text constant such as
A10 or 9.999. See the the TO_NUMBER or TO_DATE
commands for a complete list of format elements.
Sets the default value if a reply is not given.
The reply must be in the specified format if defined.
Displays text on-screen before accepting
the value of variable from the user.
Skips a line and waits for input without displaying
Hides the text that you type behind '*'
username/password[@[[oracle|odbc]database] [as sysdba|sysoper|normal]
Creates a connection to a
database with the specified credentials. If you do not specify a password, then
you will be prompted for a password. For the database, you can also specify host:port:SID or host:SID or host:port for quick connections to a database. You can also
preprend odbc or oracle to
the database to connect to an odbc or oracle datasource.
Disconnects the current
session from the database. You cannot execute any sql commands anymore until
you use the connect command again.
Prints the given text to
the console window.
tune (Oracle specific)
Puts the last executed command
inside the sql analyzer window.
You can set various options
to control the behaviour of jbsql.
Options are persistent, you don't have to enter them again when you start jbsql.
All options can be
configured with a dialog box and via the 'set' command.
The dialog box can be opened via the Tools->Options menu.
The list on the left shows
all possible options, when you select an option, the current value is displayed
on the right hand side, along with a description of the option. For boolean options (true or false), a checkbox is displayed
whether or not you want to show this option on the main window's toolbar or not
for easy access to this option.
If set, pressing Enter
inside a sql statement inserts a newline. If not set, then the statement is
Specifies how many records
are fetched at once. If the amount of memory that's needed to hold the records exeeds the maxmem option, then
the arraysize is automatically reduced for the
Specifies whether or not
autocomplete should be active when you press TAB.
autotrace (Oracle specific)
is enabled, the execution plan is displayed in the sql analyzer window before
each statement is executed and statistics about the statement are displayed
after the query in the console.
the background color for the console. This setting is remembered for each
connection string, so this allows you to configure for example a red background
color for the production database, a yellow one for the test database, …
you use the set command to set this, you have to
specify the RGB value as: rxxxgxxxbxxx, for example:
r255g255b255 for white
Specifies how many previous
commands are kept in memory.
Specifies the startup
directory for jbsql. This makes it easier to type the
location of sql scripts, you only have to type the location relative to the
Specifies what editor to
use with the 'edit' command.
Specifies what filename to
create with the 'edit' command
fastresults (Oracle specific)
you set it to x, then it will fetch the first x records one by one and show
them in the listview, the rest is fetched with the
configured arraysize. This option is disabled when
you set it to 0.
directory where your favourite scripts are stored. If
empty, then it defaults to %APPDIR%\jbsql\favourites
set, then a result is shown each time a statement is successfully processed.
font to use in the command window. Only fixed width fonts are possible
here.(not settable via command-line)
font to use in the results listview. All fonts are
possible here. (not settable via command-line)
Should headings be printed
when output to screen is enabled or spooling is on?
Specifies whether query
output should appear in the listview table at the
bottom or not.
Specifies how many
characters are read from LONG, LONG RAW, BLOB, CLOB and BFILE columns.
Specifies the maximum
number of nested scripts. This is to prevent recursive scripts.
Specifies how many lines
are buffered in the console window.
Specifies the maximum
memory usage for queries, if arraysize*record_size > maxmem then the arraysize is automatically adjusted to fit into the maximum
Saves the command history
to a file when you exit jbsql and reads them back in
when you start jbsql
Scan scripts for &
characters, which denotes variables and ask for variable values.
Is output from queries to
the console on?
serveroutput (Oracle specific)
Displays output generated
by dbms_output after completion of a sql statement.
Play a sound when long
running queries are finished.
Specified the sound file to
Specifies the time (in
seconds) after which a sound should be played..
the text to use as console prompt.
following variables are replaced:
Connection identifier used to make connection, where available
Specifies the editor used by the EDIT command
Current version of the installed Oracle Database
Full release number of the installed Oracle Database (11g+)
Privilege level of the current connection
Full release number of JBSql
User name used to make connection
any 'defined' variable (see define command)
If enabled, displays
elapsed time information for each sql statement.
enabled, trims end of lines in spoolfile
If enabled, displays old and new
values for replaced && variables in a script.
If enabled, check regularly
for a new jbsql version on startup.
6 Bind Variables
You can use bind variables inside a sql statement
(prefixed with a : ). Jbsql will prompt you to
specify values for each bind variable in the statement. You can then specify
the value and datatype of the variable.
When you change the value or datatype, then you need
to click the apply button. When you are done, click the OK button, then the
statement will continue to process.
You can also call this screen to view all existing
bind variables from the Tools menu. Then you can also add or delete bind
For CLOB/BLOB/BFILE variables, 2 extra buttons appear
to load a file to the CLOB/BLOB or to save the CLOB/BLOB/BFILE to a file. This
allows you to save the content of a lob column to a file: exec select lob_column into:lob from … and
then open tools->bind variables screen and then save the lob variable to a
To load a file to a lob column: first make a new lob bind variable with the
tools->Bind variables and then execute: insert into tablex
(lob_column) values (:lob);
You can also declare a variable with the var command:
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR |
CHAR(n) | VARCHAR2(n) | DATE | CLOB | BLOB | BFILE | BINARY_FLOAT | BINARY_DOUBLE
VAR without arguments shows all known bind variables
and their datatype.
You can also view the value of a bind variable with
the print command:
PRINT without argument shows all variables
7 Extra features
7.1 Command recall
You can press 'arrow up'
inside the console to get the previous commands back. You can use 'arrow down'
to scroll between the previous commands. The previous commands are also
persistent between jbsql sessions if the 'savehistory' option is enabled.
7.2 Inline editing
While you are typing a
command of when you have recalled a command, you can use the 'arrow left',
'arrow right', 'home','end', 'Page Up and 'Page down'
key to navigate the cursor to another position. When you're not at the end of a
command and you have a multi-line command, then you can also use the 'arrow up'
and 'arrow down' key to navigate to a previous line or next line inside the
'Home' moves the cursor to
the beginning of the current line of the current command.
'End' moves the cursor to the end of the current line of the current command.
'Page Up' moves the cursor to the beginning of the command.
'Page Down' moves the cursor to the end of the command.
'Ctrl-Left' moves the cursor to the start of the previous word.
'Ctrl-Right' moves the cursor to the start of the next word.
When you're inside a
command, you can insert text or use the 'del' and 'backspace' key to delete
When the 'allow_newline' option is on, then you can insert a new line
inside the command by pressing the 'enter' key. The command is only executed
when the cursor at the end of the command.
When the 'allow_newline' option is off, then pressing 'enter' will
always execute the command, no matter where the cursor is. You can insert a new
line in the command by pressing 'Shift-Enter'.
You can also position the
cursor by clicking with the mouse at the desired position inside the current
When you are typing a sql statement,
you can press the 'tab' key to find a list of closest matches to the word that
you are typing. If more that one match is found, a popup list is displayed with
all possible choices. If only one match is found, then the word is
automatically completed without bringing up the popup list.
The autocompletion is
context sensitive, this means that is knows for example that after the 'from'
word, it is likely that you want to type a table name or a view name, so it
searches a list of table and view names. It does not do complete sql parsing,
so it does not correlate column names after the 'where' word with table names
after the 'from' word unless you give those tables an alias and also specify
the alias after the where keyword. Just try it out and see what happens, most
of the time, the correct list is shown, but sometimes it may not be what you
Autocomplete also works for
specifying script names after the @ command. It completes directory and file
JBSql has got (just like SQL*Plus),
rectangular selection. This means that you can select text inside the console,
not line by line, but in a rectangular fashion. It also has the same
quick-paste feature as SQL*Plus. This means that when you press the right mouse
button after you have selected a piece of text and also while the left button
is still down, the selected text is pasted at the current cursor location.
You can copy selected text
from the console using Ctrl-C or Ctrl-Insert and paste text inside the console window
by using Ctrl-V, or Shift-Insert. More copy/paste
options are available on a popup menu when you right click on the results.
When you right-click on the results window,
there is a submenu called Databrowser. In this
menu you can quickly show the child or parent records of the selected records.
It will show you a list of child or parent tables (defined by the foreign key
constraints) to choose from. This feature only works if you have executed a
simple query like select * from table; It does not work if you have
joins/unions or if you do not select the columns that are
needed for the foreign keys. You can also show parent records with ctrl-alt-up
and child records with ctrl-alt-down.
7.6 Quick Exit
You can quickly exit jbsql by pressing Ctrl-D
7.7 Interruptable statements
You can cancel a running
statement by pressing Ctrl-X or selecting 'Cancel' from the Query menu. It may
take a while before the statement is cancelled.
While a statement is
executing, the rest of the application still responds to user input. For
example, you can still select text or change an option while a query is
executing. The same yields while the 'edit' command is running or when a script
7.9 User friendly feedback
For most sql statements, jbsql will tell
you what it's doing: parsing, executing, fetching, ...
For most create/alter
statements, it will tell you which table/index/... it is creating and has
created. This is most useful in scripts that contain many create/alter or drop
statements. This way you can easily track errors in your scripts.
You can add the last
executed statement in your favourite scripts. Jbsql will ask you for the file name to save the statement
in. You can also create subdirectories to better organize your favourites.
All *.sql files found will
be added to the favourites menu. Subdirectories will
be added as submenus. When you select a favourite
script from the menu, it will be executed.
The edit favourites will open windows explorer in your favourites folder. There you can edit all scripts and
rearrange the folder structure.
7.10 Object support
JBSql supports querying object types,
including ref, anydata and xmltype
types. The maximum length that is fetched is controlled by the “long” setting
for lobs. Fetching object types is record by record, not by array fetch.
(limitation in OCI)
8 SQL Analyzer (Oracle specific)
The SQL Analyzer helps you
to tune your sql statements. At the top, you can enter your sql statement.
is on or if you enter the tune command, the last executed command is put at the
The hint listbox contains most known hints. When you select a hint
from the list, a short explanation is displayed at the bottom.
Depending on the hint, the
table and index list is displayed. From these lists, you can select the table
name(s) and index name(s). When you have selected a hint/table/index, the sql
statement is updated with the new hint. Some hints require one or more table
names as parameter and some hints also require one or more index names.
Remember that if you have table aliases in your sql statement, then you have to
use the same aliases in your hint instead of the table name.
When you press the 'Explain
plan' button, the sql statement is analyzed and the execution plan is displayed
in the tree in the middle. Remember that if there's a syntax error inside the
hint, Oracle doesn't complain about it.
When you press the
'Execute' button, the sql statement is copied to the console and the statement
is executed. This way you can see how fast the statement executes.
9 Schema Browser (Oracle specific)
With this tool, you can see
the DDL create statements for most database objects.
The top three listboxes let you choose the database object. First select
the desired schema name, then the object type and then the object name. When
you change the schema, the other listboxes are updated
with data from the chosen schema. When you change the object type, the object
name list is updated with all the object names from the chosen schema and objec type. When you have selected an object name, the DDL
create statement is shown in the text window below. When you have selected a
table, then it also shows the DDL create statement for the indexes and
constraints on that table.
You can export the
currently selected data to a file. This feature is available from the file
10.1 CSV format
When you export to CSV
(Comma Separated Values) format, the delimiter is a ; character and all fields
are enclosed by a " character. If there is a " inside the exported
data, then to consecutive " characters are put in the csv file. All fields
are exported as text and as they are shown in the list view.
10.2 XML format
You can also export the
selected data to a XML file. The format of the xml file is as follows:
10.3 SQL format
You can also export the
selected data as a SQL insert script. This will generate sql insert statements
for each row. This generated script may not always work, for example if you have
a complex query. It will probably work best if you have just done select * from
- 0000064:[Enhancement Request] Make export to XML in real UTF-8 (jorgen.bosman) - resolved.
- 0000068: [Enhancement Request] Run without Oracle
client to connect to ODBC datasource (jorgen.bosman) - resolved.
- 0000032: [Performance] Improve speed of export (jorgen.bosman) - resolved.
- 0000065: [Performance] Increased performance of retrieving query data
by factor 6 (jorgen.bosman) - resolved.
- 0000066: [Enhancement Request] Auto-apply bind
variable settings when clicking different variable in list (jorgen.bosman) - resolved.
- 0000063: [Crash] Crash when connectstring is like host:port:sid and hostname>10 chars (jorgen.bosman) - resolved.
- 0000062: [Enhancement Request] Set background color per environment (jorgen.bosman) - resolved.
- 0000059: [Bugs] Cursor position wrong when executing
script which accepts input (jorgen.bosman) - resolved.
- 0000060: [Enhancement Request] Check for update using proxy (jorgen.bosman) - resolved.
- 0000061: [Enhancement Request] Auto update (jorgen.bosman) - resolved.
- 0000051: [Bugs] File
access error when closing multiple jbsql
sessions at once (jorgen.bosman) - resolved.
- 0000042: [Bugs] Save
button missing in bind variables window (jorgen.bosman) - resolved.
- 0000049: [Bugs] When
using direct connect and having a slash in ConnectionString
- there is no history (jorgen.bosman) - resolved.
- 0000050: [Bugs] When
running JbSQL with a different extension, the
"New Window" Button doesn't work (jorgen.bosman) - resolved.
- 0000048: [Crash] JB/SQL
crashes when using feature copy entire line (jorgen.bosman) - resolved.
- 0000045: [Crash] Crash
when selecting xmltype/lob>64K after set long
> 64K (jorgen.bosman) - resolved.
- 0000046: [Bugs] Output
to screen with carriage return produces empty line (jorgen.bosman) - resolved.
- 0000044: [Bugs] TRIMSPOOL
option doesn't work from the @ command (jorgen.bosman) - resolved.
- 0000038: [Crash] JbSQL crashes every time I execute any command (jorgen.bosman) - resolved.
- 0000035: [Performance] scrolling
through previous commands is slow (jorgen.bosman) - resolved.
- 0000034: [Bugs] Click
with mouse button sometimes does not clear selection (jorgen.bosman) - resolved.
- 0000037: [Bugs] After
setting an option in a script, the option can't be set again afterwards (jorgen.bosman) - resolved.
- 0000030: [Enhancement Request] Choose font for list view (jorgen.bosman) - closed.
- 0000029: [Bugs] error on the DESC command, when switching schemas (jorgen.bosman) - closed.
- 0000016: [Enhancement Request] SQL prompt variables (jorgen.bosman) - closed.
- 0000021: [Enhancement Request] SQL PLUS trimspool
- 0000018: [Enhancement Request] Implement bind variable support (jorgen.bosman) - closed.
- 0000019: [Enhancement Request] Feedback for MERGE statement (jorgen.bosman) - closed.
- 0000013: [Enhancement Request] assign keybord shotcut keys to parent/child record navigation (jorgen.bosman) - closed.
- 0000028: [Enhancement Request] CTRL-A in listview
- 0000020: [Enhancement Request] Add crashhandling
- 0000027: [Enhancement Request] Allow logon dialog to be closed with ESC
- 0000015: [Enhancement Request] Handle password expired message (jorgen.bosman) - closed.
- 0000017: [Bugs] double SQL prompt after connect statement via prompt (jorgen.bosman) - closed.
- 0000026: [Enhancement Request] Choose font for command window (jorgen.bosman) - closed.
- 0000023: [Enhancement Request] Add support for BINARY_FLOAT and
BINARY_DOUBLE datatypes (jorgen.bosman)
- 0000024: [Enhancement Request] Add support for NCLOB (jorgen.bosman) - closed.
- 0000025: [Enhancement Request] Add support for Object types (jorgen.bosman) - closed.
- 0000022: [Bugs] Bad font charset in the console window, national
characters are displayed not properly (jorgen.bosman) - closed.
- 0000003: [Enhancement Request] Add select support for types (jorgen.bosman) - closed.
[Enhancement Request] Add data browser (jorgen.bosman) - resolved.
[Enhancement Request] show OCI version (jorgen.bosman) - resolved.
- 0000011: [Bugs]
Connecting with odbc without username shows
disconnected in title bar (jorgen.bosman)
- 0000010: [Bugs]
Reconnect does not reinitialize dbms_output (jorgen.bosman) - resolved.
- 0000009: [Bugs]
Login box does not show ORACLE as default database on first use (jorgen.bosman) - resolved.
- 0000007: [Crash]
crash in autocomplete (jorgen.bosman)
[Enhancement Request] ODBC connectivity (jorgen.bosman) - resolved.
- 0000005: [Bugs]
Paste in beginning of a large sql statement
scrolls down to the end of the statement (jorgen.bosman) - resolved.
[Enhancement Request] drop down up key on first element goest to bottom of list (jorgen.bosman) - resolved.
- Fixed some
- Made Execute
from within sql analyzer faster
- 0000001: [Crash] Crash
when executing script
up previous/next command
results in listview as they are fetched
- Added new “fastresults”,
when you set it to x, then it will fetch the first x records one by one
and show them in the listview, the rest is
fetched with the configured arraysize.
- Finally implemented favourites.
- Show current SID in title window
- Escape also clears current command (next to
- Added reconnect (ctrl-e)
- Ctrl-space can also be used for autocompletion
- Autocomplete also works for showing table columns
before the from (in the select), but only when there is a from and tables
- Added check for updates
- Added Help menu
- Autocomplete can now also be used for arguments
in procedures/functions/packages: press tab at the opening bracket (
- Desc command only shows data_length for varchar2/char and raw
- Speeded up paste, output to screen and serveroutput
- Window no longer flashes for each statement in a
script but only when the script completes.
- Added timing also when a script fails.
- Added total script execution time.
- All settings are now stored in %APPDATA%\jbsql and should be migrated automatically on first
startup. If they are not, then you can copy the files manually to the new
location. (jbsql.his and jbsql_history_*.txt)
- Added more copy/paste options in the right-click
popup menu on the listview
V22.214.171.124 beta9 02/11/2008:
- Fixed bug: crash w/hen cancelling query.
- Changed describe output of
- Added window flashing when query is complete and
window does not have focus
- Fixed bug: spooling to file and running an
anonymous plsql block caused statements like
'parsing statement' to be put in the spool file.
- Added comments to describe output
- Fixed bug: export to csv now puts newline after
- Fixed bug: "explain;" caused jbsql to crash
- Fixed bug: Paste of "é" for example did
not work correctly.
- jbsql now keeps a history per
database. It now creates a file jbsql_history_<database>.txt
in your documents and settings directory.
- Fixed bug: Paste of multiple command could
sometimes lead to corrupt commands.
- Fixed bug: record counter did not show correct
number while fetching.
- Added the -i, -v, -t
and name options to the dir and ls commands.
- Added table and view comments to the dir and ls command.
- Added Copy button on the SQL analyzer window
(copy plan to clipboard)
- Failed logon with command-line parameters now
shows the used parameters in the logon box.
- autotrace should now display correct statistics
on all database versions.
- Fixed bug: Typing when the cursor is not visible
now scolls to the cursor position
- Ctrl-x when typing a command now clears the
- Lines selected in list view stay visible when
list becomes inactive
- Added "Copy this column" to the contect menu on the list view.
- Notification of plsql
errors is now in capitals to make it a little bit more clear
- Suppor for INTERVAL types
- Improved SQL analyzer: more info in explained
plan, CTRL-A selects all text, unlimited text input
- Should run under NT again
- Fixed bug#3: describe command (desc without parameters did some strange things)
- Fixed bug#4: after export of the results, the
spreadsheet becomes corrupt
- Fixed bug#5: sql with
tabs looses some data in the main screen
- Fixed bug#6: Wrong table name returned when
creating a table in other schema
- Fixed bug#7: Newlines in export
- Implemented enhancement request #9: link to files
- Fixed bug#10: statement completion for packages
- Fixed bug#15: crash on show param
- Fixed bug#16: error when cancel multiple line
- Fixed bug#17: % is removed when pressing r
- Added /nolog
- Command history file is now also stored in your
documents instead of in the windows folder.
- Fixed bug#18: Cancelling
queries with CLOB data
- Improved speed/memory usage
when fetching LOB values
- Fixed enh#21: Provide the edit command at startup
- Fixed bug#22: No previous command when using last
query from history log
- Fixed bug#23: result screen doesn't refresh
- Fixed small resizing problem with sql analyzer
- Export in csv format now also exports a heading
with column names
- Added possibility to play a sound after a long
running query has finished
- Added the 'sound', 'soundfile'
and 'soundtime' options to support this
- Added the 'history' option. Enabling this will
keep a history of all executed statements. It logs all statements in the
jbsql_history.txt file in your documents and settings directory.
- Fixed bug: 'tune' without previous command would
- Fixed bug: 'exit' at the end of a script without
newline whould crash
- Fixed bug: spool off without first spooling
corrupted screen output
- Made SQLAnalyzer window
resizable + added a splitter window
- Fixed bug: Clicking the mouse while running a
command could cause the cursor to position incorrectly
- Added support for the 'WITH' statement
- Fixed bug: The 'r' command didn't insert newline
- Fixed bug: The ordering of the show err command
was sometimes incorrect.
- Fixed bug: Pressing TAB after a '.' character now
also works after a newline and after more than one occurance.
- Pressing TAB after a '.' character now also shows
a list of tables if the word before the '.' is a schema name.
- Pressing TAB after select now also searches for
- Fixed bug: Sometimes the CTRL key was remembered
incorrectly, this caused left/right keys to jump words instead of
- Fixed bug: Opening a new window caused the first
command the be 'AS NORMAL' followed by the typed text.
- Fixed bug: exit command did not work inside a
- Fixed bug: New window did not work when connected
as sysdba or sysoper.
- Specifying @"script.sql"
on the commandline also works now.
- Fixed bug: Paste of % character works again.
- Improved large package parsing speed.
- dbms_output is retrieved after a sql error occured.
- no longer locks the plan_table
after tuning a query.
- Added possibility to execute a command from the commandline, just enter the desired command after the connectstring on the commandline.
eg: jbsql.exe scott/tiger@db @c:\temp\script.sql
or jbsql.exe scott/tiger "select 'Instance
'||instance_name||' on host '||host_name instance from v$instance;"
- The 'edit', 'tune', '/' and 'r' commands now work
on the last known real sql statement. For
example, they do not work on a describe statement, but they do work on a
- You can now use the mouse to position the cursor
in the current command.
- 'Home' and 'End' keys now go to the start/end of
the current line within the command.
- 'Page Up' now goes the the
start of the current command.
- 'Page Down' now goes to the end of the current
- You can now enter a newline within a command by
- 'Ctrl-Right' and 'Ctrl-Left' now moves to the
- Scrolling left or right with the cursor now 'jump
scrolls' several characters at once.
- Fixed text positioning bug when you scrolled up
with the cursor and started typing.
- Fixed bug. You could not enter a
select/insert/update/delete with a hint and a newline just after the last
- Fixed bug. ';' is now ignored after '@' or
- Fixed crash with schema browser when more than
1000 objects of the same type were present in a schema.
- Added support for new timestamp datatypes
- Choosing 'new window' now first saves the command
history. This means that you can use the commands from the previous
session in your new session.
- Added support for 'show par[ameter]
- Added support for 'show err[or] [object_type object_name]'
- Added shortcut key Ctrl-N to reconnect
- Fixed redraw bug when pasting a command that
changed some option on the toolbar.
- Autocompletion now also works with exec[ute] and start commands
- Autocomplete now recognizes column names and
procedure names when typing TAB after a .
character (still experimental)
- Fixed a bug in running scripts that contain /
- Slightly improved script running speed
- Improved schema browser speed by using array
- Order by and group by now show columns when
pressing the tab key
- The export function now appends a file extention when you did not type an extention
- Fixed copy/paste problems when other windows were
open (sql analyzer or schema browser)
- Added export to csv, xml or sql
file, available from the 'File' menu. It exports the currently selected
- You can now also abort a running script.
- You can now copy data from the list view at the
bottom. When you click the right mouse button, you will get a popup menu
from which you can choose to copy the entire line or just the first
- Added some keyboard shortcuts
- Hints for table names containing a $, now work
- Fixed paste problem with strings beginning with #
- Added Edit menu
- Ctrl-C no longer cancels a query, instead use
- Improved speed of command-parsing routines
- Fixed bug: Running scripts without valid last
command in the file didn't execute the last line and caused jbsql to behave weird.
- Fixed bug: Running a plsql
block or a stored procedure that raised NO_DATA_FOUND exception caused jbsql to display an OCI error.
- When you are typing a new command
or changing a previous command, and you accidentaly
press arrow up, then the current command is remembered. You can retrieve
the modified command by pressing arrow down button all the way to the last
- Fixed bug: Starting jbsql
with connectstring as command-line parameter
without the password caused jbsql to hang
- Increased length of remembered usernames and
databases, this may cause your lists at the logon dialog box to look
- Fixed weird behaviour
when executing empty scripts
- Listboxes no longer show a big gray
block when the schema browser window is resized.
- Quick paste multiple commands at once now works
- Fixed recent introduced bug that prevented
scripts that didn't have an ending newline from running.
- The edit command now appends .sql
to the filename when no extension has been given and the file does not
- set serveroutput on now
does an implicit dbms_output.enable(100000)
- The listboxes in the
schema browser can now be used in WindowsNT/2000
too, not only WindowsXP.
- Fixed problem with & and && variables
being on a 1000 character boundary in scripts.
- Improved screen output speed when drawing the
lines under the column headers.
- Added new feature: Schema Browser
This new tool is available under the tools menu. It allows you to view the
definition of most database objects. In fact, it shows the DDL create
statement for the object. This tool isn't finished yet, but it should give
you an idea of what it can do.
- Improved & and && compatibility with
SQL*Plus. & only asks for the variable is it's not previously defined.
&& doies the same thing, but does an
implicit define of the variable so that it can be reused. (remember that
defined variables inside a script are defined only during the duration of
the script, this is different than SQL*Plus)
- No longer gives an error on 'column x format y'
commands, but doesn't do anything with it...
- Added support for the 'rollback to savepoint x' command.
- Added support for the 'edit filename' command.
- Fixed hard coded 'afiedt.buf'
- Fixed 'show error' on objects created with owner.object_name
- The working directory (directory option) is
passed on to the external editor with the 'edit' command
- Copy-Paste now also works with Ctrl-Insert and
- When a command starts with #, then the #
character is stripped from the command.
- Fixed weird disappearing console/toolbar window
- Because of this, options set inside a script are
only set locally to the running script. When the script ends, the original
settings are restored.
- Fixed bug when running scripts that asks for
variables. Sometimes jbsql would hang when
leaving the main window.
- When you choose to reconnect using the logon
dialog, the currently typed statement is cleared.
- Tune and autotrace now
put sql command with newlines correctly into the
sql analyzer window
- Fixed memory leak
- Added debug option, this will create a jbsql.log
file with debugging information in it.
- Autocomplete for files in current directory now
- Fixed script nesting directory problem
- Enhanced the set command, you can now set
multiple options at once by using 'set option value option value ...'
- Options can now be abbreviated, for example 'set feedb on' will turn feedback on.
- Better feedback to user when creating or dropping
- create [or replace] type [body] support.
- connect /@database now doesn
ask password anymore
- Output buffers are no longer limited to 5000
characters, but are now dynamic. This allows you to paste sql statements with lines longer than 5000 characters
- Username and database on the logon window can now
be longer than the width of the field.
- Fixed set scan off bug
- Added SYSDBA/SYSOPER/NORMAL connect modes, also changed
the connect command to:
connect username/password[@database] [as sysdba|sysoper|normal]
- Added quick connect using host:port:SID or host:SID
- connect command without password asks for
- Added support for the set constraint[s], set role
and set transaction commands
- Added this documentation
- Errors in sql
statements in the SQL Analyzer are now shown in the tree instead of in a
- Fixed copy-paste bug from SQL Analyzer to console
- The sql statement
inside the SQL Analyzer is now seen as a single sql
statement in the console when the Execute button is pressed
- Abbreviated the connect command to conn[ect]
- Abbreviated the disconnect command to
- You can now pass arguments to scripts.
Use &&1, &&2, ... inside the script to access them
- You can specify a script withing
a directory that contains spaces by surrounding it with quotes:
@"c:\program files\jbsql\tst.sql" arg1 arg2
- Autocomplete now also works with the spool
command and when quotes are used to surround a filename
- Added missing jbsql.manifest
file to zip file to allow you to compile it
- Changed output of 'show errors' a little bit to
improve the screen appearance.
- Added 'tune' command to put the previous command
into the sql analyzer
- Fixed ORA-01406 when fetching of LONG columns
that are larger than the specified long size.
- The sql analyzer now
does nothing when a statement is active in the console.
Otherwise it would produce unpredictable results.
- Added SQL Analyzer tool, available from the
'Tools' menu. With this new tool, you can view execution plans for your
queries and add hints to the query. You can select most hints from a listbox. For hints that require table and/or index names,
listboxes will appear when necessary. In these listboxes you can select one or more tables/indexes
depending on the hint. You can also exeute the
tuned query with a click on a button to see the actual processing time.
- Added 'autotrace'
option (only on and off). This option will automatically pop-up the SQL
Analyzer window and show the query plan for the last executed query. It
will also show session statistics in the console.
- Removed most 'Statement processed' and 'Executing
statement' messages and replaced them with more meaningful messages.
- Fixed some problems with accept inside scripts
- Fixed wrong error position due to new verbose
- Fixed problem with describe from a synonyn when the synonym has a different name than the
actual object (thanks to Dieter Van Baarle)
- Added autocomplete when entering a script
filename. Pressing <TAB> will expand directory names and filenames
and show a list of matching filenames if more than one match is found.
- Reworked the screen printing in
order to clean up the spaghetti code. This may result in strange
behaving screen output. Please report this to me and describe how to
reproduce the problem! One possible symptom is that a line which should
have been on the next line, is appended to the previous line.
- Fixed screen refresh problem
- When the 'screen' option is on, the headers are
no longer printed when there are no records found.
- When running a script, the 'screen' option is
turned on during the duration of the script.
- Added ROWID support
- Added toolbar buttons. Also
configurable via options dialog.
- Added New Window menu item. This opens a new jbsql windows with the same connection.
- Added Commit, Rollback and Repeat to menu and
- Added Windows XP visual styles
- You can now select text with the mouse that's
beyond the visible window.
- & and && support in scripts (not yet
on the console)
- Added 'scan' option. set scan off disabled the
& and && scanning
- Added 'verify' option. set verify off disables
printing old and new values when & and && have been replaced
- Added 'exec[ute]'
- Added more verbose statement/query progress
- Query can now be cancelled when no records have
been fetched yet
- 'define variable=value' support
- 'undef variable'
- 'accept' command support
- Oracle 8.0.x client desupported,
only 8i and above is supported
- Fixed bug when running scripts that contained /*
*/ within create [or replace] ... statements
- Fixed bug when saving command history that caused
the last character of statements that were not terminated by ; or / to be repeated twice
- Fixed 2 memory leaks
- Fixed possible typing before logon screen
- Added command expansion (autocomplete) and
tooltips: Pressing <TAB> when you've typed one or more letters does
a context sensitive (not perfect) match for the words that match the typed
word. If only one is found, then the partially typed word is completed
with the found word. If more matches are found, then a list with the
matches is displayed, so that you can choose one.
- Added autocomplete option to turn this fancy
feature on or off
- Fixed the funny animated column resizing in the
list view when you have many columns, should be faster too.
- Fixed describe of private synonym (used to say 'feature not implemented')
- Fixed describe of public synonym where a private
synonym existed for another user with the same name. Then that table would
sometimes be described instead of the public synonym table.
- Fixed crash when executing a query after
- Changed location of registry settings to
HKEY_CURRENT_USER. This allows per user settings instead of global
- Logon dialog box shows 10 last usernames and
databases and fills these fields with the last used values.
- Fixed new tokenizer bug of previous release that
caused newlines within sql commands not
to work in all cases
- Fixed 'allow_newline'
bug of previous release that caused the display to be corrupted
- Added 'rem' command
- Added 'editor' option to specify what editor to
use for the 'edit' command
- Added 'edit_filename'
option to specify what filename to use for the 'edit' command
- '/' command doesn't repeat the sql command again
- Added 'serveroutput'
option to display dbms_output generated messages
- Added 'commandhistory'
option to specify the number of previous commands to keep in memory
- Added 'savehistory'
option. If turned on, then the command history is saved to a file when you
exit jbsql (%WINDIR%\jbsql.his).
When you start jbsql again, the command history
is read from the file. This way you can use arrow up between sessions.
- Added Generic Option Dialog box
- Save options to registry \\HKLM\Software\JBSQL
- All options also settable via 'set' command
- Added 'directory' option to let jbsql change to that directory on startup (for easy
- Added 'arraysize'
option to specify the number of records fetched in block
- Added 'long' option to set the number of bytes
read from a LONG, LONG RAW,BLOB, CLOB or BFILE
- Added 'max_scripts'
option to specify the maximum number of nested scripts.
- Added 'maxlines' option
to specify the number of text lines in the console window that are
- Added 'maxmem' option
to specify the maximum memory usage during a query. This will
automatically adjust the array size if memory usage would be too large.
- Added 'prompt' option that allows you to set a
new prompt in the console
- Moved the 'output to screen' menu item to an
- Added 'timing' option to display timing
information for each sql command.
- Added 'connect' and 'disconnect' commands.
- Added 'connect' and 'disconnect' in file menu.
- You may now pass connect information on the commandline: jbsql.exe scott/tiger@db
- Added the current connection information to the
- Fixed a NULL value BLOB, CLOB, BFILE bug
- Added 'feedback' option to turn off sql result feedback
- Added 'heading' option to turn off row headers
- Added 'spool file' 'spool off' functionality
- You can now use " " to specify an
option value that includes spaces, useful for 'set directory "c:\program files\jbsql"'
- Added 'allow_newline'
option. If set, pressing Enter inside a sql
statement inserts a newline. If not set, then the statement is executed.
- Added 'listview'
option. This turns query output in the list view at the bottom on or off.
- Added /* */ comment support in scripts ( must be the first and last characters of a line )
You can contact me for any questions, bugs, enhancement
requests, congratulations, postcards… at the following coordinates:
If you want to thank me, please have a look at my goodies whishlist!
Xjorgen.bosmanX@gmail.com (home) (remove X)
Groot Hagelkruis 179F
You can always find the latest version (and other tools) at:
The direct download link for jbsql is:
You can log bugs at here
This is Freeware!