Custom XPATH for Code Analysis - Check for Column

Toad for Oracle
1

I am trying to create a custom rule in Code Analysis that will be violated when a required column is not present in a CREATE TABLE script. I have written an XPATH exp that returns the value I expect as an indicator that the column name is not present. when I test it externally against the XML.

count(//CREATE/TABLE/COLUMN_SPEC/QNAME/IDENTIFIER[@value="CREATEDATE"])<1

However when I test it in Code Analysis it consistently returns

Rule 7002: Tables must contain CREATEDATE DATE column - [Code Correctness, SQL, Warning] (0: 0)
regardless of the presence of the column or not.
What am I missing here?

2

Pointing this to one of our Xpath experts at Quest...

3

I would use this XPATH, instead. This will detect a table without the CREATEDATE column and mark the violation on the table name.

//CREATE/TABLE[not(COLUMN_SPEC/QNAME[@value="CREATEDATE"])]/QNAME
1 Like
4

To expand upon the change there... your XPATH just evaluates a boolean expression whereas mine is identifying a create table statement that does not have the createdate column and it selects an actual node in the XML to mark as the violation. It's the last part, selecting a node in the XML, that is most critical.

1 Like
5

Thank you. This triggers correctly in CA. It's exactly what I was looking for.

1 Like
6

Michael hit the nail on the head. The expression should return something that can be highlighted indeed, at least IF it would return something. A mere condition would not tell you where it's all happening.

Just for completeness, the comparison @value="CREATEDATE" is case insensitive and will work as long as the column name is not written within double quotes. In such (rather rare) case it is useful to use @dbvalue instead, which is the value of the name known by the database. Columns abc, ABC, AbC and "ABC" will all match @dbvalue "ABC" (Oracle stores all of them as uppercase ABC). If the column name is "aBc" then only @dbvalue="aBc" will produce a match.
As only IDENTIFIER elements do have @dbvalue attributes we need to write the expression as

//CREATE/TABLE[not(COLUMN_SPEC/QNAME/IDENTIFIER[@dbvalue="CREATEDATE"])]/QNAME

Thanks,
Andre

1 Like