Use of TODO tags to quickly generate a tasklist for PL/SQL development

There are quite a few neat tricks we can steal from other technology stacks and communities for use with PL/SQL development. I have recently written an article on AOP (Aspect Oriented Programming) with PL/SQL as well as a post outlining a database based version control system. Today I want to write some words about an extremely simple and nifty feature in Eclipse, the popular open source Java IDE. If you include the tag TODO in the comments in your source code, Eclipse will spot the occurrences of this tag and aggregate them into a Tasklist. The task-lists provides an overview of all classes, the tasks identified in those classes and the line numbers in those classes where the tags were found.

Now I am wondering: how difficult would it be to do something similar in the Oracle database? So that we can execute a query like

select object_name
,      object_type
,      task
,      line_numer
from  task_list
/

that will return a list of all tasks still to do for the PL/SQL objects in the current schema.

It feels quite obvious that I will make use of a Table Function, pipelined, that will return task-list entries. Let’s first create the database types for the task-list entry and the task-list entry collection:

create or replace
type worklist_entry_type
as object
( object_name varchar2(30)
, object_type varchar2(30)
, task         varchar2(4000)
, line_number  number(5)
, code         varchar2(4000)
)
/
create type worklist_type
as table of worklist_entry_type
/

A straightforward implementation of our Worklist Extractor is the following PL/SQL Function:

CREATE OR REPLACE function worklist
return worklist_type
pipelined
as
  l_task varchar2(4000);

   function get_code
   (  p_name in varchar2
   ,  p_type in varchar2
   ,  from_line IN INTEGER
   ) return varchar2
   IS
     l_code varchar2(32000);
      CURSOR src_cur
      IS
        SELECT S.line, S.text
          FROM user_source S
         WHERE S.name = UPPER (p_name)
           AND S.type = UPPER (p_type)
           AND S.line between from_line and from_line+10
		   ;
   BEGIN
     for r_src in src_cur loop
       l_code:= l_code ||TO_CHAR (r_src.line)||':'|| r_src.text;
     END loop;
	 return l_code;
   END get_code;

  /* @TODO Implement function in package, cache the worklist in the package along with the creation time; for next requests, only query PL/SQL objects with a LAST_DDL_TIME later than the cached worklist creation time
  */
begin
  /* basically two options:
     - use a query on user_source to find all occurrences of @ TODO
	 - use a query on all objects, use dbms_metadata.get_ddl to find sources and use instr to look for @ TODO
	 I am not sure which one will be superior from a performance point of view
   */
  for src in (select src.name
              ,      src.type
              ,      src.line
			  ,      src.text
			  from   user_source src
			  where  instr(src.text, '@'||'TODO') > 0
			  order
			  by     2,1,3
			  ) loop
    l_task:= substr( src.text, instr(src.text,'@'||'TODO')+6);
	l_task:= substr(l_task,1, instr(l_task, chr(10))-1);
    pipe row (worklist_entry_type(src.type||' '||src.name, l_task, src.line,get_code(src.name, src.type , src.line+1)));
  end loop;
  return;
end worklist;
/

With this function in place, we can perform simple queries like:

select package_name
,      task
,      line_number
from   table (worklist)
order
by     1
/
PACKAGE_NAME
------------------------------
TASK
----------------------------------------------------------------------------------------------------
LINE_NUMBER
-----------
FUNCTION WORKLIST
Implement function in package, cache the worklist in the package along with the creation time; for next requests, only query PL/SQL objects with a LAST_DDL_TIME later than the cached worklist creation time
         29
PACKAGE BODY PLSQL_ARCHIVER
Ensure that creation of a PL/SQL object as part of revert_to_version does not create a new version itself
        186
PACKAGE BODY PLSQL_ARCHIVER
Cater for situations where the Source of an object is larger than 32769 bytes: in that case EXECUTE IMMEDIATE cannot be used, and dbms_sql is required instead
        193
PACKAGE BODY PLSQL_ARCHIVER
Replace hardcoded value of 40 with the actual column width
        349

We can further refine and embellish this functionality by using additional markers, such as ||,^^,[], what the urgency of the task is, how long the task presumably will take to implement, who (what role?) should do the task, what the key of the task in the Incident Management System is etc.

To not have to suffer the substantial performance hit of generating the tasklist for every time in a session we query that list, we could make of a package that retains a cached copy of the worklist along with the creation time; for next requests, we only need to query PL/SQL objects with a LAST_DDL_TIME later than the cached worklist creation time.

Task List in JDeveloper 1o.1.3

Steve Muench pointed out in his comments on this article that JDeveloper 10.1.3 (EA) has similar capabilities with regard to TODO tags and Task Lists. At first I figured it would be limited to Java source code. However, it turns out that any source type you can edit using JDeveloper, including SQL, PL/SQL, HTML etc., is scanned for TODO tags.

The TODO tags for example I demonstrated earlier in this article in SQL*Plus, using only SQL, can also be reviewed in JDeveloper. With the added benefit of being able to directly navigate to the line in the source code where the task is defined. It looks as follows:

You see here two packages, stored in the database – not on my local file system! – and open in JDeveloper for editing.

I am not exactly sure how to specify the priority and due date, but I am sure it can be done. Very neat!

One thing to notice though: JDeveloper can only show the tasks for the PL/SQL objects that are currently open. Since database objects are not permanently included in a Project or Workspace, like file sources are, there is no way of telling for JDeveloper for which PL/SQL objects you want to see the tasks, unless you explicitly open the object (double click or RMB menu Open). It is therefore not as easy to get an overview of all the tasks for all PL/SQL objects in a database schema.