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.