Explain Custom PL/SQL Packages: Design Standards in Oracle Applications

19 / 60

PL/SQLintermediate

Custom PL/SQL Packages: Design Standards

Establish best practices for structuring custom PL/SQL packages in an EBS technical environment

Custom code in EBS has to survive patching, be maintainable by the next person, and behave like the code around it. A few conventions do most of that work.

A well-designed custom package is like a clearly labeled, professionally wired electrical sub-panel added to a house — clearly marked as an addition (naming convention), following the same electrical code as the original wiring (Oracle conventions), and documented so any future electrician (developer) can safely work on it.

Key Concepts

Prefix everything. XX_ or a client code, so custom objects are distinguishable from seeded ones at a glance and cannot collide with anything Oracle ships later.

XX_

Specification and body carry different responsibilities.

    package spec   the contract: procedure signatures, public types
    package body   the implementation

Callers depend on the spec. Change the body and dependent objects stay valid; change the spec and they are invalidated and must be recompiled. That distinction is worth designing around in a system where a recompile cascade is disruptive.

Follow Oracle's own calling convention.

    PROCEDURE create_thing(
        p_api_version   IN  NUMBER,
        p_customer_id   IN  NUMBER,
        x_return_status OUT VARCHAR2,
        x_msg_count     OUT NUMBER,
        x_msg_data      OUT VARCHAR2);

p_ for input, x_ for output, and the same three status parameters every Oracle API returns. Custom code shaped this way is callable by the same wrappers, and reads as native to anyone who knows EBS.

p_x_

Messages belong in the Message Dictionary, not in string literals:

    FND_MESSAGE.SET_NAME('XXCUST', 'XX_INVALID_SUPPLIER');
    FND_MESSAGE.SET_TOKEN('SUPPLIER', p_supplier_name);
    FND_MSG_PUB.ADD;

Translatable, changeable without a recompile, and it joins the same message stack Oracle's own APIs use.

Log through FND_FILE.

    FND_FILE.PUT_LINE(FND_FILE.LOG, 'Processed ' || l_count || ' rows');

FND_FILE

DBMS_OUTPUT produces nothing in a concurrent program. FND_FILE.LOG writes to the request log, which is what support will read.

DBMS_OUTPUTFND_FILE.LOG

The critique questions, and what they are looking for.

    WHEN OTHERS THEN NULL;        swallows every error silently --
                                  the single worst thing in the file
    l_org_id := 204;              a hardcoded id that differs per
                                  environment
    'SELECT ... ' || p_input      SQL injection
    no bulk processing            row-by-row over a large set

On WHEN OTHERS. Catching it is fine; catching it and doing nothing is not. Log the error, set the return status, and re-raise or return it. A program that reports success while having processed nothing is worse than one that fails.

WHEN OTHERS

What the interviewer is probing.1. "What naming and object standards would you follow?" Probing: the discipline. Stalls: "Any." Moves up: a custom prefix such as XX, objects in a custom schema with APPS synonyms and grants, registered as a custom application.

2. "Why a separate custom schema?" Probing: the reason. Stalls: "Tidiness." Moves up: so a patch never touches your objects and an upgrade impact analysis can tell custom from seeded in one query.

3. "What logging is expected?" Probing: the operational angle. Stalls: "DBMS_OUTPUT." *Moves up:* FND_FILE for concurrent program output and log, plus FND_LOG_MESSAGES, so support can diagnose without a code change.

4. "How should errors be surfaced?" Probing: the engineering. Stalls: "Raise them." *Moves up:* caught, logged with context, and reported as a request completion status of warning or error — not swallowed into a successful run.