Encapsulate each SQL statement in a dedicated function/proc

Link to this posting

Postby Ursego » 06 Aug 2019, 07:39

From the book "PL/SQL Best Practices":

Put single-row fetches inside functions; never hard-code a query in your block.

Always put your single-row [i.e. NON-cursor - Ursego] query inside a function, and then call the function to return the information you need (whether it's a scalar value, an entire record, or even a collection) through the RETURN clause.


Instead of writing code like this:

Code: Select all
    SELECT title INTO l_title -- HARD-CODED QUERY... BAD IDEA!
     FROM book
    WHERE isbn = isbn_in;

you should create a function, ideally within a "table encapsulation package":

Code: Select all
PACKAGE te_book IS
   FUNCTION title (isbn_in IN book.isbn%TYPE) RETURN book.title%TYPE;

Now your application code looks like this:

Code: Select all
    l_title := te_book.title (isbn_in);


Optimal performance: The query is written once, presumably by the developer who best knows how to write it. Since there is a single physical representation of the query in code, the parsed version of the cursor is cached and used repeatedly.

Easy maintenance: If you need to change the query, you only have to do it in one place.

Consistent error handling: Individual developers don't have to remember to write handlers for NO_DATA_FOUND and TOO_MANY_ROWS.

The readability of your code also improves, since it's a more accurate translation of the requirement.


Discipline is required in a multi-person team environment to ensure that the team has at least one person overseeing this type of encapsulation and that the whole team adheres to this standard.

Encapsulate INSERT, UPDATE, and DELETE statements behind procedure calls.

Write a standalone procedure or put such procedures inside a single "table encapsulation package," but never, ever embed DML statements directly within application code.

Know Thy SQL

Take the "Know Thy SQL" test: pick a table, any critical table in your application schema. Ask yourself this question: "Do I know where all or any of the INSERT statements for this table appear in my code?" Chances are that you can't answer definitively, and that is because we PL/SQL developers are somewhat haphazard about managing our SQL statements. The result? Tremendous obstacles to performing accurate impact analysis on your code from database changes, among other things.


Instead of writing an INSERT as follows:

Code: Select all
INSERT INTO book (isbn, title, author)
VALUES ('1-56592-675-7', 'Oracle PL/SQL Programming Guide to Oracle8i Features', 'Feuerstein, Steven');

use a standalone procedure, as in:

Code: Select all
add_book ('1-56592-675-7', 'Oracle PL/SQL Programming Guide to Oracle8i Features', 'Feuerstein, Steven');

or a packaged procedure:

Code: Select all
te_book.ins ('1-56592-675-7', 'Oracle PL/SQL Programming Guide to Oracle8i Features', 'Feuerstein, Steven');


Your application runs faster. All programs that perform inserts into a given table use exactly the same INSERT, which results in less parsing and reduced demands on SGA memory.

Your application handles DML-related errors consistently. It's not up to individual developers to write error-logging mechanisms or decide how to deal with particular errors.


You need to write or generate more procedural code.

Your DBA may need to adjust the size of the shared pool area to handle the increased volume of code.

You may need to create multiple update procedures, to match up with various combinations of columns that you update in your application.
User avatar
Site Admin
Posts: 141
Joined: 19 Feb 2013, 20:33

IF you want to ((lose weight) OR (have unbelievable (brain function AND mental clarity))) THEN click:

Traffic Counter

free counters

eXTReMe Tracker