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 Oracle's SQL*Plus©,
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
statements, but allows you to get a better query result overview and have
editing and command history capabilities.
This program is intended to be
largely compatible with Oracle's SQL*Plus©, but not all
have been implemented yet, and some may never be implemented...
It also supports connecting to ODBC
sources, but not all features are available then.
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
pointing to the Oracle 10g home. Alternatively, you can extract the jbsql.exe
to your %ORACLE_HOME%\bin
then you don't have to worry about your Oracle Homes.
To start jbsql,
just double-click the jbsql.exe file...
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
This should be sufficient for most
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
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
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
If you have x64 windows and x86 jbsql, then you need to configure your datasource
You can connect using the command
line parameter with user/password@odbc:datasource:
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.
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
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 with
the ; or /
For an overview of all sql
statements, see http://otn.oracle.com (sql
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 with
the ; or /
character. The exact syntax of the statements and the kind of statements
depends on the datasource.
4.2 JBSql Commands
The execute command allows you to execute a stored
This is identical to BEGIN stored_procedure; END;
dir [-i|-v|-t type] [name]
ls [-i|-v|-t type] [name]
Shows a list of all user objects
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 command
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.
name (Oracle specific)
Shows the initialization parameters that contain name
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.
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
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
Gives a value to a variable. This variable can be
used inside scripts by using &variable or &&variable.
If you do not specify =value, then the current value is shown.
Removes the specified variable
acc[ept] 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.
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 datatype CHAR.
Makes reply a valid DATE
format. If the reply is not a valid DATE format, ACCEPT gives an error message
and prompts again.
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
default value if a reply is not given. The reply must be in the
specified format if defined.
on-screen before accepting the value of variable from the user.
Skips a line and waits for
input without displaying a prompt.
text that you type behind '*' characters.
conn[ect] 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
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
If set, pressing Enter inside a sql statement inserts a
newline. If not set, then the statement is executed.
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 executed statement.
Specifies whether or not autocomplete should be active
when you press TAB.
autotrace (Oracle specific)
When autotrace 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.
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 startup directory.
Specifies what editor to use with the 'edit' command.
Specifies what filename to create with the 'edit' command
fastresults (Oracle specific)
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. This option is disabled when you set it to 0.
The directory where your favourite scripts are stored. If empty, then it
defaults to %APPDIR%\jbsql\favourites
If set, then a result is shown each time a
statement is successfully processed.
The font to use in the command
window. Only fixed width fonts are possible here.(not
settable via command-line)
The 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
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
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
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 play.
Specifies the time (in seconds) after which a sound should be played..
Specifies the text to use as
The following variables are replaced:
_CONNECT_IDENTIFIER: Connection identifier used
to make connection, where available
_DATE: Current date
_EDITOR: Specifies the editor used by the EDIT command
_O_VERSION: Current version of the installed
_O_RELEASE: Full release number of the installed
Oracle Database (11g+)
_PRIVILEGE: Privilege level of the current
_SQLPLUS_RELEASE: Full release number of JBSql
_USER: User name used to make connection
+ any 'defined' variable (see define command)
If enabled, displays elapsed time information for each sql statement.
If 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 variables.
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 file.
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 ] ]
arguments shows all known bind variables and their datatype.
You can also view
the value of a bind variable with the print command:
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 command.
'Home' moves the cursor to the beginning of the current line of the current
'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 text.
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 command.
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 expect.
Autocomplete also works for specifying script names after the
@ command. It completes directory and file names.
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 is running.
7.9 User friendly
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
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
The SQL Analyzer helps you to tune your sql statements. At the top, you can
enter your sql statement.
If autotrace is on or if you enter the tune
command, the last executed command is put at the top.
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
9 Schema Browser (Oracle
With this tool, you can see the DDL create statements for most database
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 menu.
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 some_table;
- 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 command (jorgen.bosman)
- 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) -
- 0000028: [Enhancement Request] CTRL-A in listview (jorgen.bosman) - closed.
- 0000020: [Enhancement Request] Add crashhandling (jorgen.bosman)
- 0000027: [Enhancement Request] Allow logon dialog to
be closed with ESC button (jorgen.bosman) -
- 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.
- 0000008: [Enhancement Request] Add data browser (jorgen.bosman) - resolved.
- 0000012: [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)
- 0000009: [Bugs] Login box does not show ORACLE as default
database on first use (jorgen.bosman) -
- 0000007: [Crash] crash in autocomplete (jorgen.bosman)
- 0000002: [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)
- 0000004: [Enhancement Request] drop down up key on first element goest to bottom of list (jorgen.bosman)
- Fixed some hanging issues
- 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 ctrl-x)
- 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 with aliases.
- 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
- 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 header
- Fixed bug: "explain;" caused jbsql to crash
- Fixed bug: Paste of "é" for example did not
- 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
- 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
- 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 command.
- Lines selected in list view stay visible when list
- 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 with
- Fixed bug#10: statement completion for packages
- Fixed bug#15: crash on show param
- Fixed bug#16: error when cancel multiple line statement
- Fixed bug#17: % is removed when pressing r
- Added /nolog command-line
- Command history file is now also stored in your
documents instead of in the windows folder.
- Fixed bug#18: Cancelling queries with CLOB
- 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 when
- Fixed small resizing problem with sql analyzer
- Export in csv format now also exports a heading with
- 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 crash
- Fixed bug: 'exit' at the end of a script without
newline whould crash
- Fixed bug: spool off without first spooling corrupted
- 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 script.
- 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 select statement.
- You can now use the mouse to position the cursor in the
- '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 command.
- You can now enter a newline within a command by using
- '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 'spool'
- 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
- 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
- Fixed a bug in running scripts that contain / within
- Slightly improved script running speed
- Improved schema browser speed by using array fetching
- 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 data.
- 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 column.
- Added some keyboard shortcuts
- Hints for table names containing a $, now work too
- Fixed paste problem with strings beginning with #
- Added Edit menu
- Ctrl-C no longer cancels a query, instead use Ctrl-X
- 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 weird.
- 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 exist.
- 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
- 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 bug.
- 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
- 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 works
- 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
- 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 or host:port
- connect command without password asks for password
- 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 dialog box
- 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
- Abbreviated the connect command to conn[ect]
- Abbreviated the disconnect command to disc[connect]
- 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
- 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 output
- 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
- Added New Window menu item. This opens a new jbsql windows with the
- Added Commit, Rollback and Repeat to menu and toolbar
- 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
- 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]' support
- Added more verbose statement/query progress
- Query can now be cancelled when no records have been
- 'define variable=value' support
- 'undef variable' support
- '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
- Fixed 2 memory leaks
- Fixed possible typing before logon screen appeared
- 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
- 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
- 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 'connect'
- 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 script execution)
- 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 buffered.
- 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 option.
- 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 window
- 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!
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!