I’m using Code Analysis. and I am trying to learn how to create my own rules, using Xpath expressions. There is not much documentation on Xpath that is beyond the basics (how to identify a node).
What I would really like is to know HOW Toad converts a pl/sql package/procedure into XML. There is a 4 panel window in Code Analysis, where you can paste your pl/sql code into one panel, and then click the little “XML” button in the top left corner of the window, and Toad generates a new window containing the XML for the given pl/sql code.
HOW DOES TOAD DO THIS CONVERSION? They must be using an XML Schema (.xsd). But, I can’t find the schema (xsd) anywhere.
Please show me how I can get the schema.
Oh, and if you know of some GOOD intermediate to advanced Xpath documentation I would be grateful.
The Quest Dev team can correct me if I’m wrong, but Toad uses a proprietary, internally-developed parser to accomplish the conversion.
Have you checked out some of the tutorials on Xpath online? 3WSchools has a decent one, and here’s one more link to consider. I am sure there are others. Inviting the community to share here! Good luck!
https://www.w3schools.com/xml/xpath_intro.asp
https://www.tutorialspoint.com/xpath/
Hi Donald,
Gary is correct. Toad uses an internally developed parser to convert your SQL into XML for XPath parsing. We added the ability to preview and test the XML in the Code Analysis window in order to help make it easier for you to develop custom XPath expressions for custom rules, but they should follow standard XPath methodology.
-John
I appreciate your replies. But, I’m sad to say, I am no further along with writing my own Xpath (for custom Rules). I worked through w3schools and tutorialspoint Xpath lessons some time ago. However, those lessons are so very basic, they do not provide any help for the complexity of writing xpath for code analysis. I find it very odd that Toad has not written an XSD for sql conversion to XML. All my research and practice using XML suggests the importance of a schema document defining the xml. My thoughts were to obtain the logic used to convert SQL to xml-rendered-sql and then using the schema to accurately write my Xpath.
Would it be too much to ask for you to approach your xpath developers to send me details on how they write their xpath.
thanks much, donald
I think this request is a good one to add to the Idea Pond. Let’s see if there are others that would also like this feature.
Can you give a detailed example of a rule you want to add? Perhaps it is a rule that we should incorporate in Toad for everyone.
Thanks for reaching out to me Debbie.
I am pleasantly surprised. Below please find three rules, I’d be super happy if your team would provide the Xpath (and some explanation of how they arrived at
the “answer” for all three, however, I am sure all are very busy. They are in order of my preference/need.
Comparisons and conditional tests shall be written to handle the possibility that the value of one or more operands may be NULL. In PL/SQL, comparisons
involving NULL values always yield NULL and NULL is never TRUE. The best practice, when feasible, is to use NVL() on operands to “convert” NULL values to a value that is not in the domain of possible values thereby eliminating the issues involved with NULL
comparisons.
DECLARE
x NUMBER := 5;
y NUMBER := NULL;
BEGIN
– Ok and Preferred
IF NVL(x, -99) != NVL(y, -99) THEN
<statement(s)
ELSE
<statement(s)
END IF;
– Ok but NOT Preferred
IF (x IS NULL AND y IS NOT NULL)
OR (x IS NOT NULL AND y IS NULL)
OR (x IS NOT NULL AND y IS NOT NULL AND x != y) THEN
<statement(s)>
ELSE
<statement(s)>
END IF;
– Not Ok, yields NULL and executes the FALSE branch even though x and y are not equal
IF x != y THEN
<statement(s)>
ELSE
<statement(s)>
END IF;
END;
Remove variables from Declaration section that are NOT used in the code.
DECLARE
x NUMBER := 5;
y NUMBER := 7;
z NUMBER := 99; --NOT USED IN CODE, SHOULD BE REMOVED
myName varchar2 (50) := ‘DONALD’; --NOT USED IN CODE, SHOULD NE REMOVED
BEGIN
IF x != y THEN
<statement(s)>
ELSE
<statement(s)>
END IF;
END;
-
COUNT shall not be used just to determine if there are rows to process in subsequent DELETE or UPDATE statements.
– Not Ok
BEGIN
SELECT COUNT(*)
INTO count
FROM emp
WHERE salary > 100000;
IF count > 0 THEN
UPDATE emp
SET salary = salary * 1.1;
ELSE
<statement(s) to indicate nothing was done>
END IF;
END;
– Ok
BEGIN
UPDATE emp
SET salary = salary * 1.1
WHERE salary > 100000;
IF SQL%NOTFOUND THEN
<statement(s) to indicate nothing was done>
END IF;
END;
Donald Burrows, CTR | IngenuitE
Project Support Unit (AMK-212D)
Enterprise Service Center
Phone: 405-954-7640
Cell: 405-593-2037
Hello Donald,
Case 1 NVL
Looks a great idea. We’ll implement this asap as rule 6749 (Use NVL in PL/SQL predicates whenever values can be NULL).
Issue QP-3397.
Case 2 Unused variables
Rule 6405 (Avoid defining variables that are not referenced inside the program.) should detect that. Feel free to feed back any issues you may have.
Case 3 COUNT(*)
We have rule 5804 (Ensure SELECT COUNT(*) is being used to check the number of rows and not merely the existence of rows.) which uses the following expression:
//SELECT_LIST (: query with a COUNT() :)
[count(SELECT_ITEM) = 1]
[SELECT_ITEM/QNAME[PARAM_LIST]/IDENTIFIER/@value = "count"]
/SELECT_ITEM/QNAME[PARAM_LIST]
Perhaps this is a little too basic in the sense that any retrieval of table count will trigger that rule. Perhaps it would be more useful if we could detect that the retrieved count is followed by a test on its value.
The following should work:
for $q in (//SELECT (: any query with puts COUNT() into a counter variable :)
[count(EXPR/SELECT_LIST/SELECT_ITEM) = 1]
[EXPR/SELECT_LIST/SELECT_ITEM/QNAME[PARAM_LIST]/IDENTIFIER/@value = "count"])
return
(: is the counter used in a subsequent test? :)
$q/following-sibling::IF//QNAME[@value=$q/EXPR/INTO_LIST/QNAME/@value]
How about this? We can still refine it. We’d appreciate your feedback!
Hope this helps,
Andre
Donald, in Toad you can create a new rule (View -> Toad Options -> Code Analysis -> General -> Edit Rules -> click on 1st icon on top and enter the fields. Don’t forget to add the rule to any of the rule sets you’re using.
You can clone an existing rule (such as the said 5704) as well instead.
About your request for XSD and alike … you are fully right, you need both the schema definition AND the XPath language grammar in order to be able to go in depth, beyond cloning and patching. We wrote our own XPath parser based on 2.0 and 3.0 and highly customized to SQL (such as having automatic case insensitivity) and with a bunch of proprietary extensions. I feel that Quest would not be reluctant to publish XSD and grammar, however it would not make sense to create mutual liabilities for just a very small number of users: documentation, versioning, development restrictions, support etc. our side, and learning XSD and Code Analysis XPath the users side. In that case we’d better write, or help writing, the rules for those few users directly, and at the same time that enriches our own rule catalog.
Here’s where Idea Pond comes in. We’d very much like to see how many users are actually interested, and to what extent, what are their needs.
Thanks for your replies. I have created a couple of my own custom rules, however, they are so very basic. All they do is “identify” a node (name and/or attribute).
Can you please give me some direction with a couple of things?
- How can I create and populate a variable in Xpath. For example, I identify a node and want to “store” the value in that node to a variable. Then I use that variable as part of a condition.
- How can I create a variable and populate it with, for example, the count of all nodes with attribute value “x”?
So, I am perfectly capable in “finding” a node. I just can’t figure out how to use the data from the node.
Following up on your reply “…We’d better write, or help writing, the rules for those few users…”
Is this an invitation for me to provide you guys with further Rule Requirements?
And following up on “…I feel that Quest would not be reluctant to publish XSD and grammar…”
Would you please send me an “informal” copy of your XSD and any relevant grammar?
Again, big thanks,
donald
send me an “informal” copy
Sorry, we are not ready to do that today, at least for technical reasons: everything is in a format that cannot be easily shared, we have nothing out of the box at this time. In my writing I meant that we could organize ourselves to do that (given enough user interest), but that implies quite a bit of work, including providing support etc.
direction with a couple of things
Explaining XPath and hopping from topic to topic may lead us too far. May I suggest to write down what you want to obtain, perhaps we can help you quickly. And the let's see where we end.
Thanks,
Andre
Hi Avergison,
Hope you are doing good,
I am also working on Toad Code Review features and stuck at some points. It would be great help if Quest publish/share XSD to convert sql to xml.
Regards,
Kumudraj