After the earlier post about PL/Scope another use has come to mind with regard to standards checking.
A standard coding principle is to to avoid hard coding in code. Hard coding can take many forms but the one we are looking at here is the case where we define parameters or variables in PL/SQL using a static definition rather than using typing to a database columns.
For example we may have
v_surname VARCHAR2(40); -- hard code length
instead of
v_surname person.surname%TYPE -- takes definition from the surname column in the person table
By using the type of the column we automatically pick up changes that happen to the table definition without the need to alter our code.
So where does PL/Scope help with this? Well you can pretty easily check all your code (that has been compiled with PL/Scope turned on) to find examples of where variables have been declared using a standard datatype rather than referencing a database column.
So here is a small function as an example.
FUNCTION f_agg_cost (p_event_no NUMBER)
RETURN NUMBER
AS
v_agg_cost NUMBER;
BEGIN
SELECT SUM (bk.cost)
INTO v_agg_cost
FROM bookings bk
WHERE bk.event_no = p_event_no;
RETURN (v_agg_cost);
END;
If we compile using PL/Scope we can then run this query to find where straight datatypes have been used
SELECT ai2.line,
ai2.NAME,
ai2.TYPE usage,
ai.NAME datatype
FROM all_identifiers ai
INNER JOIN
all_identifiers ai2
USING
(owner, object_name, object_type)
where owner = 'SCHEMA_OWNER' AND
object_name = 'F_AGG_COST' AND
ai.TYPE LIKE '%DATATYPE' AND
ai.usage_context_id != 0 AND
ai2.usage_id = ai.usage_context_id AND
ai2.TYPE IN ('FORMAL IN', 'FORMAL OUT', 'VARIABLE')
ORDER BY ai.line
Which results in two records
LINE NAME USAGE DATATYPE
---- ---------- --------- --------
1 P_EVENT_NO FORMAL IN NUMBER
4 V_AGG_COST VARIABLE NUMBER
So we can see that we get entries corresponding to the declarations for the parameter and the variable. Of course this would be easy to pick up just by looking at it but in a 10,000 line package being able to do it via a query makes finding these much easier.
So if we change the function to be this
FUNCTION f_agg_cost (p_event_no bookings.event_no%TYPE)
RETURN NUMBER
AS
v_agg_cost bookings.cost%TYPE;
BEGIN
SELECT SUM(bk.cost)
INTO v_agg_cost
FROM bookings bk
WHERE bk.event_no = p_event_no;
RETURN (v_agg_cost);
END;
And then run the query again we get no records returned.
Of course it does not always make sense to declare parameters and variables against database columns as there may not be a related column that can provide the typing details. But having this option to quickly check lots of code can help with code reviews and implementing standards.
