SQL*Plus Tip: Turn a File into a String Literal

Consider for a moment this code, using SQL*Plus's '@' command to import a file:

select
'
@foo.htm;
'
from dual;

We've just turned the entire contents of foo.htm into a string literal. Of course, it won't work if foo.htm contains any single quote characters. No problem in 10g, using the alternative quoting mechanism:

select
q'{
@bar;
}'
from dual;

Now we're OK as long as the file doesn't contain the sequence }'. We could pick other characters if needed to avoid collisions with the file contents.

Think about this for a minute. Any file less than 32K could be turned into a PL/SQL VARCHAR2 variable. We could run REGEXP_REPLACE functions to substitute variable content inside the string.

This could be very useful. There is always some tension between coders and designers on web projects. Everyone wants to be able to freely edit their pieces without interference from the other. This technique allows someone to mark up a div, table, style, or other HTML block in its own file, and a PL/SQL coder to pick it up and use it without any extra overhead.

I've never been entirely comfortable with solutions that start with an HTML file and embed little code blocks inside, like with PL/SQL Server Pages. That seems to me to give too much opportunity for fatal error to the person editing the HTML file. When was the last time you asked somebody to make a tiny edit to an HTML file, and they only changed the part you wanted them to. My point exactly; chances are, they went wild changing all your list tags, adding style attributes, and who knows what else. I'd rather have my program logic pumping out divs, table cells, form fields, etc. with only the small block of tagging for each container subject to outside editing.