Home of SQLTools++
  Changelog

The following is the list of changes to the release 1.10RC2 of SQLTools++ since the previous version SQLTools++ 1.02 RC1, which is available here:

  • Tested with Oracle 11g Server and Client, recognizes 11g as server and client version.

  • Latest 11g session statistics supported, either in automatic or manual session statistics mode

  • Filter header bars added to connection dialog and schema browser object list, similar to SQLTools 1.5

    This offers the missing filter functionality in the object list (and for your convenience also in the connect dialog in case you are connecting to many databases). Thanks to Aleksey for the good ideas implemented in SQLTools 1.5.

  • Freshened GUI look, the initial SQLTools++ release used accidentally the "old" Win9x style

  • Execute script and halt on errors (new toolbar button)

    This adds the often requested "halt on errors" option to the script execution. The script execution of the whole or selected script stops when an error is encountered. You have two options to run this mode: There is a new toolbar button, and a new menu item resp. keyboard shortcut (default CTRL+F5) available. If you want this mode as default mode even if you just run "Execute as script" (default shortcut F5), then you can go to the "Settings" and enable this option in the new "PlusPlus settings" page. Note that when enabling this option effectively every script execution will halt on errors. If want to run a script unconditionally you need to disable the option, and use the existing "Execute as script" functionality.

  • Execute script in external tool (new toolbar button)

    You can run your script or a selected range of a script in an external tool. The executable needs to be defined in the new "PlusPlus settings" page of the "Settings". In addition you probably want to define the command line parameters used to call the external tool. For SQL*Plus, the following parameters might be suitable

    <USER>/<PASSWORD>@<CONNECT_STRING> @<FILENAME>

    Please note that placeholders used above (<USER>, <PASSWORD>, <CONNECT_STRING> and <FILENAME>) will be replaced with your current connection credentials.

    If you select a range of your current script file or answer "No" to the question if you want to save your file, a temporary file will be generated and the filename of this temporary file will be passed to the external tool. If you save your file before executing the external tool, the actual filename will be passed. Please note that SQLTools++ attempts to delete the temporary files created when the application exits. If your external tool is still using the temporary file, undefined things might happen or the temporary file might not be removed from your TEMP directory.

  • Use optionally DBMS_METADATA to generate DDL (can be used from Server version 9i onwards)

    In the "PlusPlus settings" page you can define that by default "DBMS_METADATA" should be used to generate DDL from the database. Of course this applies only to database server versions 9i or later, in previous releases the build-in DDL generation will be used. I've added the "DBMS_METADATA" option to all dialogs that offer DDL options ("Load" or "Extract schema") so that you easily switch between the modes without having to visit the "Settings" dialog each time. Please note that the default settings is only made persistent for the next start of the application in case you modify it in the actual "Settings" dialog. The setting modified from the "DDL options" dialogs only persist for the application run time.

    Currently the DDL options offered by SQLTools++ don't fit very well what can be customized when using DBMS_METADATA. For instance, DBMS_METADATA always uses uppercase, always prints the object owner and so on. I've tried to do my best but you'll note a lot of differences in behaviour when switching the modes. Nevertheless it offers instant support for all of your database features available, even the latest partitioning options of 11g (e.g. Range/Range, List/List, List/Range etc. etc.), so I think it is quite useful to have the option to use it. May be in future releases I'll offer different DDL options when using DBMS_METADATA, but I'm not sure yet.

  • Optionally save all files automatically before executing database operation
     
    Sometimes I'm really upset when running an database operation that's "unstoppable" which sometimes happens and is not a client, but more a server issue. If you lack the required privileges on the database then you even are not able to kill/disconnect your session, so I've decided to add an option to automatically save all your open files before running a database operation. It does not cover all operations issued by SQLTools++ yet, but there are only a few left and they should be harmless in almost all cases. If you have made a lot of changes to an important script then it's nice to be reminded to save these changes before running the next "two hours" batch process execution.

  • Test connection/Reconnect in case you were disconnected from the database, similar to TOAD functionality (new toolbar button)

    Often production databases have this IDLE_TIME profile setting in use that disconnects ("snipes" as it is called by Oracle internally) your session after a certain idle time. It's quite annoying to get an error message when issuing the next command and as next step re-connect using the connect dialog, so I've added a simple "reconnect" button and feature that attempts to test a currently open connection (by issuing a dummy "begin null; end;" PL/SQL block) and in case an error is raised or the connection has already been closed by a previous database call it attempts to re-open the last connection using the credentials used to connect. Note that currently it can also be used to re-open your last connection even if you have closed it intentionally. I'm not sure yet if this poses  a too high security risk, so I left it in because I find it quite handy.

  • Schema browser object list supports now Snapshots, Snapshot logs and the Recyclebin in 10g onwards

    The object list now has three additional tabs that shows you Snapshots (or Materialized Views as they are called now by Oracle), Snapshot Logs and from 10g onwards also the contents of your personal recyclebin. DDL support for Snapshots and Snapshot Logs is only very basic when using the builtin DDL generation, but of course you get full-blown support when using the "DBMS_METADATA" option available with 9i and later.

    The "Recyclebin" tab has some additional context menu entries "Flashback" and "Purge All" that you can use to administer your recyclebin. In addition the "Drop" functionality in this context actually means "purging" the selected entries.

  • Index object type as lookup object in the Object Viewer supported

    You can lookup now indexes as well by pressing the object viewer keyboard shortcut (default F12)

  • DDL generation support for database and DDL triggers

    Previously database and DDL triggers were not supported by the DDL generation code. Now I've added this to the builtin DDL generation code, but it's not 100% perfect yet, e.g. does not adhere 100% to the "upper/lowercase" setting. Again you can use the "DBMS_METADATA" option to get full support for all features offered by your current database server release.

  • Cleanup of Settings dialog: PlusPlus settings are now in a separate page grouped together

    With more and more new options required by the additional features of SQLTools++ I've decided it's time for a separate settings page which tidies up the remaining pages.

  • Copy selected entries from schema browser and object viewer separated by newlines using new shortcut Ctrl+Alt+C in addition to the already supported Ctrl+C shortcut

    When copying a large number of selected entries to the clipboard I find it quite handy to have them separated by newlines rather than just a plain comma, so I've added this additional shortcut (which can be customised if you want to in the "Custom" keyboard configuration). Note that you need to add this new entry manually to an existing "Custom" configuration if you upgrade an existing installation, otherwise it won't work.

  • Handle NULL passwords better by offering a separate dialog to enter the missing password

    I'm sometimes quite lazy and forget to enter a password resp. for security reasons do not save the passwords, therefore have to enter them each time I connect. So in case the database refuses the empty password which is probably most of the time the case, you'll be reminded to enter a password in a very comfortable way... Currently I think it would have been sufficient to just set the focus to the password field of the connect dialog in that case, but it works now as it is so I think it's Ok.

  • DBMS_OUTPUT now allows max. linesize of 32767 beginning with 10g

    For quite a while now Oracle increased the limits of the DBMS_OUTPUT functionality so I've adjusted that in SQLTools++ to support these new limits.

  • Unlimited DBMS_OUTPUT supported in 10g or later

    Same applies to this option, in 10g and later you can request "unlimited" DBMS_OUTPUT size, so this is now also supported.

  • 10g DBMS_XPLAN.DISPLAY_CURSOR emulation now available for Oracle9i, see "Data\display_cursor_9i.sql"

    The "Tuning by cardinality feedback" method offered by 10g out-of-the-box using DBMS_XPLAN.DISPLAY_CURSOR is available in 9i as well, but there you have put together the information yourself and don't have such a convenient function at hand. Since it is a very good SQL statement tuning approach I decided to write a anonymous PL/SQL block that emulates the DISPLAY_CURSOR functionality. It offers two functions:

    a) Without setting "statistics_level" to "ALL" in the session via "ALTER SESSION" it will display the actual plan used by the statement, which can be quite different from what EXPLAIN PLAN tells you for various reasons (Bind variable peeking, Cursor sharing, Session settings etc.)

    b) Having set "statistics_level" to "ALL" you'll get the actual number of rows per row source compared to the cardinality estimate of the optimizer which is a very powerful information as it shows you where the optimizer does incorrect estimates. Note that in case of a SELECT statement you should fetch all records of the result set in order to get  representative information. You can use the "Refresh" menu item of the "Plan" window context menu (right click) to refresh the contents after fetching all records.

    Please note that due to a limitation of Oracle 9i (the "CHILD#" column is missing from V$SESSION) the child number of the SQL is hardcoded to 0, which means that if your shared pool contains multiple versions of the same SQL statement and you are using not the the version with child number 0, this functionality will show you information about the wrong version. In order to avoid that it is recommended that you make your SQL unique, e.g. by adding a dummy comment somewhere so that it is not shared and gets a child number 0 assigned.

    You may change the contents of the file to meet your needs, or even save the code in a persistent procedure/package and change the file contents to just call this procedure/package.

    The file content is re-read each time the DISPLAY_CURSOR functionality is called.

  • MERGE sql statement now recognized and appropriate feedback provided (no. of rows merged)

  • Popup Editor window can now be closed using the ESC key as shortcut

    You can add your own shortcut by enabling the "PopupEditor.Close" shortcut in the "custom.keymap" file

  • Handle Oracle error ORA-28002 ("password expires in next <x> days") more gracefully when opening a connection

    Now the connection is established while the user connecting is in the grace period. Previously the connection was refused due to the error raised which in this case is a warning, not a fatal error

  • Check for update on web site with a single click

    Although not yet automated, you will be redirected
    to a web page using the default browser of your system that shows you if there is a newer version
    available for download.
  • Customizable settings regarding the "whitespace" and "blank" lines act as statement delimiter in the new "PlusPlus" settings page

    You now have two options in the settings dialog:

    * Empty line delimits statement
    * Whitespace line delimits statement

    If you leave both options unset, then you get the original behaviour of SQLTools. That means that from the current line on text is sent to the database until an actual SQL statement delimiter (semicolon, slash in first column) is found.

    Using either option you can set which kind of lines should delimit the statement above and below the current line.

    The difference between "empty" and "whitespace" line is that an "empty" line means that there is no character at all on that line, not even any whitespace character. "Whitespace" lines may contain any number of whitespace characters (TAB, space), but nothing else.

    The differentation is important if you have a line that is indented and create a new line. In that case SQLTools creates a new line having whitespaces in place already if you use the "Auto Indent" default setting.

    These lines wouldn't be used as statement delimiter if you uncheck the option "Whitespace line delimits statement"

  • Installation cleanup, default settings, files and web links updated

Bugfixes

  • Fixed issue with background session connection (timing issue while establishing the connection)
     
  • Explain plan did not adhere to "whitespace line" setting

  • Auto-fit columns is now executed even if the column descriptions stay the same

    There was a caching active that compared the current columns of the result set with the previous and in case they were the same no resizing took place. I deactivated that comparison because it annoyed me

  • Object lookup in Object Viewer for similar object names used previous object in case previous object name is superset (e.g. OBJECT_NAME1 vs. OBJECT_NAME) of current object name. Now an exact match is performed

  • Lookup of partitioned object fails due to "unknown object type" error message, this has been fixed

Known issues

  • Believe it or not, but the DBMS_METADATA package sometimes generates invalid and incomplete DDL. For instance generating the indexes of an table having LOB columns by calling DBMS_METADATA.GET_DEPENDENT_DDL attempts to create the LOB indexes explicitly using an incomplete and invalid CREATE INDEX command.
    This holds true even for Oracle 11g, nevertheless I doubt that this is something you would dare to call a "feature"... Still I render the option to use DBMS_METADATA as useful, but in cases like you might prefer to use the builtin DDL generation.

The wish list

Here is the ever growing list of features I would like to add, but haven't managed to cope with yet.

  • Enable filter in Object list

    Done

  • In Oracle 9i and above, use Oracle DBMS_METADATA package for DDL extraction

    Done

  • Edit Data feature: Extend existing Grid to enable DML operations (INSERT/UPDATE/DELETE)

  • Online visual match: Highlight matching braces/brackets etc. when moving cursor (more an OpenEditor issue)

  • Completey fix Auto-Fit in Grid to data, which sometimes does not work for me as intended
    Tip: Adjust the "Max column length" setting in the "Data Grid 1" options. It means the maximum number of chars to be used for calculating the column width.

    Done

  • Multi-connection feature

    Idea is to have support for multiple DB connections at the same time, and you can select from a drop-down list of connections which you want to use as current connection in your SQL worksheet. Currently you need to start multiple instances of SQLTools++ to keep multiple connections open at the same time.
     

  • Threaded query support

    Non-blocking execution of SQL

  • Execution Time in SQL history

  • Automatically save files before SQL execute

    Done

  • Null password on connect should show a separate password entry dialog

    Done

  • Preselect SYSDBA if connect as SYS

  • Run SQL file in external tool (e.g. SQL*Plus)

    Done

SQLTools++

SQLTools++ is published under the GNU General Public License (GPL). This means that this tool can be used free of charge under the GPL. The formal terms of the GPL license can be found at http://www.gnu.org/licenses/.

SQLTools++ release version "SQLTools_pp 1.10 RC2"

Previous Releases

Release Date 22/Dec/2007

Download the Windows installer here:
SQLTools_pp_1.10RC2.exe

File size: 1.107.393 bytes
MD5: f72ea2feb09f83651cd8a0f0615ae9f1
SHA1: 59a2e81dbb3a0b3ab330e3918964034d7a08bfb8
Virus checked on http://www.virustotal.com, no viruses were found

Installation instructions: If you have already an existing installation of the original SQLTools, I recommend to install SQLTools++ in a separate directory as these versions are not compatible to each other in terms of support files they require. If you have customised your existing installation (e.g. keyboard accelerators) you can try to move/migrate your changes to the new installation, but be aware that new lines have been added to some of the config files, so keep a backup of the original files in case the application does not start anymore after modifying the configuration.

If you intend to update a previous SQLTools++ installation location, the installer recognizes this and offers options to overwrite existing configuration information. It is recommended to do that to get support for all new features added. If you are using the "custom" keyboard configuration, please note that this configuration file is never updated by the installer. You therefore need to do a diff on the newly installed "default" keyboard and your "custom" config file to be able to add the new keyboard shortcuts available. If you do not add them, newly added functions like "Execute script and halt on errors" will not have a keyboard accelerator assigned in the "custom" configuration.

Download the source tarball here:
sqlt_1.10RC2.tar.gz

Browse the CVS repository here:
http://www.sqltools-plusplus.org:7676/cgi-bin/viewvc.cgi

The corresponding CVS tag for this release is: "sqltpp_1_10RC2_1"

You can download the source tarball also from the CVS repository browser.

Build instructions: You need Visual C++ 2005, Oracle OCI 10g libraries and header files (part of any regular Oracle database or client installation) and the MFC libraries, which means that Visual C++ 2005 Express Edition is not sufficient since it does not contain the MFC libraries and header files. You can download or order a trial version of Visual Studio 2005 from the Microsoft homepage:

http://www.microsoft.com/emea/msdn/visualstudio/getthetrials/default.aspx

The Professional Edition trial version available there is sufficient, you don't need the Team Edition.

The Project file to use is: SQLTools2005.sln or SQLTools2005.vcproj in the 
SQLTools subdirectory