Skip to Main Content

Info

For this site, I created a package (and some supporting objects) to dynamically show the source code and/or DDL of Apex and database objects.
It has turned into a bit of a monster size-wise, and it is not complete (i.e. a work in progress) but it serves its purpose very well.
I do plan to update the html it generates; that could be (and look) a bit less old school.

Using a package for this has a couple of obvious advantages: it makes sure the source code shown is always complete, up to date, and formatted the same consistent way.
A drawback is that for some object types, e.g. views and packages, generating the html output is quite slow. This slows down the entire page and puts a lot of strain on the (small) server I'm using. So I added a caching mechanism that makes sure the use of the Oracle views and packages is kept to a minimum.
Every time the source code of a particular (group of) objects is requested, the cache table is queried. If a non-expired entry is found, it is returned. If an entry is not found or expired, it is generated and merged into the cache table.
A database job checks to see if the cached version is expired (stale), and is scheduled to run every hour (max 1 hour seemed an acceptable lag). If the cache entry is expired, it marks it as such, and the next time the html is needed, a new version is generated and stored in the cache table.

Since htp.p only supports varchar2, it is limited to 32k. For package bodies that is not always sufficient, so I created an extra object_source_p procedure. It would probably have been better to change all the functions to procedures, but changing all the calls in Apex is a lot of work so I'm not going to bother with that.

Code

Page

Identification
Page ID
Name

Region

Identification
Sequence
Title
Type
Source
Source Type
Region Source

Package

Package
Name
Source

Package Body

Package Body
Name
Source

Table

Table
Name
DDL

Scheduled Job

Job
Job Name
TED_PAGE_SUMMARY_CACHE_REFRESH
Job Type
PLSQL_BLOCK
Job Action
ted_page_summary.cache_refresh;
Schedule Type
CALENDAR
Start Date
12-FEB-19 12.00.00.000000 AM +01:00
Repeat Interval
Freq=Hourly