JBSql  1.4

A freeware/open source enhanced Oracle SQL*Plus� replacement.

Created by Jorgen Bosman 2001-2019

 

 

1 Introduction

 

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

sql statements, but allows you to get a better query result overview and have better

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 available then.

 

2 Installation

 

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 11g client (or higher) installed on your pc and your Primary Oracle Home

must be pointing to the Oracle 11g 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 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 "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.

e.g.: dbserver:1521:ORCL

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 "C:\Windows\SysWOW64\odbcad32.exe"

 

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

 

4 Commands

 

4.1 Introduction

 

 

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 with

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 with

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;

exit

Quits JBSql

dir [-i|-v|-t type] [name]
ls [-i|-v|-t type] [name]

Shows a list of all user objects
-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

r
/

Repeats the previous command

ed[it] [file]

Starts an external editor containing the previous command. Or when you specify a file name, it starts the editor with the specified file.

desc[ribe] object

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 " "
e.g.: @"c:\program files\jbsql\tst.sql"
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 " "
e.g.: @"c:\program files\jbsql\tst.sql"
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.

spool file|off

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

define variable[=value]

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.

undef[ine] variable

Removes the specified variable

acc[ept] variable [num[ber]|char|date] [for[mat] format] [def[ault] default] [prompt text|nopr[ompt]] [hide]

Prompts the user to enter a value for a variable.

Refer to the following list for a description of each term or clause:

variable

Represents the name of the variable in which you wish to store a value.

NUM[BER]

Makes the datatype of variable the datatype NUMBER. If the reply does not match the datatype, ACCEPT gives an error message and prompts again.

CHAR

Makes the datatype of variable the datatype CHAR.

DATE

Makes reply a valid DATE format. If the reply is not a valid DATE format, ACCEPT gives an error message and prompts again.

FOR[MAT]

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.

DEF[AULT]

Sets the default value if a reply is not given. The reply must be in the specified format if defined.

PROMPT text

Displays text on-screen before accepting the value of variable from the user.

NOPR[OMPT]

Skips a line and waits for input without displaying a prompt.

HIDE

Hides the 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.

disc[onnect]

Disconnects the current session from the database. You cannot execute any sql commands anymore until you use the connect command again.

prompt [text]

Prints the given text to the console window.

tune (Oracle specific)

Puts the last executed command inside the sql analyzer window.

5 Options

5.1 Introduction

Description: Description: Description: Description: Description: Description: Description: C:\dropbox\Dropbox\programs\jbsql\doc\settings.gif

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.

5.2 Overview

allow_newline

If set, pressing Enter inside a sql statement inserts a newline. If not set, then the statement is executed.

arraysize

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.

askbind

Should the bind variables window be shown when statement contains bind variables.

autocomplete

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.

backgroundcolor

Specifies 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, �

If you use the set command to set this, you have to specify the RGB value as: rxxxgxxxbxxx, for example: r255g255b255 for white

commandhistory

Specifies how many previous commands are kept in memory.

debug

Is set, debug information is written to jbsql.log.

directory

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.

editor

Specifies what editor to use with the 'edit' command.

edit_filename

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.

favdir

The directory where your favourite scripts are stored. If empty, then it defaults to %APPDIR%\jbsql\favourites

feedback

If set, then a result is shown each time a statement is successfully processed.

font

The font to use in the command window. Only fixed width fonts are possible here.(not settable via command-line)

font_lv

The font to use in the results listview. All fonts are possible here. (not settable via command-line)

heading

Should headings be printed when output to screen is enabled or spooling is on?

history

Keep a history of all executed statements, visible through Statement->History

listview

Specifies whether query output should appear in the listview table at the bottom or not.

long

Specifies how many characters are read from LONG, LONG RAW, BLOB, CLOB and BFILE columns.

max_scripts

Specifies the maximum number of nested scripts. This is to prevent recursive scripts.

maxlines

Specifies how many lines are buffered in the console window.

maxmem

Specifies the maximum memory usage for queries, if arraysize*record_size > maxmem then the arraysize is automatically adjusted to fit into the maximum memory usage.

nullstr

Specifies the value to print for NULL values

savehistory

Saves the command history to a file when you exit jbsql and reads them back in when you start jbsql

scan

Scan scripts for & characters, which denotes variables and ask for variable values.

screen

Is output from queries to the console on?

serveroutput (Oracle specific)

Displays output generated by dbms_output after completion of a sql statement.

sound

Play a sound when long running queries are finished.

soundfile

Specified the sound file to play.

soundtime

Specifies the time (in seconds) after which a sound should be played..

sqlprompt

Specifies the text to use as console prompt.

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 Oracle Database

·       _O_RELEASE: Full release number of the installed Oracle Database (11g+)

·       _PRIVILEGE: Privilege level of the current connection

·       _SQLPLUS_RELEASE: Full release number of JBSql

·       _USER: User name used to make connection

·       + any 'defined' variable (see define command)

timing

If enabled, displays elapsed time information for each sql statement.

trimspool

If enabled, trims end of lines in spoolfile

verify

If enabled, displays old and new values for replaced && variables in a script.

versioncheck

If enabled, check regularly for a new jbsql version on startup.

 

6 Bind Variables (Oracle specific)

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.

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 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 ] ]

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 [variable]

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 command.

'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 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.

7.3 Autocompletion

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.

7.4 Mouse

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.

7.5 Copy-Paste

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.

7.6 Databrowser

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.7 Quick Exit

You can quickly exit jbsql by pressing Ctrl-D

7.8 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.

7.9 Multithreading

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.10 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.

7.11 Favourites

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.12 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)

Description: Description: Description: Description: Description: Description: Description: C:\dropbox\Dropbox\programs\jbsql\doc\sqlanalyzer.gif

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 statement executes.

9 Schema Browser (Oracle specific)

Description: Description: Description: Description: Description: Description: Description: C:\dropbox\Dropbox\programs\jbsql\doc\schema_browser.gif

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.

10 Export

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:

<?xml version="1.0" encoding="UTF-8"?>
<ROWS>
  <ROW num="1">
    <COLUMN1>value1</COLUMN1>
    <COLUMN2>value2</COLUMN2>
    ...
  </ROW>
  ...
</ROWS>

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;

11Sql Monitor (Oracle specific)

If you start the Sql Monitor (Tools->Sql monitor), then you can start to monitor what your current session is doing and what the progress is. It will show you the current sql statement that is being executed. Below the sql statement you will find the actual query plan that is being used and some statistics (if available) for each step in the query plan:

-       %Complete: The progress of a step, only available when the database knows how many blocks it will scan, such as full table scan, index fast full scan, hash join, ...

-       Rows: how many rows has the step returned so far. You can use this column to identify possible bottlenecks in your query

-       Execs: How many times has this step been executed. A step can be executed more than once for example in a nested loop join

-       Read req: How may disk read requests did this step make to get data

-       Write req: How may disk write requests did this step make to get data

-       Memory: How much memory is used by this step (bytes)

-       Temp: How much temp space is used by this step (bytes)

At the top you can also select another session from the list if you like to monitor someone else's session

12 Change history

V1.4.0.5 07/01/2020:

·       0000087: [Crash] crash when copy from listview after new query has started (jorgen.bosman)

V1.4.0.4 20/12/2019:

V1.4.0.3 13/06/2019:

V1.4.0.2 13/06/2019:

V1.4.0.1 12/06/2019:

V1.4.0.0 11/06/2019:

V1.3.0.7 06/10/2017:

V1.3.0.6 28/09/2017:

V1.3.0.5 20/09/2017:

V1.3.0.4 08/09/2017:

V1.3.0.3 30/03/2015:

V1.3.0.2 16/12/2013:

V1.3.0.1 31/10/2013:

V1.3.0.0 13/10/2013:

V1.2.1.0 09/03/2012:

V1.2.0.0 02/08/2011:

V1.1.0.2 19/07/2011:

V1.1.0.1 18/07/2011:

V1.1.0.0 15/07/2011:

V1.1.0.0 beta9 02/11/2008:

V1.0.2.8 27/05/2003:

V1.0.2.7 26/05/2003:

V1.0.2.6 01/03/2003:

V1.0.2.5 30/01/2003:

V1.0.2.4 27/01/2003:

V1.0.2.3 13/01/2003:

V1.0.2.2 28/11/2002:

V1.0.2.1 24/09/2002:

V1.0.2.0 22/09/2002:

V1.0.1.5 17/09/2002:

V1.0.1.4 30/07/2002:

V1.0.1.3 13/05/2002:

V1.0.1.2 02/05/2002:

V1.0.1.1 02/05/2002:

V1.0.1.0 01/05/2002:

V1.0.0.6 25/04/2002:

V1.0.0.5 17/04/2002:

V1.0.0.4 04/03/2002:

V1.0.0.3 23/02/2002:

V1.0.0.2 19/02/2002:

V1.0.0.1 07/02/2002:

V1.0.0.0 28/01/2002:

22/01/2002:

20/01/2002:

16/01/2002:

10/01/2002:

02/01/2002:

11/06/2001:

07/06/2001:

08/05/2001:

03/05/2001:

01/05/2001:

12 Contact

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 address:
Jorgen Bosman
Groot Hagelkruis 179F
2180 Antwerpen
Belgium


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!