Functions Introducing

How to Run

How to Config

    After the tool started, firstly open 'Oracle Connect' window. The window supplies two kinds of connection example(supplies initial password of user SYS,SYSTEM and SCOTT). User need update existed connection config(like host,port etc.), can create a new connection config(just input a new alias in input field of combo box "Alias"), but can not delete existed connection config. If user want to more expediently modify\update\create connection config, or connection config is complicated, so need enter "Config" window to edit. There are 2 approaches to 'Config' window: One is to click icon button 'Config' on the top right corner of 'Oracle Connect' window, the other is to click icon button 'Config' on the tool bar.

    'Config' window supplies two kinds of config example:


    • Example 1:

      alias=Template-1;url=127.0.0.1:1521:orcl

    • Example 2:

      alias=Template-2;url=//127.0.0.1:1521/oracleserviceorcl

    • Template 3:

      alias=Template-3;url=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=oracleserviceorcl)))

    String 'Template-1' is a connection string(also called as alias), it will appear in the combo box of 'Connect' window, used to distinguish from other connections;
    String '127.0.0.1' Oracle server's IP address or host name;
    String '1521' is port number;
    String 'orcl' is SID;
    Strng 'oracleserviceorcl' is SERVICE_NAME

    You can copy then modify the template to create database connections,
    or
    press button 'Create SID connection' to create a line 'alias=[alias_name];url=[host]:[port]:[sid]',
    then replace the 4 strings: '[alias_name]', '[host]', '[port]', '[sid]'.
    or
    press button 'Create (Service Name)' to create a line 'alias=[alias_name];url=//[host]:[port]/[service_name]',
    then replace the 4 strings: '[alias_name]', '[host]', '[port]', '[service_name]'.

    Note: (1) You only need modify above 5 strings, please don't modify other strings, moreover, each config must be completed in a line, can not be separated by Enter, otherwise, will lead to connect failure;

    (2) If connection config is complicated, or you want to directly use the connection configs in file TNSNames.ora, please use the format of above template 3(related button is 'Create TNS connection').

    (3) If need connect as SYSDBA, please append string " AS SYSDBA" in user field, or select check box "connect as SYSDBA";
    If need connect as SYSOPER, please append string " AS SYSOPER" in user field;
    If user is 'SYS', must use "SYSDBA", if user is 'SYSTEM', must use "SYSDBA" or "SYSOPER"

How to Use

    When successfully connected to Oracle server by 'Oracle Connect' window, you can operate the database.
    As this tool adopts graphic interface, so graphic interface's universal keyboard and mouse's operations are also applicable for this tool(you can choose and switch interface's language, style and background color via menu 'GUI').

    The author is using the order 'SQL Editor> Menu/Tool bar buttons> Worksheet > Monitor > Data Dictionary > Message Bar/Progress Bar/Status Bar' to introduce each section:

    • SQL Editor

      It is used to input/type SQL statements and commands, including SELECT, UPDATE, INSERT, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE, LOCK, COMMIT, ROLLBACK and so on. Moreover, this tool also supplies/supports the below commands:

      # desc [user.]objectName[@dbLink]

      If objectName is a view or materialized view(snapshot), pop up a window to show view's column information(including column name, data type and length); If objectName is a table name, show table's structure information including column information(column name, data type and length), indexes, constraints(primary key, unique key, check key, child table, trigger, foreign key, even foreign key's foreign key); If objectName is other, pop up a message box to point out its object type name.

      (Remind: There is a simple and shortcut way to see a object's information: press Ctrl key + mouse click string '[user.]objectName[@dbLink]' in SQL editor or monitor's log area or amend area)

      # describe [user.]objectName[@dbLink]

      Same as statement 'desc [user.]objectName[@dbLink]'

      # schema [user.]objectName[@dbLink]

      If objectName is a table name or view name or materialized view(snapshot), display its generated scripts in monitor, to tell that talbe or view is how to created(including indexes and constraints); if objectName is other, points out its object type.

      # explain plan for SQL

      Quickly display SQL statement's explain plan(also called as execution plan). From explain plan, can syllabify see indexes' using, whether there is full table scan, and so on, finally estimate a SQL statement's performance(Remind: Button 'Explain Plan' in tool bar also can directly display a selected statement's explain plan)

      # commit

      Commit UPDATE, INSERT, DELETE and so on statements' operations

      # rollback

      Cancel/rollback UPDATE, INSERT, DELETE and so on statements' operations

      # roll

      Same as statement 'rollback'

      # set autocommit on

      Set Autocommit's status as 'ON'(shows 'Autocommit: ON' in status bar), can automatically commit UPDATE, INSERT, DELETE and so on statements' operations, needn't execute statement 'commit'

      # set autocommit off

      Set Autocommit's status as 'OFF'(shows 'Autocommit: OFF' in status bar, default status is 'OFF'), can not automatically commit UPDATE, INSERT, DELETE and so on statements' operations, need execute statement 'commit'

      # show autocommit

      Show Autocommit's status('ON' or 'OFF')

      # set timing on

      Set Timing's status as 'ON'(shows 'Timing: ON' in status bar, default status is 'ON'), display each statement and each statement's starting time(format 'hour:minute:second') and wasted time(format 'hour:minute:second.millisecond') in monitor's log area

      # set timing off

      Set Timing's status as 'OFF'(shows 'Timing: OFF' in status bar), will not display SQL statement's starting time and wasted time in monitor's log area

      # show timing

      Show Timing's status('ON' or 'OFF')

      # set echo on

      Set Echo's status as 'ON'(shows 'Echo: ON' in status bar, default status is 'ON'), in monitor's log area, display SQL statement before statement result

      # set echo off

      Set Echo's status as 'OFF'(shows 'Echo: OFF' in status bar), in monitor's log area, will not display SQL statement before statement result
      (avoid large volume of log output and reduce memory consumed)

      # show echo

      Show Echo's status('ON' or 'OFF')

      # @filename

      Needn't copy outer file's scripts to SQL editor, it can directly read and execute statements and commands from outer file(Note: demand full path name and filename, for example: '@D:\dir1\dir2\import.sql')

      # @@filename

      Same as command '@filename'

      # run filename

      Same as command '@filename'


      This tool's SQL editor is a colorful text editor:
      (1) Use different colors to differentiate from keywords, comment block, strings in quotation marks, operators, special characters and so on(Remind: /* and */ used to comment a block with multiple lines; -- is single line comment)
      (2) If cursor locates after a bracket, points out a couple of conjugated brackets by highlight(Remind: '(' conjugated with ')', '[' conjugated with ']', '{' conjugated with '}')

      This tool's SQL editor is a intelligent SQL editor:
      Only need input/type part string, pops up prompt window 1 second later, lists keywords, keyword unions, function names, column names, object names, object types, parameters, functions and procedures of package, thus, can much improve the efficiency in SQL writing. 3 kinds as below:

      (1) Prompt function of keyword, function name and object name:
      Only need input/type 3 or more letters, pops up prompt window 1 second later, lists all keywords,function names and object names that starts with inputed letters. For example, if input 'sel', will list keywords 'SELECT', 'SESSION' etc., and keyword unions 'SELECT', 'SELECT DISTINCT', 'SELECT * FROM' etc.

      (2) Prompt function of dot:
      Inputs/types a dot '.', pops up prompt window 1 second later, lists column names, object names, or procedure/function names of a package.
      For example, inputs string 'abc'+'.'
      (2.1) If 'abc' is a table name or view name, or its alias, then lists its all column names and data types;
      (Remind: alias can also be a virtual table's alias, for example: '(select ... from ...) aliasName' )
      (2.2) If 'abc' is a username, lists this user's all object names;
      (2.3) If 'abc' is a package, lists package's all procedure/function names;
      (2.4) If 'abc' is a sequence, prompt window contains 2 items: 'currval' and 'nextval'

      (3) Prompt function of space/comma:
      Inputs/types a space(can be TAB or Enter key) or comma, pops up prompt window 1 second later, lists object names, object types, parameters. Detail as below:
      (3.1 ) 'update'+space, lists current user's all tables/views/snapshots;
      (3.2 ) 'insert into'+space, lists current user's all tables/views/snapshots;
      (3.3 ) 'delete'+space, lists current user's all tables/views/snapshots;
      (3.4 ) 'select ... from'+space, lists current user's all tables/views/snapshots;
      (3.5 ) 'delete from'+space, lists current user's all tables/views/snapshots;
      (3.6 ) 'create'+space, lists all object types, like 'TABLE', 'VIEW' and so on;
      (3.7 ) 'create or replace'+space, lists object types 'PROCEDURE', 'FUNCTION', 'VIEW' and so on;
      (3.8 ) 'alter'+space, lists all object types, like 'TABLE', 'VIEW' and so on;
      (3.9 ) 'drop'+space, lists all object types, like 'TABLE', 'VIEW' and so on;
      (3.10) 'truncate'+space, lists 'CLUSTER' and 'TABLE';
      (3.11) 'lock'+space, lists 'TABLE';
      (3.12) 'truncate'+space, lists object types 'CLUSTER', 'INDEX' and 'TABLE';
      (3.13) 'comment on'+space, lists object types 'SNAPSHOT', 'TABLE' and 'VIEW';
      (3.14) 'create synonym ... for'+space, lists current user's all object names;
      (3.15) 'create public synonym ... for'+space, lists current user's all object names;
      (3.16) 'grant'+space, lists all system privileges and object privileges;
      (3.17) 'revoke'+space, lists all system privileges and object privileges;
      (3.18) 'grant ... to'+space, lists all usernames/roles;
      (3.19) 'revoke ... from'+space, lists all usernames/roles;
      (3.20) 'grant ... to ...'+comma, lists all usernames/roles;
      (3.21) 'revoke ... from ...'+comma, lists all usernames/roles;
      (3.22) 'revoke ...'+comma, lists all system privileges and object privileges;
      (3.23) 'grant ...'+comma, lists all system privileges and object privileges;
      (3.24) 'revoke ...'+comma, lists all system privileges and object privileges;
      (3.25) 'grant ... on'+space, lists current user's all object names;
      (3.26) 'revoke ... on'+space, lists current user's all object names;
      (3.27) 'desc'+space, lists current user's all object names;
      (3.28) 'describe'+space, lists current user's all object names;
      (3.29) 'schema'+space, lists current user's all object names;
      (3.30) 'create ... index ... on'+space, lists current user's all tables/clusters;
      (3.31) 'create ... trigger ... on'+space, lists current user's all tables;
      (3.32) 'temporary table'+space, lists current user's all temporary tables;
      (3.33) 'table'+space, lists current user's all tables;
      (3.34) 'view'+space, lists current user's all views;
      (3.35) 'public synonym'+space, lists all public synonyms;
      (3.36) 'synonym'+space, lists all public synonyms or current user's all synonyms;
      (3.37) 'unique index'+space, lists current user's all unique indexes;
      (3.38) 'bitmap index'+space, lists current user's all bitmap indexes;
      (3.39) 'index'+space, lists current user's all indexes;
      (3.40) 'indextype'+space, lists current user's all index types;
      (3.41) 'constraint'+space, lists current user's all constraints;
      (3.42) 'table ... constraint'+space, lists specified table's all constraints;
      (3.43) 'procedure'+space, lists current user's all procedures;
      (3.44) 'function'+space, lists current user's all functions;
      (3.45) 'package'+space, lists current user's all packages;
      (3.46) 'package body'+space, lists current user's all package bodies;
      (3.47) 'trigger'+space, lists current user's all triggers;
      (3.48) 'sequence'+space, lists current user's all sequences;
      (3.49) 'snapshot'+space, lists current user's all snapshots;
      (3.50) 'segment'+space, lists current user's all segments;
      (3.51) 'object'+space, lists current user's all objects;
      (3.52) 'database link'+space, lists current user's all database links;
      (3.53) 'library'+space, lists current user's all libraries;
      (3.54) 'dimension'+space, lists current user's all dimensions;
      (3.55) 'operator'+space, lists current user's all operators;
      (3.56) 'resource cost'+space, lists all system resource;
      (3.57) 'cluster'+space, lists current user's all clusters;
      (3.58) 'role'+space, lists current user's all roles;
      (3.59) 'user'+space, lists all usernames;
      (3.60) 'profile'+space, lists all profiles;
      (3.61) 'type'+space, lists current user's all types;
      (3.62) 'type body'+space, lists current user's all type bodies;
      (3.63) 'temporary tablespace'+space, lists current user's all temporary table spaces;
      (3.64) 'tablespace'+space, lists current user's all table spaces;
      (3.65) 'datafile'+space, lists all data files;
      (3.66) 'controlfile'+space, lists all control files;
      (3.67) 'logfile'+space, lists all log files;
      (3.68) 'database'+space, lists all database names;
      (3.69) 'alter session set'+space, lists all session parameters;
      (3.70) 'set transaction'+space, lists items 'READ ONLY', 'READ WRITE' and so on;
      (3.71) 'set'+space, lists items 'autocommit', 'echo', 'timing' and 'transaction';
      (3.72) 'show'+space, lists items 'autocommit', 'echo' and 'timing'

      Remind: When SQL editor's mouse is tundish shape, if press key Esc or change cursor's position, then stop searching, will not pop up prompt window any longer.
      When prompt window appears(visible), prompt window can automatically search. If 'S*' selected, search such items that starts with sub string, for example: if input 'name', will search 'NAME1', 'NAME2' etc. If '*S*' selected, search such items that incudes sub string, for example: if input 'name', will search 'FIRST_NAME', 'NAME1', 'NAME2' etc.
      press 'Ctrl+PdDn' key or button to downwards search for item 'last_name', press 'Ctrl+PdUp' key or button to upwards search item 'first_name'.
      Moreover, prompt window supports/supplies the following keyboard/mouse operations: up/down arrow key - upwards/downwards display a item with highlight; Home key - displays the first item with highlight; End key - displays the last item with highlight; PgUp/PgDn; Enter key(or mouse double click a item) - Choose a item and close prompt window; Esc key or button - close prompt window; mouse click on the top gray border of prompt window - sort by column.



      This tool uses statement blocks to edit in SQL editor. Statement block is such defined: If SQL editor's some text is selected, then statement block is all selected text(blue background area); otherwise(no text selected), statement block is continuous text without blank line before/after cursor position(that is, blank line is used to compart statement blocks). There is a block mark in the left of SQL editor, block mark can mark current statement block's starting line number and ending line number.

      One statement block can be a single statement, also can include multiple statements.
      If a line's last letter is semicolon ';' or statement's next line is a blank line or '/', then it is statement's end mark;
      If a line is statement 'desc [user.]objectName[@dbLink]' or 'describe [user.]objectName[@dbLink]' or 'schema [user.]objectName[@dbLink]' or 'roll' or 'rollback' or 'commit', or a line starts with '@' or '@@' or 'run ', then this line is already a statement(don't need end mark)

      Can use 3 ways to operate SQL editor: its popup menu(click it by mouse right key), or main menu 'Edit', or icon buttons of tool bar. The following text is for topic 'Menu/Tool bar buttons'


    • Menu/Tool bar buttons

      # Execute Single Statement

      Execute single statement of SQL editor(shortcut key is Ctrl+Enter or F8 . Note: If Mac system, all key "Ctrl" in the help document are replaced as key "Command", shortened as "Cmd")
      If SELECT statetment, executed reslut displays in workwheet, result information displays in message bar;
      If statement 'desc [user.]objectName[@dbLink]', then pop up a window to show object information;
      If statement 'schema [user.]objectName[@dbLink]', or 'explain plan for SQL' or '@filename', executed reslut and result information both displays in monitor;
      other statements' result information displays in message bar.

      # Execute Statement(s) by Monitor

      shortcut key is Ctrl+M or F9
      Use monitor to monitor the executing of single or multiple statements of SQL editor's current statement block, executed results and result information both display in monitor.

      (Note: If statement block includes multiple statements, must use the menu item or icon button, must not use 'Execute Single Statement'; If statement block is a PL/SQL statement, must not use the menu item(or icon button), should use 'Execute Single Statement')

      # Commit

      When Autocommit's status is 'OFF', and executed INSERT, DELETE, UPDATE and so on statements in SQL editor, then the menu item or icon button is enabled/valid(clicking it equal to executeing statement 'commit').

      # Rollback

      When Autocommit's status is 'OFF', and executed INSERT, DELETE, UPDATE and so on statements in SQL editor, then the menu item or icon button is enabled/valid(clicking it equal to executeing statement 'rollback').

      # Explain Plan

      Displays explain plan of SQL editor's single statement in monitor. From explain plan we can syllabify see a SQL statement's index using, full table scanning and so on, consequently estimate a statement's performance.

      # Export

      shortcut key is Ctrl+O
      If SQL editor's selected statement is a SELECT statement, 'Export' button or menu item is enabled/valid.
      Don't display SELECT statement's executed result in worksheet, but directly export to XLS/CSV/INSERT Statement/HTML/XML these 5 formats to a outer file, it is used to backup/migrate/print etc.(If selected format is XLS, maybe can only export 5000 or 10000 records, it is due to memory size limited. If user indeed want to export many XLS records, user can firstly export serveral small XLS files then merge them into a large XLS file, or firstly export to a CSV file then open it with XLS(Excluding XLS format, other 4 export formats are not limited by memory size, only limited by hard-disk space; If selected format is CSV, default delimiter is comma ',', if data contains selected delimiter or Enter, so the whole cell content will be quoted by double quotation marks,this is base on CSV rule.)).

      # Config

      Open 'Config' window to create database connections and set this tool's serial number (serial_no).

      # Oracle Connect

      Open 'Oracle Connect' window to create database connection/session

      # set autocommit on/off

      Used to switch Autocommit's status(equal to execute statement 'set autocommit on' or 'set autocommit off').
      (When Autocommit's status is 'ON ', can automatically commit operations of statements INSERT, DELETE, UPDATE and so on)

      # set timing on/off

      Used to switch Timing's status(equal to execute statement 'set timing on' or 'set timing off').
      (When Timing's status is 'ON', can display each statement's starting time and wasted time in message bar or monitor)

      # set echo on/off

      Used to switch Echo's status(equal to execute statement 'set echo on' or 'set echo off').
      (When Echo's status is 'OFF', in monitor, only display SQL statement's result information, don't display corresponding SQL statement)

      # Disconnect

      Open 'Disconnect' window, disconnect selected sessions' database connections to release database resource.
      (Mouse click a item of list box and press key 'Ctrl+A', can select all items; Press Ctrl key and mouse click a item, can select/unselect a item; After a session disconnected, can directly execute statement if again use next time, need not reconnect by 'Oracle Connect' window, as this tool can automatically reconnect)

      # Load Scripts

      Load text/statements from outer file to SQL editor(shortcut key is Ctrl+L).

      # Save All Scripts

      Save SQL editor's all text/statements to a outer file(shortcut key is Ctrl+S).

      # Cut

      Cut SQL editor's selected text to clipboard(shortcut key is Ctrl+X).

      # Copy

      Copy SQL editor's selected text to clipboard(shortcut key is Ctrl+C).

      # Paste

      Paste clipboard's text to SQL editor(shortcut key is Ctrl+V).

      # Find & Replace

      Open window 'Find & Replace' to search or replace suited strings in SQL editor(shortcut key is Ctrl+F; shortcut key F3 is used to search for the next one).

      # Undo

      Cancel editing operations in SQL editor(shortcut key is Ctrl+Z)

      # Redo

      Redo(restore) editing operations in SQL editor(shortcut key is Ctrl+Shift+Z)

      # Format SQL

      beautify and format and rearrange current block's tanglesome statements to improve statement's readability.

      # Add Comments

      Add comment marks for current statement block(that is, add comment mark '--' at the beginning of current statement block's each line).

      # Remove Comments

      Remove current statement's all comment marks(/* */ and --)

      # Remove Quotation Marks

      In Java or other program language, multiple line SQL statment usually uses double quotation marks '"' and plus sign '+' to join lines(for example, "select ..." + "where ..."),
      If programmer want to debug/run such a statement, need manually remove all extra double quotation marks and plus signs, or use other text editor to replace all double quotation marks and plus signs. But some plus signs are operation symbols or outer joining symbols, that is, such plus signs can not be removed, so it takes time and gives a lot of care to remove all extra double quotation marks and plus signs, even easily make a mistake. In order to make facility for programmers, this tool specially supplies the menu iteam and tool bar button, via it, programmers can expediently and quickly remove all extra double quotation marks and plus signs.
      (for example, string "a\"b"+"c(+)", will be changed to a\"b c(+) )

      # Add Quotation Marks

      Add double quotation marks at the beginning and ending of current block's each line(use plus signs to join lines), thus, programmers can put debugged SQL statements to Java program or other language programs, make facility for programm writing.

      # Increase Indent

      Move right current block by a Tab position(8 spaces, shortcut key is Tab).
      (If Ctrl+right arrow key, move right current block by a space)

      # Decrease Indent

      Move left current block by a Tab position(8 spaces, shortcut key is Shift+Tab).
      (If Ctrl+left arrow key, move left current block by a space)

      # Column Block Operation

      Open window 'Column Block Operations', can operate current block in column block mode.
      (Mouse dragging or double clicking to select a column block, then can operate the column block(can copy, cut, remove, move right, move left and so on))

      # To Upper Case

      Convert current block's text to upper case.

      # To Lower Case

      Convert current block's text to lower case.

      # Help Topics

      Open window 'Help Topics' (shortcut key is F1).
      (If Chinese interface, invoke help file 'Help-in-Chinese.html' in working directory; If English interface, invoke help file 'Help-in-English.html')

      # About Oracle SQL Handler

      Open window 'About Oracle SQL Handler', displays this tool author's WeChat number, WeChat QR code, QQ number and E-mail address, tool's version, opertion system's style, JRE version, Jcurrent session's Oracle version, this software's copyright statement, leave word web pages and so on.

    • Worksheet

      If uses menu item 'Execute Single Statement' (or tool bar button) to execute a SELECT statement, then SELECT statement's executed result will be displayed in worksheet.(In order to avoid too many rows displaying to affect memory and performance, so there is a field "Limit Select Rows" in worksheet's tool bar, default 2000 limited rows. If exceeds 2000 rows, need check memory using when each 5000 rows read, don't read remained rows if remained memory is not enought! So usually at most 10000-20000 rows can be displayed. The writer think that it is unnecessary display too many rows, because, if display too many, it is impossible that user scan or edit all records one by one. So it is required that user should not set too large value in field "Limit Select Rows", or should add filter condition when writing SELECT statement. If user indeed want to scan or save many records, please refer to above "Export" function.)

      Worksheet can be operated by main menu 'Worksheet' or its popup menu(click it by mouse right key). The following is to introduce each menu item's instruction and usage:

      # Insert A Blank Row

      Shortcut key is Ctrl+I
      If cell(s) selected, then inserts a blank row before the first selected row, otherwise(no selected grids), appends a blank row at the bottom of worksheet(Note: blank row is only inserted to worksheet instead of database).

      # Duplicate A Row

      Shortcut key is Ctrl+U
      If need insert such a row, its most content is the same as an existed row's, if use "Insert A Blank Row", need input too much, this time, should use "Duplicate". That is, firstly select a row's all or some cells, then press menu item "Duplicate A Row", after row duplicated, make some modification, thus, a row quickly inserted.

      # Delete Selected Row(s)

      Shortcut key is Ctrl+D
      If cell(s) selected, then delete/remove all selected rows(Note: only delete worksheet's selected rows instead of database's records).

      # Commit Changes

      Shortcut key is Ctrl+S
      If button's icon is red or menu item is visible, it means that database table can be updated by updating worksheet's data(a table name or view name displayed in right label of worksheet's tool bar).
      If this menu item is visible and enabled(black), it means that worksheet's data is changed(updated, inserted or deleted rows), can commit/save all changes to database table(If a row happens error in committing process, this tool can point out the error row with highlight, and gives curt error message. Thusness amends error again and again till successfully commit. When successfully commited, this menu item again becomes disabled(gray)).

      # Find & Replace

      Shortcut key is Ctrl+F
      Pop up window 'Find & Replace', can search or replace worksheet's data(It is very useful when worksheet's data volume is large).
      'Data Type' includes: 'Numeric Cells' and 'Non-Numeric Cells'(If cell's title shows data type NUMBER,FLOAT or INTEGER, then the cell is numeric cell);
      'Search Range' includes: 'All Cells', 'Selected Cells' and 'Unselected Cells';
      'Search Direction' includes: 'Rows' and 'Columns'.

      # Single Record Operations

      Shortcut key is Ctrl+R
      If worksheet contains many columns, leads to long row, sometimes it is not convenient to scan or edit long row, so this tool supplies window 'Single Record Operations', adopts vertical sheet to display a record(vertical sheet's first column displays column names, and the second column displays data)(vertical sheet supports popup menu by mouse right key clicking).

      # Copy Cell(s) With Title

      Shortcut key is Ctrl+W
      Copy selected cells with titles

      # Copy Separated Cells

      Copy selected cells, use selected delimiter to separate columns.
      If save copied result as CSV file, then the CSV file can be opened by Microsoft Excel;
      If selected delimiter is Tab key, then copied result can be directly pasted to opened Excel file.
      (If use shortcut key Ctrl+C, delimiter is Tab key)

      # Copy Separated Title

      Copy selected cells' titles, use selected delimiter to separate titles.
      (Remind: If no selected cells, then copy worksheet's all titles; This menu item usually cooperates with menu item 'Copy Separated Cells')

      # Export Selected Cells

      Shortcut key is Ctrl+O
      Export selected cells to XLS/CSV/INSERT Statement/HTML/XML these 5 formats to a outer file, it is used to backup/migrate/print etc.(please refer to above direct "Export")

      # Refresh

      Refresh worksheet's data(rerun previous SELECT statement)

      # Number Statistic

      Get a statistic result of selected numeric cells, including count, maximum, minimum, sum, average

      # Empty Cell(s)

      Shortcut key is Ctrl+E
      Empty worksheet's all selected cells

      # Select All

      Shortcut key is Ctrl+A
      Select worksheet's all cells

      # Select Whole Row

      Shortcut key is Ctrl+L
      If row(s)'s part cells is selected, can select whole row(s) by this menu item


      Morevoer, this tool's worksheet supports/supplies the following functions/operations:
      (1) If mouse points to a column title, shows the column's data type, data length and data format;
      (2) If mouse left key click a column title, sort the column in ascend/descend order;
      (3) If mouse left key click row number column, or drag mouse by pressing mouse left keky, can easily selct whole rows(can support Ctrl key or Shift key, like Excel sheet operations);
      (4) If mouse right key click a column title, copy the single title;
      (5) If Ctrl+mouse left key click a column title, select/unselect the column's all cells(not like Excel: in Excel, mouse left key click column to select whole row, in this tool, mouse left key click column to sort column);
      (6) If mouse drag a column title, change the column's position in worksheet;
      (7) If choose worksheet's one row and press 'Alt+ up/down arrow key', move upwards/downwards the selected row;
      (8) Field "Limit Selected Rows" only accept value from 1 to 999999, can support Up/Down arrow key and Page Up/Dow key, can increase or decrease its value by step 100, thus, can quickly adjust its value;
      (9) If mouse points to the boundary between column titles, mouse shape will become level-directional double-directional arrows. If press mouse left key and drag left/right, can change column's width; If double click mouse left, can adjust column to appropriate width so that a column's all cell content can be visible;If a whole row is selected or the whole sheet is selected, mouse left key double click to adjust all columns to appropriate width, it the same as Excel sheet operations;
      (10) If cell's data type is NUMBER, VARCHAR2, DATE, or TIMESTAMP and so on(cell background color is white), can directly edit by mouse double clicking;
      If data type is large text type, like CLOB, LONG(cell background color is light yellow), mouse double click it to pop up a multiple-text window;
      If data type is BLOB, LONG RAW and so on(cell background color is gray), such cells are disabled(not editable).

      If want to update database table by updating worksheet's data(that is, menu item 'Commit Changes' is visible, a table name can be showed on the left top corner of worksheet's border), demands that worksheet's at least one editable column exists in searched table. Please refer to the below examples:
      (1) Statement 'select * from tableName ...' is eligible;
      (2) Statement 'select col_1, col_2 from tableName ...' is eligible;
      (3) Statement 'select a.col_1, trunc(a.col_2) from tableName a ...' is eligible;
      (4) Statement 'select upper(col_1), sysdate from tableName ...' is NOT eligible, because no column names exist in searched table.

    • Monitor

      Also called as console, it is used to monitor the running of each statement of a batch.

      # Check box 'Pause On Exception'

      When the check box is selected, if batch's one statement runs error, then paused on the error statement, this time user can amend it then rerun, or ignore the error statement and continue the next statement;
      When the check box is not selected, even if a statement run error, will not pause, continue the next statement till the ending of the last statement.

      # Check box 'Output Log'

      If the check box is selected, do not output any log(avoid large volume of log output and reduce memory consumed)

      Supports pop up menu by mouse right key clicking monitor's log area;
      Ctrl+mouse click string '[user.]objectName[@dbLink]' of log area or amend area, will pop up a window to show a object's information.


    • Data Dictionary

      This tool supplies common data dictionaries including: Tables, Views, Indexes, Synonyms, Constraints, Procedures, Triggers and Objects.
      (locates in the right of monitor)

      Input string in text fields, then press button 'Search'(If text field is empty, search all).

      Text fields support wildcard '%' and '*' (delegates for a random string), for example, if input '%NAME' or 'NAME%' or '%NAME%', will search all names that includes sub string 'NAME'. Searched result displays in sheet under the text field. Can operate each selected object by sheet's popup menu.

      (Data dictionary's sheet operation is the same as Worksheet's, their popup menu's functions/operations are also very similar)

    • Message Bar/Progress Bar/Status Bar

      Message bar is used to display a single statement's result information(multiple statements' result information is displayed in monitor's log area), can copy message bar's all text by mouse right key clicking.
      Message bar has 3 font colors: if an error(or exception) message, then font color is red; if run normally(no errors), then font color is black; if SELECT statement's fact record number exceeds the value of field "Limit Selected Rows", then font color is blue;
      If too much message leads message bar can not display all text, or message text contains Enter key, will give a prompt 'Popup a message dialog by mouse double click' if mouse points to message bar.

      If a statement is running, progress bar is visible; If mouse points to progress bar, gives some prompt information including current statement's starting time, current statement is which statement number of a batch, how many records are already exported.

      If a statement is running, button 'Stop' is visible(appears on the right of progress bar). If current statement already run for long time, or other causes, if need stop/cancel running, only need press button 'Stop', the current stateement usually can be stopped in several seconds. But sometimes(exceptional case), current statement can not be stopped in long time after press button 'Stop'. If can not be stopped in 2 minutes, 'Stop' button's background color will become light yellow, it means that can forcibly stop the current statement if press again.

      Status bar is divided into 4 sections:
      The first section shows line/column position of SQL editor's cursor(There is a little downfallen deltoidal icon button on the right of the section, used to change cursor's line number);
      The second section shows Autocommit's status(can change Autocommit's status by satement 'set autocommit on' or 'set autocommit off');
      The third section shows Timing's status(can change Timing's status by statement 'set timing on' or 'set timing off');
      The fourth section shows Echo's status(can change Echo's status by statement 'set echo on' or 'set echo off')

Additional Statements

    [1] This tool's all text fields(single line text) and text area(multiple lines text) not only support common keyboard operations(including Ctrl+A, Home, End, Ctrl+Home, Ctrl+End, PgUp, PgDn, and so on), but also supplies the below particular keyboard operations:

      (1) Ctrl+D: in single line or multiple lines text editor, remove a line where cursor locates; in sheet, remove selected row(s);
      (2) Ctrl+I: in multiple lines text editor or worksheet, insert a blank row;
      (3) Ctrl+Backspace: backwards removes a string;
      (4) If multiple lines are selected: press Tab key to move right a block by 4 spaces; press Ctrl+Right to move right a block by 1 space; press Shift+Tab to move left a block by 4 spaces; press Ctrl+Left to move left a block by 1 space
      (5) If no lines selected or only one line selected: press Ctrl+Left, cursor will move backwards by a string; press Ctrl+Right, cursor will move forwards by a string
      (6£©Esc : firstly hide popup menu then close popup dialog
      (7£©If Mac system, key "Command+W" is to firstly close popup dialog, then close connection session one by one, finally close main window and exit system; key "Command+Q" is to firstly close popup dialog, then close main window and exit system.

    [2] This tool's below components support popup menu operations:
    SQL editor, worksheet, monitor's log area, data dictionary sheets, sheet of window 'desc objectName', session tag bar(under tool bar).
    Can know shortcut keys by popup menu or prompt of tool bar button(If Mac system, all "Cmd" in menus are shorthand of key "Command").

    [3] User can change tool's backgrond picture: Rename his/her liked picture file as background-picture.gif or background-picture.PNG, and put renamed file to this tool's working directory(the direcory where file OracleSQLHandler.jar is), then restart the tool.(Note: picture file's suffix must be GIF or PNG, if JPG or BMP suffix, just change it. Besides, picture size can not be smaller than 100x100, otherwise, system's default background pictrue will be used)

    [4] If user want to use statement "EXEC a_procedure()" to run or debug PL/SQL procedure, please edit and run the below PL/SQL in SQL editor:
    begin
    a_procedure();
    end;

    [5] Oracle JDBC supplied by this tool is version 11.2.0.4

    [6] This tool has the below default settings after onnected:

      NLS_DATE_FORMAT's default format is 'yyyy-mm-dd hh24:mi:ss',thus, to_char(sysdate) equals to to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'), to_date('2004-08-08 18:18:18') equals to_date('2004-08-08 18:18:18', 'yyyy-mm-dd hh24:mi:ss'). Can change its format by statement 'alter session set nls_date_format'

    Welcome to use this tool ! The author thanks for your valuable suggestions or advice, and welcome to leave word in the below web pages:
    http://blog.sina.com.cn/oraclesqlhandler
    http://www.onlinedown.net/soft/91179.htm
    http://www.duote.com/soft/17069.html
    https://www.crsky.com/soft/18077.html

All rights reserved by Mao Lingyan(Swan Mao), welcome to use !
WeChat: mz2250436518  QQ: 1078195462  E-mail: mao.swan@163.com

Scan WeChat QR code to add friend