The Templating Way
Today, grasshopper, you will learn the Way of the Template. The Templating Way is the path by which complex output is produced in a harmonious fashion.
The Templating Way does not cobble a string together from bits and pieces in linear fashion.
htp.p('<HTML><HEAD><TITLE>'||:title
||'</TITLE></HEAD><BODY>'
||:body||'</BODY></HTML>');
The Templating Way separates the Template from the Substitutions; by this division is harmony achieved.
DECLARE
template VARCHAR2(200)
:= q'[
<HTML>
<HEAD>
<TITLE> #TITLE# </TITLE>
</HEAD>
<BODY> #BODY# </BODY>
</HTML>
]';
BEGIN
htp.p(
REPLACE( REPLACE( template
,'#TITLE#', :title)
,'#BODY#', :body)
);
END;
It is efficient – each substitution expression is evaluated once and once only, even if required many times within the template.
The Templating Way makes dynamic SQL easy to write and debug. It makes bugs shallower.
SELECT REPLACE(REPLACE(REPLACE(q'[
CREATE OR REPLACE TRIGGER #OWNER#.#TABLE#_BI
BEFORE INSERT ON #OWNER#.#TABLE#
FOR EACH ROW
BEGIN
IF :NEW.#COLUMN# IS NULL THEN
SELECT #TABLE#_SEQ.NEXTVAL
INTO :NEW.#COLUMN#
FROM DUAL;
END IF;
END;
]', '#OWNER#', USER)
, '#TABLE#', cc.table_name)
, '#COLUMN#', cc.column_name) AS ddl
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_type = 'P'
AND c.constraint_name = cc.constraint_name
AND cc.column_name like '%NO';
The Templating Way is simple, but looks complex to the uninitiated. It is readable, and affords maintainability.
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.