Finding TODO items in any PL/SQL object

[HarrisDFO]

Not sure if this will be useful for others, or if a newer releases has it already, but I wrote this query and added it as a template. It finds all of the TODO items in any PL/SQL object (not just the one you have open) in the specified schema (replace YOURDEFAULT with the schema you use most often). I then set up TODO* in the AutoReplace of the Editor Preferences to invoke the template, as foollows:

todo*=D:\Program Files\PLSQL Developer\Template\todo.tpl

The contents of the todo.tpl is as follows:

SELECT NAME,
TYPE,
max(owner) AS owner,
MIN(line) AS line,
max(created) AS created,
listagg(text, '') WITHIN GROUP (ORDER BY NAME, TYPE) AS text
FROM (SELECT NAME
,TYPE
,line
,substr(text, INSTR(text, 'owner=') + 7, instr(substr(text, INSTR(text, 'owner=') + 7), '"') - 1) AS owner
,substr(text, INSTR(text, 'created=') + 9, instr(substr(text, INSTR(text, 'created=') + 9), '"') - 1) AS created
,'' AS text
FROM all_source
WHERE owner = '[#]&'
AND text LIKE '%TODO:%'
UNION ALL
SELECT NAME
,TYPE
,line
,' ' AS owner
,' ' AS created
,line || ' ' || substr(trim(text), 7, LENGTH(TRIM(text)) -8) || CHR(10) AS text
FROM all_source
WHERE owner = '&Owner'
AND (NAME, TYPE, line) IN (SELECT NAME,
TYPE,
line + 1
FROM all_source
WHERE owner = '&Owner'
AND text LIKE '%TODO:%')
ORDER BY 1, 2, 3
)
GROUP BY NAME,
TYPE
ORDER BY NAME,
TYPE

Once it runs, expanding the TEXT column shows all TODO items for the NAME object. I find it very useful.

 

[Marco Kalter]

Thanks!

 

[Claus Pedersen]

Please notice, that the listagg function only works on Oracle 11 or higher

 

[T-Gergely]

But pre 12.2 primary support already ended.