ClearSQL 6.9

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.

„Legacy PL/SQL what is it?“Release HistoryCompany Flyer

ClearSQL 6.9 - PL/SQL analyzer, formatter, diagrammer and editor

Edit, format, analyze and fix your Oracle PL/SQL code imported from Oracle databases, SQL scripts or Oracle Forms into ClearSQL projects. Count metrics and enforce coding standards. ClearSQL 6.9 is a full-blown code analyzer and editor for legacy code maintenance. Analyze declarations, exceptions and the control flow. Remove anomalies. Fix and format your PL/SQL automatically. Use Flowchart legacy code to discover the logic and visualize the conditional branches, loops and jumps in order to see the visual patterns of your code. Generate Call Tree diagrams and CRUD matrices to discover data flows between subroutines and dataset objects due to DML statement execution and consistency of functional requirements and to identify performance problems. Generate a detailed, easy to filter and navigate and flexibly configured HTML report for a ClearSQL project. Write pseudocode in PL/SQL code for high-level and environment-independent description of the key principles of an algorithm.

Key features
Analyze PL/SQL code

ClearSQL 6.9 performs declaration analysis, DML analysis, cursor analysis, control flow analysis, procedure analysis, exception analysis and module analysis and displays the results in a Structure View, facilitating quick navigation through your code. Script Parser Status displays the status (New, OK, Error, Skipped and Modified) of the script and, for the OK status, its attributes such as Autofix, Code Review, Flagged Code Metrics, Pseudocode, CRUD1 matrix, Flowchart and Call Tree diagrams.

Automated code review

Catch bugs early in the development cycle and avoid common mistakes. Produce code that is easy to read, understand and maintain. Code review rules detect the misuse of GOTO, parameter declaration problems, unreferenced parameters, missing or misplaced RETURN, naming rule convention violations, unlabeled END, etc. The code review issues are structured hierarchically in the tree and are grouped by categories such as Program Structure, Readability and Maintainability. Each group's title shows the number of total Code Review rules in the group; the number of rules the script was checked against; and a number of violated rules.

Code Metrics

Determine the complexity of your system and highlight complex and hard to maintain code by flagged metrics. Identify potential problem areas based on complexity, size and modularity. ClearSQL 6.9 calculates four main metrics during analysis - Cyclomatic Complexity (McCabe v(G)), Maintainability Index, Halstead Volume, Interface Complexity - by using in their calculation other metrics such as Lines of Code (LOC), Effective LOC, Logical Statements LOC, Comment Lines, Blank Lines, Physical lines, Number of Input Parameters, Number of Return Points, Functional Complexity, Halstead Program Length, Vocabulary, Volume, Difficulty and Effort. Draws a red flag when the Cyclomatic Complexity or Halsted Volume or Interface Complexity metric value is higher or the Maintainability Index metric is lower than the value defined in Code Metrics Options. Export Code Metrics to HTML, Excel, XML and CSV formats.

Enforce coding standards

Improve readability and standardize code written by others. Enforce naming standards 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. Save and load templates from a flat file.

Bi-directional ("source2project" and "project2source") code synchronization

Create a new ClearSQL 6.9 project or import files and db objects with the ability to link project items (script and folders) with original source objects (files and db objects). 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.

Project analysis summary

Select the project root node in the Project Tree and take a 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 the "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; a list of packages by average subprogram Cyclomatic Complexity, a detailed list of Parser Errors, a list of Code Metrics, such as Cyclomatic Complexity, Maintainability Index, Halsted Volume, Interface Complexity, etc. per subprogram; top violated Code Review rules and more. The Summary page layout is fully customizable and its contents can be easily exported to HTML, Excel, XML and CSV formats or printed out.

Project reports

Generate a detailed and clickable and easy to filter and navigate and flexibly configured HTML report for a ClearSQL project, including parser status, analysis information, Flowchart and Call Tree diagrams, CRUD1 and CRUD2 type matrices. Create highly customizable project specific or non-project specific report templates for reuse and faster report generation. Enable generation of the 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 never analyzed scripts. Automate report generation by scheduling it in Job Manager or in a third party scheduler.

Flowchart diagrams

ClearSQL 6.9 takes a package or a stand-alone subroutine and makes a set of clickable 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. Exports Flowcharts into image (GIF, PNG and JPEG), MS Visio drawing "vdx" and HTML formats.

Call Tree diagrams

ClearSQL 6.9 draws subroutine calls and called-by path of PL/SQL code in Call Tree diagrams. As PL/SQL is heavily coupled with data, the Call Tree diagram is enriched with data flows between subroutines and database tables due to DML statement execution. 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. Exports Call Tree diagrams into image (GIF, PNG and JPEG), MS Visio drawing "vdx" and HTML formats.

CRUD matrices

A CRUD (Create, Read, Update, Delete) matrix is the perfect aid to analyze the consistency of functional requirements and to identify 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. Both CRUD matrix types are fully clickable. Exports CRUD matrices into an HTML format.

Automated project analysis

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.

Export and Import projects, project scripts and project diagrams

Export and import a project with its contents as a flat zipped file with an ability to send it by email for simplified sharing of multi-file projects between users. Export and import a single or multiple project scripts with a variety options into the file system as files. Export a single or multiple project Flowcharts and Call Tree diagrams into image (GIF, PNG and JPEG), MS Visio Drawing "vdx" and HTML formats with the ability to keep the diagram tree structure in the target Windows folder.

Auto fix known PL/SQL problems

ClearSQL 6.9 automatically fixes 9 of analyzer's rules accelerating code development, for example: adds missing END and LOOP labels, defines IN parameters and re-parenthesizes complex operations.

Format your code automatically

Set margins and indentation. Insert blank lines. Apply standard casing to keywords and identifiers. Tidy up procedure headers. Align SQL statements.


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.

Support for PL/SQL of Oracle Forms & Libraries

ClearSQL 6.9 support for PL/SQL code of Oracle Forms versions 6 - 11g. 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.

Project analysis history

ClearSQL 6.9 tracks changes to the project content and analysis information at a project or script level. Each analysis creates a new "snapshot" storing a full "picture" about the project contents and its script analysis values in categories such as the number of folders and scripts and their statuses ("OK", "Error", "Skipped"); the total values of Cyclomatic Complexity, Maintainability Index, Halstead Volume, Interface Complexity and per subprogram, object and script; the number of alerts such as errors, autofixes, code review items, flagged code metrics; the number of lines of code, comments and blank lines; the number of CRUD, Flowchart and Call Tree diagrams; 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, Excel, XML or CSV formats.

Code editor with syntax highlighting

The syntax-highlighting and code folding editor supports SQL and PL/SQL code and allows creating different versions of the same code. It provides power features such as code folding, code explorer, bookmarks, comment out and uncomment a block of code, auto completion and code templates.

More significant features
  • New Project Assistant creates ClearSQL 6.9 projects based on files and db objects easily with a few clicks.
  • Project Tree Manager filters and sorts the project scripts by a variety of predefined filters and sorting rules and also displays script properties and legend.
  • Built-In Version Control System manages multiple versions of scripts for easy change tracking and retrieval.
  • Preprocessor directives let you generate different versions of the same code and include/exclude debug code. Support for Oracle Conditional Compilation directives.
  • Syntax Checker verifies your PL/SQL syntax offline without executing it in Oracle.
  • Active workspace enables full-blown customization of the position, size and visibility of GUI elements.
  • Toolbar Customizer provides an easy-to-use GUI to customize the visibility and position of toolbar buttons.
  • Find in Project lets you search any text occurrence in scripts and project tree nodes.
  • Logs on to an Oracle database by using TNS, LDAP and Direct connections and remembers last connections.
  • Online Support Desk communicates with Technical Support and keeps ClearSQL 6.9 up to date with its auto update feature.