ClearSQL 6.9
  AT A GLANCE  |  FULL DESCRIPTION  |  WHY ClearSQL |  SCREENSHOTS 

ClearSQL 6.9 is a code review and quality control tool for Oracle PL/SQL. Its code review feature gives recommendations for better coding style and checks for error-prone places in your project.

ClearSQL 6.9 generates a series of industry standard quality control metrics (Cyclomatic Complexity, Maintainability Index, Halstead Volume, etc.) about PL/SQL code to identify potential risks and to get better insight into the code.

ClearSQL 6.9 creates and visualizes "clickable" Flowcharts and Call Tree diagrams and CRUD1 and CRUD2 type matrices of PL/SQL code that help you find the point of possible code refactoring or module restructuring, discover data flows between subroutines and dataset objects due to DML statement execution, and analyze the consistency of functional requirements and to identify performance problems.

ClearSQL 6.9 analyzes thousands of PL/SQL scripts and Oracle Forms PL/SQL with only a few clicks. It provides a quick look at the most important analysis summaries and tracks changes to PL/SQL code and analysis results.

WHITE PAPER
„Legacy PL/SQL what is it?“Release HistoryCompany Flyer
ClearSQL 6.9 Screenshots
Synchronize your ClearSQL project content and the linked source objects (file/DB object) in both directions

New: Bi-directional ("source2project" and "project2source") project and linked source synchronization

Whenever you create a new ClearSQL project, including scripts from Windows or DB objects from a database or import objects to an existing ClearSQL project, you never lose the link between objects in the ClearSQL project and source objects (File/DB). The link keeps the relationship between the script/folder in the project and the original source object. It allows you to synchronize the content of the project object (script/folder) against the original source object and vice versa. Use Refresh to actualize the content of project objects against the linked source and Write Back to actualize the content of the original source object against the project object.

Code review for checking program structure, readability and maintainability violations

ClearSQL catches bugs early in the development cycle and helps the user to avoid common mistakes and to produce code that is easy to read, understand and maintain. 81 code review rules detect the misuse of GOTO, parameter declaration problems, unreferenced parameters, missing or misplaced RETURN, unlabelled END, etc.

The Code Review tab displays violated code review rules for the selected script grouped by Program Structure, Readability and Maintainability. Click any element within a list and the relevant line of code will be highlighted in the Script editor. The Code Review tab is applicable and enabled only for scripts with parser status OK or Modified.

Enforce coding standards by using naming rules with regular expressions

New naming template groups and dual template pattern definition mode

Improve readability and standardize code (yours or legacy) to meet corporate coding rules. Define unlimited set of naming rule patterns for package names, subprogram names, type names, table/view/synonym names, parameters, identifiers, etc., enforcing them with regular expressions.

Dual template pattern definition mode allows users who are not familiar with the Regular Expression syntax to enter the naming rule pattern in a simple and convenient way; and, for experienced users, it gives the full freedom and power of the "Regular Expression" syntax.

Oracle Conditional Compilation directives are supported by Parser

New: Support for Oracle Conditional Compilation directives

The enhanced version of ClearSQL's Parser support for Oracle Conditional Compilation directives, implemented in Oracle 10gR2. The Oracle Conditional Compilation feature enables a developer to impose conditions on the compiler. This implies that out of the complete source code of a program unit, only the part of code, which qualifies these conditions, would be compiled. ClearSQL incorporates its own Preprocessor that has different syntax. Both Preprocessor directives can be used in code simultaneously.

Import Oracle Forms & PL/SQL libraries and analyze, fix and illustrate (code Flowcharts, Call Tree diagrams and CRUD matrices) their PL/SQL code

Support for Oracle Forms&PL/SQL libraries versions 6 - 11

ClearSQL support for PL/SQL code of Oracle Forms and PL/SQL libraries versions 6 - 11. Import Oracle forms and PL/SQL libraries into a ClearSQL project and analyze, format, fix and illustrate their PL/SQL code. Drill down the project tree to discover the content of forms&libraries such as Blocks, Program Units, Triggers as well as graphically shown canvases.

Generate a series of industry standard quality control metrics and highlight the metrics which violate the critical boundary values

Accordion style table that highlights the flagged (violated) Code Metrics

Software metrics are measures that are used to quantify software and to identify areas of code that can potentially cause problems or errors before software is released. These areas include items that are directly measurable, such as lines of code (LOC), as well as items that are calculated from measurements, such as source lines of code per staff-hour.

ClearSQL calculates four main metrics during analysis - Cyclomatic Complexity, Maintainability Index, Halstead Volume, Interface Complexity - by using in their calculation other metrics such as Lines of Code (LOC), Number of Return Points, Functional Complexity, Halstead Program Length, etc. ClearSQL red flags the main metric when its value is out of the boundary critical value defined in Code Metrics Options. Export Code Metrics to HTML, MS-Excel or CSV formats.

Create and view clickable Flowcharts

ClearSQL takes a package or a stand-alone subroutine and makes a set of Flowchart diagrams that visualize the code execution path. Such diagrams show the conditional branches, loops and jumps, thereby helping to understand the opaque logic. The visual patterns help to find the points of possible code refactoring or module restructuring and make the reasons for high values of Cyclomatic Complexity metrics obvious.

Clicking an element of a Flowchart loads the related script into the editor and highlights the relevant line of PL/SQL code.

Remark: "clickable" = loads the source code and highlights the relevant line of code.

Generate and view clickable Call Tree diagrams

ClearSQL creates clickable Call Tree diagrams from any PL/SQL code. A PL/SQL Call Tree diagram is a perfect aid for reading and understanding the data flow of legacy code. A Call Tree diagram draws subroutine calls and called-by path of PL/SQL code.

Clickable data flows show how subroutines get data from data objects (table, view) with SELECT INTO statements; how they put data back with INSERT or UPDATE statements and how they delete data with DELETE statements. Click any element within a diagram, and the related code will automatically be loaded and the relevant line of code will be highlighted in the Script Editor.

CRUD matrices

New: Filters the CRUD matrices by CREATE/READ/UPDATE/DELETE statements.

The CRUD tab displays CRUD1 and CRUD2 type matrices generated by internal Parser (Code Analyzer) during analysis. The CRUD tab is applicable to and enabled only for scripts with parser status OK or Modified.

A CRUD (Create, Read, Update, Delete) matrix perfectly aids in analyzing the consistency of functional requirements and in identifying performance problems. CRUD incorporates two matrix types: "Script Level" CRUD1 type matrix and "Project Level" CRUD2 type matrix to illustrate the relationships between processes and the data they use. Matrices belonging to the script selected in the Project Tree are shown on the CRUD1 tab and are called "Script Level" matrices, while matrices belonging to the whole project are shown on the CRUD2 tab and are called "Project Level" matrices. Both CRUD matrix types are fully clickable.

CRUD and Call Tree

Clicking a data element within a "Matrix 1: Data Object" or a "Matrix 2: Data Object / Stored Object" matrix generates a Call Tree diagram for this element and displays it in a floating panel divided by a vertical splitter.

There are easy ways for the user to individually adjust the color settings for CRUD and Call Tree diagrams, to print a single CRUD matrix or to print all CRUD matrices of the whole project as well to customize the caption style for a CRUD matrix.

Investigate code structure with Structure View

The Structure View tab displays the structure of code hierarchically in the tree grouped by categories such as Declaration Analysis, DML Analysis, Cursor Analysis, Flow of Control Analysis, etc. The tree is built by the internal Parser (Code Analyzer) during analysis and is applicable to and enabled for scripts with parser status OK or Modified. Clicking an item in the list highlights the corresponding line of code in the Script Editor.

It can be very time-consuming to locate desired program objects (constant declarations, etc.) or certain program constructs (e.g. CASE statements) when your package body is relatively large. The Structure View can significantly facilitate the process of navigation through your code. In addition to the quick navigation service, the Structure View helps evaluate the quantitative data of your code - the number of certain language constructs, the nesting level of LOOPs and IFs, etc.

View and explore project/script level diagrams and analysis history separately

Analyzer View splits diagrams and analysis history at project and script levels

The Analyzer View pane is intended for displaying analysis information (code metrics, code review, autofixes, structure view), Flowcharts, Call Tree diagrams, CRUD matrices and analysis history and gives access to their features.

Flowcharts, Call Tree diagrams and analysis history are scalable at project and script levels. For ease of use and for focusing on a specific information layer, the new GUI of Analyze View splits the project and a script diagrams and analysis history into different panes and tabs.

Analyzer Progress window

The Analyzer Progress window displays and controls the process flow of project analysis in percentage of completion; specifies the analysis settings; displays detailed information about script parser status, such as OK, Error, Skipped; and script attributes, such as Autofix, Code Review, CRUD1 matrix, Flowchart and Call Tree diagrams and analysis; and matrix/diagram generation flow statistics.

Subprocesses such as Flowchart and Call Tree generation may take a longer time than others and depend on the complexity of the source code. The CRUD1, CRUD2, Flowchart and Call Tree check marks located on the buttons toolbar are additional indicators to show which subprocesses will be included in the analysis process.

Autofixes

The Code Audit | Autofix tab displays autofixes (best practice recommendations that can be automatically fixed to improve code readability) detected by the internal Parser (Code Analyzer) during analysis. The Code Audit tab is applicable to and is enabled only for scripts with parser status OK or Modified.

ClearSQL 6.9 detects missing END and LOOP labels, defines IN parameters, reparenthesizes complex operations and marks such objects in the Project Tree with status "OK -> Autofix". Such minor code deficiencies are easily fixed, making your code clean and easier to read.

Syntax Errors

The Errors tab displays a list of syntax errors of the selected in the Project Tree script with status Error detected by the internal Parser (Code Analyzer) during analysis or syntax checking. Clicking an error line highlights the corresponding error line in the Script Editor.

If the script's status is OK, then its code is syntactically correct from the internal Parser's point of view and, in that case, the list shows Parser status: OK — no errors in the center of the list.

Discover analysis summary of ClearSQL projects, folders and scripts

New: Groups information by summary categories on predefined subtabs. It allows you to create user-defined groups and reallocate the summary panes between different groups.

Select the project root node in the Project Tree and look at the Summary page to discover the most important analysis information shown graphically as pies, bars and grids at a project level. Drill down the project folders/scripts to see "Summary" at a folder/script level.

The Summary page displays the number of folders and scripts and their statuses ("OK", "Error"); the number of CRUD matrices, Flowcharts and Call Tree diagrams; the total number of errors, autofixes, code review items, flagged code metrics; the total number of lines of code and comments; the total value of Cyclomatic Complexity as well as grouping by method/class/script; a list of top complex objects by Cyclomatic Complexity and much more. The Summary page layout is fully customizable and its contents can be easily exported to HTML, MS-Excel, CSV and XML formats or printed out.

Create new projects easy with New Project Wizard

New: Links the project objects (script/folder) with the original source objects (file/db object), allowing you to synchronize the project and linked sources bi-directionally

New Project Wizard provides you with an easy-to-use and powerful graphical interface for creating new projects. You can add files from any storage device or add database objects directly from any Oracle database. It is recommended to add & link the objects to the project to be able to synchronize the project objects (script/folder) with linked source objects (file/db object) bi-directionally at a later time.

Export project scripts

Project Scripts Export Wizard exports project scripts with a variety of options into the file system as files. You can export a single script, a set of scripts, or all scripts of the project at a time.

Import scripts and db stored objects

New: Links the imported objects (script/folder) with original source objects (file/db object) allowing you to synchronize the project and linked sources bi-directionally

Import Scripts and Stored Objects Wizard imports files with a variety of options from the file system and/or Oracle database stored objects as project scripts into a ClearSQL project. You can add files from any storage device or add database objects directly from any Oracle database. It is recommended to add & link the objects to the project to be able to synchronize the project objects (script/folder) with linked source objects (file/db object) bi-directionally at a later time.

Export the project diagrams to GIF, PNG, JPEG and MS Visio Drawing formats

Project Diagrams Export Wizard is a powerful and easy-to-use tool designed to export Flowcharts and Call Tree diagrams of the project into image (GIF, PNG and JPEG), MS Visio Drawing "VDX" and HTML formats. You can export a single or a set of diagrams or all diagrams of the project at a time.

Generate a detailed, clickable and easy to filter and navigate and flexibly configured HTML report for a ClearSQL project

New: Generate a ClearSQL Report for selected scripts in the Project Tree. The improved report title allows you to collapse it to provide more space for the report details.

Project Report Assistant generates a detailed, clickable and easy to filter and navigate and flexibly configured HTML report for a ClearSQL project, including Parser Status, Code Review and Structure View information, Flowchart and Call Tree diagrams, CRUD1 and CRUD2 type matrices. Include Report Observations to highlight the scripts with Parser and DB errors and Alerts (Code Review, Autofix issues and flagged Code Metrics) as well as modified, skipped and non-analyzed scripts.

Create highly customizable project specific or non-project specific report templates for reuse and faster report generation. Automate report generation by scheduling it in the Job Manager or in a third party scheduler.

Automated project analysis (Job & Schedule Manager)

ClearSQL 6.9 automates analysis of ClearSQL projects or scripts and/or stored objects via its scheduled jobs.

Create a job for analysis of an existing ClearSQL project or embedded ClearSQL project that can be a selection of scripts and/or stored objects. Schedule the job in ClearSQL 6.9 by creating one or more integrated schedules.

Each job is fully customizable in terms of GUI, Code Analyzer, and grouping options and allows generating a ClearSQL report and exporting analysis data to HTML, Excel, XML, CSV formats.

Specify analysis, naming convention, diagrams and formatting options in Code Analyzer Options

The Code Analyzer Options window provides an easy interface to the Code Analyzer settings that are grouped by features and are displayed in the navigation tree. There is a group of settings on the Formatter Options page to tune the PL/SQL Formatter. Code Review Options, Naming Rule Templates, Code Metrics Options, Flowchart and Call Tree diagrams and CRUD matrix options are available on the corresponding pages.

ClearSQL project/scripts analysis and formatting uses the currently loaded Code Analyzer options. Define and save different sets of options for a specific project or different projects and load them easily when needed.

Organize scripts in the Project Tree hierarchically and walk through in Explorer-like manner

New: The Project Tree has been moved to a tab "Project Tree" to separate it from the Project Manager.

The Project Tree is intended for showing and operating with open ClearSQL projects. It allows organizing scripts in a ClearSQL project hierarchically in a structured way, which reflects the business or system areas to which they belong. Create any number of folders at different levels without limitation to meet your requirements.

The Project Tree has two columns: Folders and Scripts and Parser Status. The Folders and Scripts column displays the project folders and scripts hierarchically and the root folder of the tree is the ClearSQL project name. The Parser Status column displays the status of the script such as OK, Error, Skipped, New, and Modified and Script Parser attributes such as Autofix, Flagged Code Metrics, Pseudocode, CRUD1, Flowchart and Call Tree.

Filter and sort the Project Tree, view the script properties and project analysis statistics

New: The Project Manager has been moved to a separate tab "Project Manager" – next to the Project Tree tab.

The Project Tree Manager is located under the Project Tree, is divided by a horizontal splitter, and is designed for managing the Project Tree: filtering and sorting the project scripts by a variety of predefined filters and sorting rules; as well as for displaying the properties of the selected item, the legend and analysis statistics.

The colored background of the Project Tree indicates that the tree is filtered. Tree filtering is possible by Parser Status (OK, Error, Skipped, etc.), by script parser attributes (Autofix, Code Review, Flagged Code Metrics, Call Tree, etc.), by a set of Code Review rules, by script name and script content.

Syntax-highlighting and code folding Script Editor

Advanced Script Editor

ClearSQL incorporates a powerful syntax-highlighting and code folding editor that allow you to quickly create and understand SQL and PL/SQL code.

Script Editor includes important editing features such as SQL syntax highlighting, code folding, embedded Code Explorer, smart code completion, dot suggestion and stored object arguments hint by Code Insight, a powerful search engine, convenient bookmarks, upper or lower case character conversion, finding a matching pair, commenting and uncommenting blocks of text, and more.

Rapid code-building tools like Code Explorer, Code Insight and user templates save time, reduce errors and ensure a consistent programming style. A set of advanced Cut, Copy, Paste features will improve the productivity of any programmer by reducing the number of keystrokes required.

Track and compare changes to the project content and analysis information

Project analysis history

ClearSQL tracks changes to the project content and analysis information at project and script levels.

Each analysis creates a new "snapshot" storing a full "picture" about the project contents and script analysis values in categories such as a number of folders and scripts and their statuses ("OK", "Error", "Skipped"); a number of CRUD, Flowchart and Call Tree diagrams; a number of alerts, such as errors, autofixes, etc.; a number of lines of code, comments and blank lines; etc.

The Analysis History page displays analysis "snapshots" indicating a negative or positive direction of the value change. Select one or more categories to draw diagrams of changes to analysis values. Analysis history content can be easily exported to HTML or CSV formats.

Retain multiple versions of script with built-in versioning

Unlike traditional Explorer type organizers, ClearSQL 6.9 can retain multiple versions of each script for easy retrieval. For example, assume that you open an existing script as a template for a new project you are working on. The application allows you to create a new version of this script with a few simple clicks and creates a new version automatically to embed instrumented code. After making your changes, you can save the script without losing the original version.

Write pseudocode for a high-level description of the key principles of an algorithm

Support for pseudocode based on structural conventions of PL/SQL

Pseudocode is a compact and informal language for high-level description of algorithms. Pseudocode is a type of structured english used for describing algorithms that allows the designer to focus on the logic of the algorithm without being distracted by the details of language syntax. The reason for using Pseudocode is that it is easier for understanding than conventional programming language code, as it is an efficient and environment-independent description of the key principles of an algorithm. In ClearSQL 6.9 it uses the structural conventions of the PL/SQL language but is intended for human reading rather than machine reading. Pseudocode in ClearSQL 6.9 is based on writing comments above code lines.

Trace code with Instrumented Code

Instrumented Code is a powerful feature of ClearSQL 6.9 that locates and identifies bugs in code on the Package, Subroutine and Line of Code levels by adding trace statements bracketed with a pair of conditional directives. Instrumented code entries of stored objects are populated during script analysis and are shown hierarchically on the Instrumented Code tab of the Analyzer View pane for the selected script in the Project Tree.

Back up and restore the project as a flat compressed file

Export Project to Project Backup and Import Project from Project Backup

A ClearSQL project is a complex system that consists of a number of files of different formats stored in different Windows folders. Export Project to Project Backup is a tool designed to export/back up a project with its contents (scripts, diagrams, CRUD matrices) as a compressed single flat file.

Import Project from Project Backup is a tool designed to import a project with its contents (scripts, diagrams, CRUD matrices) from a project backup file created by Export Project to Project Backup back to ClearSQL 6.9.

Back up and restore the project as a flat compressed file

Export Project to Project Backup and Import Project from Project Backup

A ClearSQL project is a complex system that consists of a number of files of different formats stored in different Windows folders. Export Project to Project Backup is a tool designed to export/back up a project with its contents (scripts, diagrams, CRUD matrices) as a compressed single flat file.

Import Project from Project Backup is a tool designed to import a project with its contents (scripts, diagrams, CRUD matrices) from a project backup file created by Export Project to Project Backup back to ClearSQL 6.9.

Project analysis statistic

Project analysis statistic

All analyses performed on a ClearSQL project with detailed information are stored by the application and displayed on the Statistics tab of the Project Tree Manager.

The last analysis information of the project is shown on the Last Analysis subtab and it displays data such as started, finished and elapsed time of analysis, analysis result, total number of scripts included in the analysis, total number of scripts with OK, Error, etc. statuses, total number of generated diagrams and CRUD matrices. To see the total analysis information, go to the Totals subtab where the first and last analysis date are shown with the total number of performed analyses.

Project Analysis History Report exposes the complete project analysis history for each analysis separately. The report can be saved in an HTML file or printed.

Search text in projects with Find in Project

New: Improved GUI with Instant Help for search options

Find in Project is a handy feature that lets you search any text occurrence in scripts and folder names and script content. The project search result is shown on the optional Search tab that appears in the Analyzer View pane on search start.

Search for text in the ClearSQL project by Occurrence, Regular Expression and Wildcard methods. It provides a wide set of search options to meet the user's needs.

You can keep each search result in its individual subtab. Pin the subtab to have a new search result populated in a new subtab.

Customize toolbar buttons and their positions

Toolbar Customizer

All application toolbars are customizable, meaning that you may show/hide the toolbar buttons and change their positions. Right-click the toolbar you want to customize and select Customize the Toolbar to open the Customize Toolbar window. Use customization buttons to show/hide and to change a button's position in the toolbar.

Contact Technical Support and update ClearSQL 6.9 with OSD — Online Support Desk

OSDOnline Support Desk — is a built-in tool that gives ClearSQL 6.9 users an easy way to automatically update the software (OSD Updater) and directly contact Technical Support (OSD Messenger) for help.

^