Oracle SQL Handler, is an Oracle development tool(client tool) , is very useful and helpful for Oracle database developers and operators. convenient, functional, its advantages and good features as below:
(1) Platform crossing, can run in platforms Windows(WIN8,XP...), Linux and Mac OS.
(2) Does not needs install Oracle client, can directly use after decompress, can directly connect to Oracle server by offered JDBC package.
(3) Super intelligent SQL editor, popup prompt window to list key words, key word unions, function names, column names and types, object names and types, can much improve the efficiency in SQL writing.
(4) Super convenient worksheet to show and operate SELECT statement's searched result(can easily select row/column/cell like Excel sheet operations, can save updated data to database table).
(5) Multiple types of data export including XLS\CSV\INSERT Statement\HTML\ XML.
(6) Can easily operate data dictionaries including tables, views, indexes, procedures, triggers(can show/edit/compile PL/SQL code).
(7) Chinese/English double-language GUI and can switch momentarily, individuation, compact and salubrious GUI.
(8) Run statement "DESC tableName" to clearly show table's all logical structure informations including column info, all constraints, indexes, triggers and child tables.
(9) Run statement "SCHEMA objectName" to show table or view's original DDL code.
(10) Can clearly show SQL's explain plan with indent ladder format.
(11) Can run and debug PL/SQL code, can accurately locate at error row/column.
(12) Beautify and format SQL with indent ladder format.
(13) Multiple threads and multiple connections, can stop or edit or create a new session when a session is running.
(14) Can monitor executing batchs of SQL statements(can pause and amend in running progress).
(15) Simple connection config, can automatically reconnect.
(16) Engross few system resource, start quickly, connect quickly, run quickly.
(17) High efficiency block operations, highlight paired parentheses.
(18) Functional functions, simple and convenient operating.
......
Main functions as below:
Use a worksheet to display SELECT statement's searched result, can again operate on the searched result, such as modify, insert/delete row(s), commit changes(save all updating to database table), multiple-function copying, can export selected cell data to XLS /CSV /INSERT Statement /HTML /XML files, single record operating, can convenient search/replace cell data, number statistic, row/column moving, column sorting, selection mode switching; can expediently view/edit data of large text fields(like CLOB, LONG)
Intelligent SQL editor: automatically pop up prompt window, list keywords, keyword union, function names, column names, object names, object types, parameters, function and procedure of a package. so, it can much improve the efficiency in SQL writing, for example:
(1) Input string 'sel', 1 second later, list all keywords and keyword union and function names and object names that starts with 'sel';
(2) Input string 'abc'+'.', if 'abc' is a table name or view name, or its alias, then list table or view's all column names and data types; if 'abc' is a user name, then list the user's all object names; if 'abc' is a package name, then list package's all procedure names and function names;
(3) Input string 'grant'+space, then list all privileges and rols; if again input 'on'+space, then list all object names; if again input 'to'+space, then list all user names.
Colorful SQL editor, improve readability :
(1) Use different colors to differentiate from keywords, comment blocks, strings in quotation marks, operators, special characters;
(2) If cursor locates after a bracket, points out a couple of conjugated brackets with highlight
Can execute batchs of SQL statements: monitor each SQL statement running, can stop/pause/resume a SQL statement in running process, even can amend error statement
Convenient block operations for special requirement:
(1) Button 'Format SQL' can beautify and format and rearrange tanglesome statements of a block;
(2) Button 'Explain Plan' can quickly display a SQL statement's explain plan;
(3) Button 'Export' can convert SELECT statement's result to INSERT statements for data migrating and backuping;
(4) support column block operations;
(5) add/remove comment marks, add/remove quotation marks, increase/decrease indents, upper/lower case converting and so on
Run statement 'desc [user.]objectName[@dbLink]' or 'Ctrl' key+mouse clicking string '[user.]objectName[@dbLink]', if a table name, then distinctly display a table's column info(including column names, data types and length, default value, not null), indexes, constraints(including primary key, unique key, check key, child table, trigger, foreign key, even foreign key's foreign key)
Run statement 'schema [user.]objectName[@dbLink]', if a table name or view name or materialized view(snapshot), can display its generated SQL scipts
Can run and debug PL/SQL statement(including Procedure, Function, Trigger, Package), can display the results of DBMS_OUPTPUT.PUT_LINE; and give error message, even curror can accurately locate at row and column position where error is, for easy debugging
Multiple threads, multiple connections: Can create multiple sessions/connections, multiple sessions can run simultaneity, can control sessions' running status in running process, can momentarily stop it, can work on editing in running process
Supports common data dictionary operations(like Tables, Views, Indexes, Synonyms, Constraints, Procedures, Triggers). For example, input '%NAME' or 'NAME%' or '%NAME%', can search out current user's or all users' all object names that contains part string 'NAME', can operate each object via popup menu
For Windows operation system(WIN8,XP,Vista,NT...): For Linux operation system: For Mac operation system:
Please read the decompressed file "How-to-start-in-Windows.txt"
Notes: The downloaded ZIP file from internet is about 25MB, the ZIP file already contains Windows JRE(version is 1.6.0_30, in directory JRE1.6-for-Windows), then can start/run this tool by mouse double clicking file start-in-Windows.bat, or run the command D:\OracleSQLHandler\JRE1.6-for-Windows\bin\javaw -jar D:\OracleSQLHandler\OracleSQLHandler.jar in CMD window(assuming the ZIP file was decompressed to directory "D:\OracleSQLHandler").
It is required that JRE version is 1.6 at least, offered JRE in ZIP file is version 1.6.0_30(only 21MB), it is suggested to use the offered JRE, as it can support Windows 7's color scheme.
Please read the decompressed file "How-to-start-in-Linux.txt"
Please read the decompressed file "How-to-start-in-Mac.txt"
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)))
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 Worksheet Monitor Data Dictionary Message Bar/Progress Bar/Status Bar
# 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.
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.
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.
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 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')
[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:
[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:
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 |
![]() |