Is Your PL/SQL Code as Good as You Think? Scan it with SQLcl CODESCAN, please.

When working with applications based on Oracle Database (like APEX), ensuring the quality of your PL/SQL code is essential. But how can you quickly check for issues?

The answer is the SQLcl CODESCAN. A FREE tool that scans your database objects for coding standard violations and performance problems.

What do you need?

• Latest SQLcl installed (24.4 at the moment of this blog post). How to install it → here. Official documentation here.

• Objects you wish to scan exported, preferred .sql format (I used project export command to export my HR schema). How to use project export → here.

Run CODESCAN

Ok, I have exported all my HR schema objects. Obviously, some of them are not PL/SQL, but I just want to scan everything. Especially for this blog, I’ve put some bad code into hr_job_pkg_body.sql

1. Go to /HR/ schema objects locations

2. Run SQLcl (you don’t need to be connected to any database!)

   sql /nolog

3. Run CODESCAN:

codescan -format txt - output hr_scan.txt

You can have results in JSON format, specify a path as a parameter or ignore some rules - you can read more about it here.

CODESCAN results & Rules checked

SQLcl CODESCAN scanned much of my code, including packages, procedures, functions and views.

Numbers in brackets are (Line number, column)

All rules starting with “G-” are rules you may know from the Trivadis Guidelines, and those that start with “PSR-” are performance issues stated in Oracle’s documentation.

***** /hr/procedures/secure_dml.sql
*** 2 distinct warnings
Warning (1,19): G-7310: Avoid standalone procedures – put your procedures in packages
Warning (5,34): G-5050: Avoid use of the RAISE_APPLICATION_ERROR built-in procedure with a hard-coded 20nnn error number or hard-coded message
***** /hr/procedures/pkg_job_history.sql
*** 6 distinct warnings
Warning (1,19): G-7310: Avoid standalone procedures – put your procedures in packages
Warning (2,5): G-7160: Always explicitly state parameter mode
Warning (3,5): G-7160: Always explicitly state parameter mode
Warning (4,5): G-7160: Always explicitly state parameter mode
Warning (5,5): G-7160: Always explicitly state parameter mode
Warning (6,5): G-7160: Always explicitly state parameter mode
***** /hr/procedures/add_job_history.sql
*** 6 distinct warnings
Warning (1,19): G-7310: Avoid standalone procedures – put your procedures in packages
Warning (2,5): G-7160: Always explicitly state parameter mode
Warning (3,5): G-7160: Always explicitly state parameter mode
Warning (4,5): G-7160: Always explicitly state parameter mode
Warning (5,5): G-7160: Always explicitly state parameter mode
Warning (6,5): G-7160: Always explicitly state parameter mode
***** /hr/package_bodies/hr_job_pkg_body.sql
*** 9 distinct warnings
Warning (4,9): G-7160: Always explicitly state parameter mode
Warning (5,9): G-7160: Always explicitly state parameter mode
Warning (6,9): G-7160: Always explicitly state parameter mode
Warning (7,9): G-7160: Always explicitly state parameter mode
Warning (8,9): G-7160: Always explicitly state parameter mode
Warning (11,9): G-1030: Avoid defining variables that are not used
Warning (26,13): G-3145: Avoid using SELECT * directly from a table or view
Warning (27,9): G-5060: Avoid unhandled exceptions
Warning (31,13): PSR-103: Too many values in IN-LIST filter may prevent the optimizer from finding the more efficient plan
***** /hr/package_spec/hr_job_pkg_spec.sql
*** 5 distinct warnings
Warning (4,9): G-7160: Always explicitly state parameter mode
Warning (5,9): G-7160: Always explicitly state parameter mode
Warning (6,9): G-7160: Always explicitly state parameter mode
Warning (7,9): G-7160: Always explicitly state parameter mode
Warning (8,9): G-7160: Always explicitly state parameter mode
***** /hr/triggers/secure_employees.sql
*** 1 distinct warnings
Warning (2,5): G-7730: Avoid multiple DML events per trigger
***** /hr/views/emp_details_view.sql
*** 1 distinct warnings
Warning (37,9): G-3130: Try to use ANSI SQL-92 join syntax

Bonus

Interactive feedback while you’re writing your code

Ensure the CODESCAN feature is ON by typingset codescan on

Type some code:

Unofficial extension for SQLcl Codescan by Niko Sperat

I found this in the VS Code extensions marketplace, and I love it!

Now, I can edit my PL/SQL in VS Code, and this extension on LIVE shows me all the bad things in my code.

Summary

I don’t want you to encounter many issues in your code, but we are all learning constantly :)