How to track commenting pattern and others custom pattern inside procedure or function

[rajivgupta780184]

Hi,

Just going through your cool stuff and tries to play around . I didnt see any option to track code commenting pattern context or any custom pattern standards context setting (plscope_context.set_attr) provision inside procedure or function. Is this doable if yes than can you give me some guidance.

Thanks Rajiv

[PhilippSalvisberg]

Could you please provide an example? E.g. as source snippet and what you are looking for.

[rajivgupta780184]

Suppose I have a subtype declared in some package say pkgsubtype" and I am declaring a local variable based on that in my all proc as this is standard I want to follow in my procedure.

Declare v_procname pkgsubtype.proc_name;

It migh happended a new joinee is unaware of something like this and created his own declaration like below

v_procname varchar2(50);

Which is my area of pain as I want identify custom code like this as error. In brief above was just a sample my intention is to find any specific pattern if someone is deviating from that than it should be marked as not ok. Hope this clear your doubt. I know its not straight forward but just wondering if this can be done.

[rajivgupta780184]

Also How to check naming convention for constraint,PK,FK etc ..? Is their any provision ?

[PhilippSalvisberg]

Let's create some example code

CREATE OR REPLACE PROCEDURE good IS
   l_dname dept.dname%type;
BEGIN
   NULL;
END;
/

CREATE OR REPLACE PROCEDURE bad IS
   l_dname VARCHAR2(100);
BEGIN
   NULL;
END;
/

CREATE OR REPLACE PACKAGE mytypes IS
   SUBTYPE dname_type IS VARCHAR2(14 BYTE);
END;
/

CREATE OR REPLACE PROCEDURE good2 IS
   l_dname mytypes.dname_type;
BEGIN
   NULL;
END;
/

and here's a PL/Scope which reduces the results to relevant columns and declared variables:

SELECT p.name, c.type AS ref_type, c.name AS ref_name, c.text
  FROM plscope_identifiers p
  JOIN plscope_identifiers c
    ON p.owner = c.owner
       AND p.object_type = c.object_type
       AND p.object_name = c.object_name
       AND p.usage_id = c.usage_context_id
 WHERE p.type = 'VARIABLE'
   AND p.usage = 'DECLARATION'
   AND p.object_name IN ('BAD','GOOD', 'GOOD2');

The result of the query is

NAME            REF_TYPE             REF_NAME        TEXT                               
--------------- -------------------- --------------- -----------------------------------
L_DNAME         TABLE                DEPT               l_dname dept.dname%type;        
L_DNAME         PACKAGE              MYTYPES            l_dname mytypes.dname_type;     
L_DNAME         CHARACTER DATATYPE   VARCHAR2           l_dname VARCHAR2(100);      

You may now filter the result based on REF_TYPE. Please have a look at the PL/Scope documentation to find other values expected in REF_TYPE. Filtering on CHARACTER DATATYPE will not be enough for all use cases.

[PhilippSalvisberg]

Please open another issue regarding naming conventions. Just to avoid mixing things in this thread. Thanks.

[rajivgupta780184]

Thanks Phillips but how to include same in plscope_naming view? .What if that pattern doesn't exists in declaration part and exists in body part of any procedure /pkg/function and that can be any custom code e.g. I want to search for a specific pattern in my code like what pattern team has used for commenting

/Multi line code comments with single start /

or

/ double start comments/

or

--Single line comments

and suppose I want to restrict team to use only /* / pattern and not others and I want this to be included in plscope_naming view and I can set the COMMENT_REGEXE

exec plscope_context.set_attr('COMMENTS_REGEXE','(*.*//.)|(/*.)|(--.)|(((/*)+?[\w\W]+?(\/)+))');

Please advise.

[PhilippSalvisberg]

PL/Scope is not a full parsing solution. It just reports identifiers and SQL statements during parse time with some context information.

Comments are not covered by PL/Scope. Hence you need to parse the comments from dba_source or from source files yourself. BTW the regex will become a bit more complex since you have to exclude comments in string literals.

So, there are no plans to cover comments in plscope-utils.