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.

SQL*Plus Tip: @ vs. @@

I built huge amounts of PL/SQL code with SQL*Plus, using the '@' command to import source files, without ever running across the '@@' command. But one day I found a limitation in '@', and in one fell swoop changed all my SQL and PL/SQL scripts to use '@@' and have never looked back.

You need '@@' if you ever want to compile source code by running SQL*Plus from a different directory. For example, I had source files that would call each other like so:

@variables;
@file1;
@file2;

and it all worked fine, as long as I went into the directory where all the files were and ran SQL*Plus from there.

But to automate my build process, I started running various scripts from a different directory, such as:

sqlplus $credentials source/file1.sql

For file1.sql to be able to import file2.sql and so on, I needed to use '@@'. With '@@', all the import commands are processed relative to the directory where the original file sits, not the directory where you run SQL*Plus.

SQL*Plus Tip: Splitting Up Package Code

With PL/SQL packages, I run into the perennial problem. Logically, a package works best as a single huge thing, grouping perhaps hundreds of related procedures together. But logistically, source code sometimes works better as small individual files.

First up, always put the package spec and package body in separate files. That way, you can recompile the body without invalidating other procedures that call into the package.

You can take advantage of SQL*Plus's lack of a true parser by splitting big PL/SQL package files (typically the bodies) into smaller ones. The '@' and '@@' commands work inside a package, where you might not expect them to. For example:

create or replace package foo
as
@foo_declarations;
@foo_procedures;
@foo_functions;
end;

Tahiti Views, My Oracle Blog

I figured I should keep my work-related blog posts separate from my personal ones, so here's my new Oracle-related blog, "Tahiti Views". Project Tahiti is the code name for my work at Oracle, hardly a secret anymore since tahiti.oracle.com debuted in 2000.

Here I'll blog about all things Oracle, documentation, usability, web application development, and combinations thereof.

As you might suspect, all material here is my own personal opinion, not anything official from Oracle.