Table Encapsulation and %ROWTYPE

Question:
Steven, I have taken your advice about writing SQL statements to heart (don't write SQL in application-level code; hide them behind a packaged API, with as much of it generated as possible). I also decided (and I am the team leader so my decision carries some weight) to go the full route and I revoked privileges on the tables, so my developers have no choice but to use the encapsulation packages. Here's my problem: another of your best practices is to avoid hard-coded declarations and anchor variables back to database tables and columns with %TYPE and %ROWTYPE. But my people can't do that – because I revoked privileges, and they need SELECT on a table to do that. What's a best practice-oriented guy supposed to do?
Answer:
So nice to hear that you are going the encapsulation route! I have used it myself in recent years in every single one of my development projects, and I really can't imagine going back to the "old way" of writing all the SQL statements every time I need them.
And I am very impressed to hear about your decision to revoke table privileges. That's a hard thing to do, but once the revocation is in place, it can greatly improve the robustness of your application.
Yet, as you point out, it leads to an interesting conflict of best practices.
Here's how I resolve this problem: I generate three table API packages for each of my tables:
• A change package that implements all the basic INSERT, UPDATE, and DELETE operations
• A query package that gives me a wide range of functions for querying data from the table
• A types package that creates a set of subtypes, which in essence hide the %TYPE and %ROWTYPE declarations and ensure that I don't even need SELECT authority on tables to build high-quality code
How does it work? Simple.
Suppose I am building an application to maintain a category of things my company sells. One of my tables is cat_tools, which contains information about tools. The table has a name, a description, and a universal_id (primary key, defined as a GUID, or globally unique identifier). Listing 7, includes a portion of the types package for this table.
Code Listing 7: Creating a type package as an API
CREATE OR REPLACE PACKAGE cat_tools_tp
IS
SUBTYPE cat_tools_rt IS cat_tools%ROWTYPE;

SUBTYPE universal_id_t IS cat_tools.universal_id%TYPE;

SUBTYPE name_t IS cat_tools.NAME%TYPE;

SUBTYPE description_t IS cat_tools.description%TYPE;

TYPE table_refcur IS REF CURSOR
RETURN cat_tools%ROWTYPE;

TYPE cat_tools_tc IS TABLE OF cat_tools%ROWTYPE
INDEX BY PLS_INTEGER;

TYPE universal_id_cc IS TABLE OF cat_tools.universal_id%TYPE
INDEX BY BINARY_INTEGER;
END cat_tools_tp;
/
So now assume that the cat_tools table and both the cat_tools_tp (types) and cat_tools_qp (query) packages are defined in the CATALOG schema. I grant EXECUTE on cat_tools_tp and cat_tools_qp to HR. Then from HR, I can write the block of code in Listing 8.
Code Listing 8: Access using the type and query packages
DECLARE
/* A string to hold the assertion name */
l_name CATALOG.cat_tools_tp.name_t;

/* A collection to hold the set of tools rows. */
l_tools CATALOG.cat_tools_tp.cat_tools_tc;
BEGIN
/* The allrows function retrieves all rows from cat_tools. */
l_tools := CATALOG.cat_tools_qp.allrows;

/* Assign each name from collection to local variable. */
FOR indx IN 1 .. l_tools.COUNT
LOOP
l_name := l_tools (indx).NAME;
END LOOP;
END;
/
As you can see, even though the HR schema has no access to the cat_tools table, I am able to declare variables by using datatypes that are anchored (indirectly) to the table and its columns. I can also query the contents of the table.
And if the table ever changes, the types package will also change (that is, you will regenerate it). All programs that reference this package will then have to be recompiled; thus, you have the same dependencies you would have with %ROWTYPE and %TYPE declarations.