When I was working as a developer I was working with different companies and each one had their own rules/best practices about the pl/sql code. Some of them were the same and some were exactly the opposite: not better or worse...just different. In these cases it is hard to remember which one is the correct format on each place.

Back then we had a huge Word document with all the rules to follow...I really wish I had Code Analysis then!
With Code Analysis I could have selected the rules needed, modify them if required and if some were missing, ask for it. My life would have been easier!

Hold on...Don't you know what I am talking about? I am talking about Code analysis.

If you are not familiar with that or you just want a refresh, please have a look at the videos below. In this post I will assume you already know about this great feature .

 

   

 

 

In this post we will create five new rules and I will provide the XPath expression for them.
The rules our fictitious company wants us to follow are:

Make sure Prefix Schema name exists for all objects.
Table names should be under 26 characters.
When creating index or table specific Tablespaces should be used.
When creating table, Primary key should be created.
Columns type blob and timestamp are not allowed.

So let's start!

First go to View | Toad options | Code Analysis |General and, on the right panel select Edit Rules

 


Click on Create New Rule

You can create rules by numbering them in the 7000 - 9999 range. This range is reserved for customer usage, and Quest will never write or overwrite anything there.
As you can see you can select the Severity, Code Correctness and Category for each rule.
In this case I will select the default one.

 

 

The first one is "Make sure Prefix Schema name exists for all objects". In this example the prefix will be za_
The XPath expression is:

//CREATE/ (CLUSTER, DATABASE_LINK, EDITION, FUNCTION_BODY, INDEX, JAVA, MATERIALIZED_VIEW, MATERIALIZED_VIEW_LOG, MATERIALIZED_ZONEMAP, PACKAGE_BODY, PACKAGE_SPEC, PROCEDURE_BODY, SEQUENCE, SYNONYM, TABLE, TRIGGER_BODY, TYPE_SPEC, TYPE_BODY, USER, VIEW) /QNAME
[IDENTIFIER/substring(@value,1, 3) != "za_"]

This rule returns object name of which the first 3 characters are not "za_" (and this is case INsensitive, so all these options work: ZA ,za_ , zA_ or Za).

Let´s add the information there. Once we click ok we will have a new Rule (#7000) ready to use!

We will do exactly the same for the next rules. You can find the XPath Expression for all of them below:

2. Table names should be under 26 characters

//CREATE/TABLE/QNAME
[IDENTIFIER/string-length(@value) >= 26]

Returns table name if its length is 26 or higher.

3. When creating index or table specific Tablespaces should be used. I defined as valid tablespaces Zaida and users

//CREATE/(TABLE, INDEX)
[ not( TABLESPACE/QNAME/@value=("zaida", "users") ) ]
/QNAME

Returns name of table or index no having tablespaces with one of the listed names.

4. When creating table, Primary key should be created

//CREATE/TABLE [not(CONSTRAINT/@type="primary_key")]/QNAME

Returns name of table without primary key constraint.

5. Some columns type are not allowed. In this example blob and timestamp are not allowed

//CREATE/TABLE/COLUMN_SPEC
[TYPE/QNAME/@value=("blob", "timestamp")]
/QNAME

Returns the lists the "forbidden" data types.

 

 

 Once created we can see the, listed as a rules:

Now let's go to see them in action. We will type a select that does not follow any of this companys rules and will run Code Analysis. In this case I will select "All Rules":

As expected Toad lists us all the violations at the rules we just created.  

Let's fix them one by one. 
For rule 7001 we need to choose a shorter name for the table:

Now for Rule 7002 we need to specify one of the tablespaces defined as valid: zaida or users:

For Rule 7003 we need to define a Primary Key:

For Rule 7000 we need to add the Prefix Schema in the table name. In this case is za_

And last but not least let's go for Rule 7004: timestamp type needs to be changed to an allowed type. For example varchar2(20).

Now our code follows the company rules.

Wasn't this great? Now there are no excuses to have a perfect PL/SQL code!

By the way, the rules above will be included as rules 2141,2142,2143,2144 and 2145 in the next version of Toad for Oracle.
Do you have any suggestion for a new rule? Please add a comment below!

Hope this helps and thanks for reading!

Anonymous
Related Content