SQL and PL/SQL Coding Standards Copy link

Self-audit every SQL / PL-SQL / VBA edit against these rules before presenting or deploying it — including one-off operator, migration and test scripts, which are written standards-compliant from the first draft (never "functional first, restyle later"). Most-missed: alias every table including the delete/update/insert target and qualify every column (#5/#6); a space around =; a redundant nvl; != instead of <>.

PL/SQL Coding Standards

1. No redundant brackets or code - AND has higher precedence than OR, so inner brackets around AND conditions are redundant

   -- Bad
   if ((:xxen_reports_run_v.type is not null) and (:xxen_reports_run_v.type='U')) then
   -- Good
   if :xxen_reports_run_v.type='U' then
   -- Bad (redundant inner brackets - AND binds tighter than OR)
   (xu.order_number=p_order_number or (xu.order_number is null and p_order_number is null))
   -- Good
   (xu.order_number=p_order_number or xu.order_number is null and p_order_number is null)

2. No redundant nvl()

   -- Bad
   if nvl(fnd_profile.value('XXEN_REPORT_DEBUG'),'N')='Y' then
   -- Good
   if fnd_profile.value('XXEN_REPORT_DEBUG')='Y' then

   • nvl(col, sentinel) = literal is redundant BECAUSE OF the = operator, NOT because the column is non-null — do not "verify the column can't be null" to justify removing it. When sentinel <> literal, a null column already fails the equality (null='VALID' is not true) and so does the sentinel (nvl(null,'X')='VALID' -> 'X'='VALID' -> false): identical result whether or not nulls occur. Just write col=literal.
   • Same for the negation when sentinel = literal: nvl(col,'INVALID')<>'INVALID' is redundant (null and 'INVALID' both give false) -> col<>'INVALID'.
   • The one case where the nvl is NOT redundant is a negation with sentinel <> literal: nvl(col,'X')<>'VALID' flags a null row ('X'<>'VALID' is true) whereas bare col<>'VALID' does not (null<>'VALID' is null/not-true). Keep it there only if nulls can occur AND you want them flagged; otherwise (column provably non-null for the filtered set) removing it is correct and keeping it is a guard that can't fire (rule #50).

3. No redundant else null in case statements

   -- Bad
   case when x.shared_string='s' then to_number(x.shared_string_id) else to_number(null) end
   -- Good
   case when x.shared_string='s' then to_number(x.shared_string_id) end

4. One when clause per line in case statements

   • Keep when and then on the same line when the statement is simple enough to fit
   • Split onto separate lines only when the when condition or then expression is complex and doesn't fit readably on one line

     
     -- Bad (multiple when clauses on one line):
     when 1 then p_attribute1 when 2 then p_attribute2 when 3 then p_attribute3

   -- Good (simple - when/then on same line): when 1 then p_attribute1 when 2 then p_attribute2 when 3 then p_attribute3

   -- Good (complex - split for readability): when p_status='ACTIVE' and p_end_date is null then some_long_function_call(p_param1, p_param2, p_param3)

5. Space between closing bracket and keywords

   -- Bad: )loop
   -- Good: ) loop

6. Always use table aliases — even on single-table queries — and qualify every column with its alias

   -- Bad (no alias)
   select 1 from dual where exists (select 1 from xxen_report_sql_columns)
   -- Good (alias on the only table)
   select null from xxen_report_sql_columns xrsc where rownum=1
   -- Bad (single-table query without alias)
   select 'Y' from fnd_irep_classes where irep_name='XXEN_WEBSERVICES' and deployed_flag='Y';
   -- Good (alias plus qualified columns)
   select 'Y' from fnd_irep_classes fic where fic.irep_name='XXEN_WEBSERVICES' and fic.deployed_flag='Y';
   -- Bad (alias declared but not all columns qualified)
   select count(*) from user_ords_roles uor where name='xxen_webservices';
   -- Good
   select count(*) from user_ords_roles uor where uor.name='xxen_webservices';

   The only exception is dual, which is conventionally referenced without an alias when no column is selected from it (select sysdate from dual, select 'X' from dual). Any other table — including one-off existence checks, scalar lookups in select lists, and queries against data-dictionary views — must have an alias and column-qualified references. Applies equally to PL/SQL select … into statements, for c in (...) cursors, and SQL embedded in shell here-docs.

7. Use correct table alias conventions

   • Alias is formed from first letter of each word part in the table name
   • Include the _v suffix for views
   • Exception: if alias would be a reserved keyword, keep full short form (e.g. ap_suppliers → aps not as)
   • When table name changes, alias must change accordingly
   • When joining the same table multiple times, add descriptive suffix

     
     -- xxen_reports -> xr
     -- xxen_reports_tl -> xrt
     -- xxen_report_parameters -> xrp
     -- xxen_report_run_param_values -> xrrpv (not xrrpvt)
     -- xxen_report_dynamic_col_rules -> xrdcr
     -- xxen_report_dyn_col_rule_rpts -> xrdcrr
     -- ra_rules -> rr
     -- ra_terms -> rt
     -- wsh_carrier_ship_methods_v -> wcsmv (include _v for views)
     -- qp_list_headers_vl -> qlhv
     -- hz_cust_site_uses_all -> hcsua
     -- po_headers_all -> pha
     -- mtl_system_items_b_kfv -> msibk (NOT mtl_system_items_kfv — always use _b_kfv directly)
     -- ap_suppliers -> aps (not 'as' — reserved word)

   -- When same table joined multiple times, add suffix: -- ra_rules for accounting rule -> rr_a -- ra_rules for invoicing rule -> rr_i -- hz_cust_site_uses_all for ship_to -> hcsua_ship -- hz_cust_site_uses_all for bill_to -> hcsua_bill -- wsh_carrier_ship_methods_v for line -> wcsmv_ln -- gc_code_combinations_kfv multiple joins -> gcck1, gcck2

8. Two blank lines BEFORE AND AFTER each top-level procedure in the package body — consistently. Single blank line within procedures and in the spec.

   • Two blanks above the first procedure (after the package globals / private helpers).
   • Two blanks between every pair of top-level procedures.
   • Two blanks after the last procedure, before end <pkg_name>;.
   • The point is visual symmetry: each top-level procedure stands out the same way, with two blank lines separating it from whatever comes before AND after. Not 1, not 3.
   • No blank lines between procedure X is (or function X is) and the first declaration / begin. Declarations start immediately on the next line. Same for nested procedures.
   • In the spec: single blank line between procedure declarations.

9. No standalone comment separator lines

   -- Bad
   --
   -- comment
   -- Good (just blank line before comment)
   -- comment

10. No spaces around assignment or comparison operators

    -- Bad: l_row := l_row + 1;
    -- Good: l_row:=l_row+1;
    -- Bad: nvl(ac.status,'A') <> 'I'
    -- Good: nvl(ac.status,'A')<>'I'
    -- In XML: use &lt;&gt; with no surrounding spaces

11. Use bind variables instead of literals in dynamic SQL

    -- Bad: 'where xur.run_id='||g_upload_run_id
    -- Good: 'where xur.run_id=:g_upload_run_id'

12. Package spec procedure order must match body order

    • The order of function/procedure declarations in the package spec MUST exactly match the order of their implementations in the package body
    • When adding a new public function, find where it's located in the body and add the spec declaration at the corresponding position
    • This is a PL/SQL requirement - mismatched order causes compilation errors

      -- Example: If body has functions in order: func_a, func_b, func_c
      -- Then spec MUST have them in same order: func_a, func_b, func_c
      -- NOT: func_a, func_c, func_b (wrong order - will fail to compile)

13. Simplify nested loops - combine into single query where possible

14. CRITICAL: No code duplication - This is a fundamental rule. Code must be minimalistic.

    • Never create a new procedure/function when similar logic already exists - refactor and reuse
    • When adding functionality that exists elsewhere, move the existing code to a shared location
    • If a local/nested procedure needs to be called from multiple places, move it to package level
    • When fixing a bug, ensure the fix applies to all places using that logic (should be one place)
    • Before writing new code, search for existing implementations that can be extended
    • If you find yourself copying code, STOP and refactor into a single reusable unit

15. No unnecessary global variables or functions - use local scope

    • Helper functions/procedures used by only one parent procedure should be defined locally within that procedure's declare section, not at the package body level
    • This keeps scope tight, makes dependencies clear, and avoids polluting the package namespace

16. Use EBS standard types (fnd_table_of_varchar2_4000) instead of custom record types when possible

17. Use Windows line endings (CRLF) for SQL/PL/SQL files, and NO UTF-8 BOM. Files must start with the first byte of create or replace ..., not the 3-byte BOM (EF BB BF). SQL*Plus does not strip the BOM; it interprets it as part of the first command and emits SP2-0734: unknown command beginning '<feff>create or...' with all subsequent declarations following also rejected as garbage. Verify after writing/editing a .pkb / .pks:

    file path/to/X.pkb   # must say "UTF-8 text" or "ASCII text", NOT "UTF-8 (with BOM) text"

    To strip an existing BOM in-place while preserving CRLF (Python; on Linux: sed -i '1s/^\xef\xbb\xbf//'):

    with open(path,'rb') as f: data=f.read()
    if data.startswith(b'\xef\xbb\xbf'):
        with open(path,'wb') as f: f.write(data[3:])

18. Avoid exception handling unless required - Let errors propagate with full stack trace rather than catching and logging

    • When when others IS needed, always log the full error stack — never just sqlerrm (which loses line numbers and the call stack)

      
      -- Bad (loses stack trace):
      exception when others then
      write_log('error: '||sqlerrm);

    -- Good (full stack with line numbers): exception when others then write_log('error: '||dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);

19. Consistent log/error messages in proper English

    -- Bad: write_log ('p_source_line_id        :'||p_source_line_id);
    -- Good: write_log('p_source_line_id: '||p_source_line_id);

20. All code must be lowercase - including function parameters like userenv('lang')

    -- Bad: SELECT * FROM ... userenv('LANG')
    -- Good: select * from ... userenv('lang')

    • The rule governs CODE tokens (keywords, identifiers, function names) — NOT case-significant string-literal DATA. A literal whose exact case is part of a stored value or wire contract keeps its case: parameter/column type codes ('Date', 'DateTime', 'Number', 'Char', 'LOV' — the values stored in xxen_report_parameters.parameter_type and sent in the add-in's F bind field), the engine's semantic markers ('<multiple_values>', '<null>', '<id>'), display meanings, and any value compared against case-sensitive data. Lowercasing these breaks the comparison. The userenv('lang') example is lowercased only because that particular argument is case-insensitive.

21. No redundant get_ prefix in function names

    -- Bad: get_site_use_id, get_new_value, get_inventory_item_id
    -- Good: site_use_id, new_value, inventory_item_id

22. Use traditional Oracle SQL syntax, not ANSI

    -- Bad (ANSI):
    select * from t1 left join t2 on t2.id=t1.id
    -- Good (traditional):
    select * from t1, t2 where t2.id(+)=t1.id

    • Exception: a genuine full outer join uses ANSI full join, confined to its own inline view that joins only the keys, while the rest of the statement stays traditional (Oracle rejects (+) and ANSI joins in the same query block). Typical case: two child lists sharing a key, which outer-joined to their parent separately multiply each other.

23. Use xxen_util functions for lookup translations instead of querying lookup tables

    • xxen_util.meaning(code, lookup_type, application_id) - code to meaning
    • xxen_util.lookup_code(meaning, lookup_type, application_id) - meaning to code
    • Works for fnd_lookup_values, oe_lookups (660), ar_lookups (222), etc.

      
      -- Bad (querying lookup table):
      select ol.lookup_code from oe_lookups ol
      where ol.lookup_type='FOB' and ol.meaning=p_meaning and rownum=1
      -- Good (using function):
      xxen_util.lookup_code(p_meaning,'FOB',660)

    -- Bad (joining to lookup table): from fnd_lookup_values flv where flv.lookup_type='FREIGHT_TERMS' and flv.lookup_code=ooha.freight_terms_code select flv.meaning -- Good (using function): select xxen_util.meaning(ooha.freight_terms_code,'FREIGHT_TERMS',660)

    
    - For FND lookups in LOV queries: use fnd_lookup_values_vl (flvv) with security_group_id=0 and enabled_flag='Y' — NOT fnd_lookup_values with language filter

24. WHERE clause formatting - Each restriction on a new line, join direction flows left to right matching FROM clause order

    • Outer join (+) goes on the right side, matching the natural reading flow from driving table to lookup table
    • Exception: when joining to constant values like rtt.language(+)=userenv('lang'), the (+) stays on the left

      -- Bad (wrong direction, uppercase LANG):
      where
      ooha.header_id(+)=xu.header_id and
      qlhv.list_header_id(+)=ooha.price_list_id and
      rtt.language(+)=userenv('LANG')
      -- Good (left to right flow, lowercase lang):
      where
      xu.header_id=ooha.header_id(+) and
      ooha.price_list_id=qlhv.list_header_id(+) and
      rtt.language(+)=userenv('lang')

      The same left-to-right flow applies to correlated scalar subqueries: the restriction coming from the outer query drives the subquery, so the outer value goes on the LEFT:

      -- Bad (outer correlation on the right):
      (select cbv.bank_name from ce_banks_v cbv where cbv.bank_party_id=ieba.bank_id) bank_name,
      -- Good (outer correlation on the left):
      (select cbv.bank_name from ce_banks_v cbv where ieba.bank_id=cbv.bank_party_id) bank_name,

25. Indentation: declare section flat, begin section indented 2 spaces

    • Everything between declare and begin (variables, types, inline functions) has NO indentation — sits at the same level as declare
    • Everything between begin and end is indented 2 spaces from begin — no exceptions. The first statement after begin must be indented. Nested blocks (loop body, if body) add another 2 spaces.
    • This applies at every nesting level: inline functions in a declare block follow the same rule — their own declare section is flat, their own begin section is indented 2
    • Parameters and local variables use same indentation as procedure/function name
    • Procedure/function headers on one line: function foo return boolean is
    • No alignment padding on local variable declarations

    • Verification: After writing or editing a procedure, check that no line between begin and end has zero indentation (except exception, end, and multi-line string literal continuation lines)

      
      -- Bad (declare section indented):
      declare
      l_sql clob;
      l_error varchar2(4000);
      function decode_b64(p_b64 varchar2) return clob is
      begin
      return utl_raw.cast_to_varchar2(...);
      end;
      begin
      l_sql:=decode_b64(:input);

    -- Good (declare section flat, begin section indented 2): declare l_sql clob; l_error varchar2(4000);

    function decode_b64(p_b64 varchar2) return clob is begin return utl_raw.cast_to_varchar2(...); end; begin l_sql:=decode_b64(:input);

    -- Same rule for named procedures/functions: -- Bad (indented parameters and variables): function default_header_value( p_attribute_code in varchar2, p_operating_unit in varchar2 default null ) return varchar2 is l_header_rec oe_ak_order_headers_v%rowtype; l_default_id number; begin

    -- Good (no extra indentation): function default_header_value( p_attribute_code in varchar2, p_operating_unit in varchar2 default null ) return varchar2 is l_header_rec oe_ak_order_headers_v%rowtype; l_default_id number; begin

    -- For calls, parameters match the call's indentation: process_sales_order( p_header_rec=>l_header_rec, p_line_tbl=>l_line_tbl );

26. Minimalistic SQL - no redundant null checks

    • Equality conditions naturally handle nulls (null = anything is never true)
    • Don't add explicit "is not null" checks when the value is already used in an equality condition

      -- Bad (redundant null checks):
      where xu.order_number is not null and xu.order_type is not null and
      ooha.order_number=xu.order_number and ottt.name=xu.order_type
      -- Good (equality handles nulls):
      where ooha.order_number=xu.order_number and ottt.name=xu.order_type

27. Understand unique keys - no redundant rownum=1

    • Know which columns have unique indexes/constraints and don't add rownum=1 when the query can only return one row
    • Example: hr_all_organization_units.name is unique, so no rownum=1 needed when filtering by name

      -- Bad (redundant rownum on unique column):
      select haouv.organization_id from hr_all_organization_units_vl haouv
      where haouv.name=xu.operating_unit and rownum=1
      -- Good (name is unique):
      select haouv.organization_id from hr_all_organization_units_vl haouv
      where haouv.name=xu.operating_unit

      This applies equally to correlated scalar subqueries: do not add a defensive rownum<=1 "for safety" when the correlation key is unique (e.g. ce_banks_v.bank_party_id) — same family as rule #50, a guard that cannot fire hides a broken assumption.

28. *Use subqueries with x/y/z aliases instead of with ... as blocks (CTEs), and use to avoid repeating columns**

    • Prefer nested subqueries over with ... as blocks
    • Use short aliases (x, y, z) for subquery levels
    • Use select x.* to pass through columns instead of listing them all
    • When a subquery is written in multiline style, put the opening ( on its own line and start select on the next line — never (select on the same line. (A subquery kept entirely on one line is fine as (select ... ) alias; this only applies once it spans multiple lines.)

      
      -- Bad (multiline subquery with '(select' on the same line):
      (select msit.description
      from mtl_system_items_tl msit
      where msit.inventory_item_id=x.item_id and msit.language=userenv('lang')
      ) item_description

    -- Good ('(' on its own line, select on the next): ( select msit.description from mtl_system_items_tl msit where msit.inventory_item_id=x.item_id and msit.language=userenv('lang') ) item_description

    -- Good (short subquery stays on one line): (select ppx.full_name from per_people_x ppx where ppx.person_id=x.buyer_id and rownum=1) buyer

    ```sql
    -- Bad (with ... as blocks, repeating columns):
    with base as (
      select xu.col1, xu.col2, xu.col3, ... from table xu
    ),
    enriched as (
      select base.col1, base.col2, base.col3, ..., extra from base
    )
    select enriched.col1, ... from enriched
    -- Good (nested subqueries with * and x/y/z):
    select y.*, ooha.extra_col
    from (
      select x.*, (select ...) resolved_id
      from (select xu.* from table xu) x
    ) y, other_table ooha
    where y.resolved_id=ooha.id(+)

29. No redundant 'as' keyword for column aliases

    -- Bad:
    select count(*) as order_line_count, min(x.upload_row) as order_sort
    -- Good:
    select count(*) order_line_count, min(x.upload_row) order_sort

30. Use cursor for loop instead of select into with exception handling

    • Shorter and more elegant than begin/exception/end blocks
    • Loop naturally handles no_data_found (just doesn't execute)
    • For multi-line SQL, keep same indentation level as the for statement

      
      -- Bad (verbose with exception handling):
      begin
      select mp.organization_id into l_org_id
      from mtl_parameters mp where mp.organization_code=p_warehouse;
      exception when no_data_found then null;
      end;

    -- Good (cursor for loop, single line): for c in (select mp.organization_id from mtl_parameters mp where mp.organization_code=p_warehouse) loop l_org_id:=c.organization_id; end loop;

    -- Good (multi-line SQL, same indentation as for): for c in ( select ooha.header_id from oe_order_headers_all ooha where ooha.order_number=p_order_number ) loop return c.header_id; end loop;

    -- Bad (SQL indented further than for): for c in ( select ooha.header_id from oe_order_headers_all ooha ) loop

31. No redundant nvl() or implicit type conversions

    • Comparisons handle null naturally (null > 0 is false, not an error)
    • Numeric comparisons do implicit to_number conversion

      
      -- Bad (redundant nvl and to_number):
      nvl(to_number(c.debug_level),0)>0
      -- Good:
      c.debug_level>0

    -- Bad (redundant nvl - null > 0 is already false): nvl(l_count,0)>0 -- Good: l_count>0

32. SQL formatting - one line per select column, from table, where clause, insert column, values expression

    • One column per line after select, one table per line after from, one condition per line after where
    • insert into <table> ( ... ) values ( ... ) — one column name per line in the column list, one value/expression per line in the values list. Never pack multiple columns or values on the same line.
    • Applies everywhere: main SQL, LOV queries in XML, Success SQL, PL/SQL cursors, DML statements
    • Only use single-line format for very small simple queries (e.g. select mp.organization_id from mtl_parameters mp where mp.organization_code=p_code)
    • FROM clause table order must follow the restriction flow — the table restricted first (by bind variable, constant, or driving condition) comes first

      
      -- Bad (columns and tables on same line as keywords):
      select ooha.header_id
      from oe_order_headers_all ooha,oe_transaction_types_tl ottt
      where ooha.order_number=p_order_number

    -- Good (separate lines): select ooha.header_id from oe_order_headers_all ooha, oe_transaction_types_tl ottt where ooha.order_number=p_order_number and ooha.order_type_id=ottt.transaction_type_id

    -- OK for very small queries (single line): for c in (select mp.organization_id from mtl_parameters mp where mp.organization_code=p_code) loop

    -- Bad (insert with packed columns/values): insert into qa_plan_char_actions ( plan_char_action_id, plan_char_action_trigger_id, action_id, assigned_char_id, assign_type, message, creation_date, created_by, last_update_date, last_updated_by, last_update_login ) values ( l_plan_char_action_id, l_plan_char_action_trigger_id, p_action_id, p_assigned_element_id, p_assign_type, p_action_message, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, fnd_global.login_id );

    -- Good (insert with one column / value per line): insert into qa_plan_char_actions ( plan_char_action_id, plan_char_action_trigger_id, action_id, assigned_char_id, assign_type, message, creation_date, created_by, last_update_date, last_updated_by, last_update_login ) values ( l_plan_char_action_id, l_plan_char_action_trigger_id, p_action_id, p_assigned_element_id, p_assign_type, p_action_message, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, fnd_global.login_id );

33. No hardcoded user-facing values - use lookup codes for multi-language support

    • Never hardcode translated/translatable values like 'Book Order', 'Cancel', etc.
    • LOVs display xxen_util.meaning(code, lookup_type, app_id) to users
    • In PL/SQL, use xxen_util.lookup_code(meaning, lookup_type, app_id) to convert back to code
    • Compare against the lookup CODE (e.g., 'BOOK', 'CANCEL'), not the translated meaning

      
      -- Bad (hardcoded translated value - breaks in other languages):
      if l_order_action='Book Order' then

    -- Good (convert meaning to code, compare against code): if xxen_util.lookup_code(l_order_action,'ORDER ACTION',660)='BOOK' then

    - **An error message a user sees comes from the Oracle message dictionary wherever one exists** - fnd_message.get_string('<application>','<message name>'), or fnd_message.set_name+get where the message takes tokens. It is already translated and already says what the user is used to reading, and a rejection our code raises is then indistinguishable from the one the standard program would have raised for the same condition. Reproduce an Oracle validation ourselves (because an interface or api cannot see the rows) and reuse its message and error_code verbatim. Write a literal only when no seeded message fits.
    ```sql
    -- Bad (our own wording for a condition Oracle already names):
    message_:='Costs already exist for this item and cost type.';
    -- Good (the message the Cost Import itself writes):
    message_:=substrb(fnd_message.get_string('BOM','CST_CANT_INSERT'),1,240);

34. Only one space between column expression and alias

    • No alignment padding with multiple spaces
    • Remove alias if it matches the column name

      -- Bad (padded alignment, redundant alias):
      haouv.name                         operating_unit,
      rcta.trx_number                    trx_number,
      rctla.unit_selling_price           unit_price,
      -- Good:
      haouv.name operating_unit,
      rcta.trx_number,
      rctla.unit_selling_price unit_price,

35. Use <> not != for not-equal comparisons. In XML files, escape as <> with no surrounding spaces.

36. Use coalesce for 3+ fallback values instead of nested nvl

    -- Bad (nested nvl):
    nvl(a, nvl(b, c))
    -- Good (coalesce):
    coalesce(a, b, c)

    Two values is fine with nvl. Use coalesce when there are 3 or more fallback values, and always when a fallback is a scalar subquery or function call: nvl evaluates its second argument for every row even when the first is not null (measured: the subquery's full reads with a non-null first argument), while coalesce stops at the first non-null value.

37. PL/SQL string truncation — substr vs substrb

    • Use substrb only when truncating to a variable's byte limit (e.g. 32767 for varchar2). With AL32UTF8, 32767 chars can be 131068 bytes → ORA-06502. Use substrb(text,1,32767) instead.
    • Use substr when truncating to an arbitrary processing limit (e.g. 4000). You want N characters of content, not bytes.
    • Rule of thumb: Is the limit the target variable's max size? → substrb. Is it a "good enough" processing limit? → substr.

36a. ORA-01489: result of string concatenation is too long is raised only by || / concat / listagg — under max_string_size=STANDARD (the EBS default, 4000-byte SQL varchar2) the other string functions (replace, regexp_replace, rpad, lpad, substr) silently truncate at 4000 instead. Corollaries:

• A PL/SQL function returning varchar2 is treated as varchar2(4000) when called from SQL: a longer result raises ORA-06502 at the call site, so any value that comes back is already ≤4000 bytes.
• The classic trap is '|'||some_function(...)||'|' like '%|FOO|%' on a value near 4000 bytes: the two extra characters overflow. Rewrite the pipe-anchored like as bare patterns (x like '%|FOO|%' or x like 'FOO|%' or x like '%|FOO' or x='FOO'), or substrb(value,1,3900) before any ||.
• Signature in a report log: ERROR fetching data with a stack ORA-01489 → SYS.DBMS_SQL → APPS.XXEN_REPORT (dbms_sql.fetch_rows) — the framework is only the messenger, the offending || is in the report/upload SQL, and it fails only on the rows whose value is that long (a filtered test run passes).

37. Minimalistic code is paramount

    • Eliminate unnecessary variables, redundant queries, and extra logic
    • Reuse a single variable rather than declaring separate ones
    • Return directly from loops instead of storing in a variable then returning
    • if condition then return 'N'; end if; return 'Y'; is cleaner than if/else return

38. Always use the highest-level Oracle standard API — before writing direct DML or low-level table operations, search for official Oracle APIs in this order: (1) Public APIs (*_PUB, *_API), (2) Standard utilities/commands (e.g. FNDGFU for fnd_lobs uploads, FNDLOAD for setup data), (3) Table handlers (*_PKG). Example: for fnd_lobs uploads, use the FNDGFU command (like bin/import_xml does) — never direct INSERT into fnd_lobs.

39. Product name is "Blitz Report" — always use the full name "Blitz Report" in labels, descriptions, comments, and user-facing strings. Never abbreviate to just "Blitz" unless it’s an established exception (e.g. profile options like "Blitz Upload", "Blitz FSG" for readability).

    -- Bad:
    p_label=>'Blitz Webservices'
    -- Good:
    p_label=>'Blitz Report Webservices'

40. Never display raw Y/N flag values — always translate using xxen_util.meaning

    • Report display convention: show only "Yes", leave blank for "No" — users don't want to see a wall of "No" values; a blank cell is clearer. Use the dedicated helper xxen_util.yes(col) for Y/N flag columns — it returns the translated "Yes" for 'Y' and null (blank) for anything else. It is defined in XXEN_UTIL as exactly decode(p_lookup_code,'Y',meaning('Y','YES_NO',0)), so always prefer the helper over hand-writing the decode(col,'Y',xxen_util.meaning('Y','YES_NO',0)) form.
    • For CASE WHEN conditions: xxen_util.yes(case when condition then 'Y' end) (omit else 'N')
    • Applies to all boolean/flag columns: primary_flag, dependent_flag, beneficiary_flag, approved_flag, billable_flag, reverse_flag, capitalized_flag, released, transferred, etc.

      
      -- Bad (shows "No" for every non-Y row):
      xxen_util.meaning(pcr.dependent_flag,'YES_NO',0) dependent,
      xxen_util.meaning(ppaa.reverse_flag,'YES_NO',0) reverse_flag,
      case when pdia.released_date is not null then 'Y' else 'N' end released,

    -- Good (shows "Yes" or blank — use the dedicated xxen_util.yes() helper): xxen_util.yes(pcr.dependent_flag) dependent, xxen_util.yes(ppaa.reverse_flag) reverse_flag, xxen_util.yes(case when pdia.released_date is not null then 'Y' end) released,

    
    - **Storage convention for new boolean columns** (Enginatics-owned tables): store 'Y' or null — never 'N'. Two-valued logic only, no Y/N/null tri-state. On insert/update convert incoming 'N' to null with nullif(p_flag,'N'); on display translate with xxen_util.meaning(nvl(col,'N'),'YES_NO',0) so blanks render as language-specific "No". Does NOT apply to existing Oracle EBS flag columns that already use 'N' — keep their semantics.

41. Never wrap indexed date columns with trunc() or to_char() — this prevents index access and forces full table scans

    • For "date from" filters: column>=:date_from (no trunc)
    • For "date to" filters: column<:date_to+1 (before midnight the next day, no trunc)
    • This pattern captures the full day including any time component, while preserving index access

      
      -- Bad (kills index on transaction_date):
      trunc(rt.transaction_date)>=:date_from
      trunc(rt.transaction_date)<=:date_to

    -- Good (index-friendly): rt.transaction_date>=:date_from rt.transaction_date<:date_to+1

42. Default cached id-lookup variables to an impossible sentinel (e.g. -99), not null, when they will be used in NOT IN clauses

    • When replacing a user_name/name→id subquery with a cached variable (e.g. l_enginatics_user_id), initialize to -99 rather than leaving nullable
    • Reason: col not in (-1, null) evaluates to null → filters ALL rows out. The original user_name not in ('ANONYMOUS','ENGINATICS') correctly returned rows when the user didn't exist — a null variable would silently break that semantic on instances where the lookup returns no row.

      
      -- Bad (breaks NOT IN on instances where ENGINATICS user doesn't exist):
      l_enginatics_user_id number;
      for c in (select fu.user_id from fnd_user fu where fu.user_name='ENGINATICS') loop
      l_enginatics_user_id:=c.user_id;
      end loop;
      -- ...
      where xrp.created_by not in (-1,l_enginatics_user_id) -- null → no rows pass

    -- Good (sentinel default): l_enginatics_user_id pls_integer:=-99;

43. Line wrapping: don't wrap proactively, and put connectors at END of line

    • Line length budget: assume wide screens. Don't wrap until a line clearly exceeds ~200–240 characters. Wrapping shorter lines hurts readability, not helps. Sweet spot: keep related logic together on one line where possible, only break when the line genuinely won't fit.
    • Connector position when you DO wrap: and / or go at the END of the previous line, never at the START of a new line. This is consistent with rule #23 for SQL WHERE clauses, generalized to PL/SQL if/while/exit when conditions and SQL HAVING/ON clauses.

      
      -- Bad — connector at start of line:
      if l_x is not null
      and l_y is not null
      and l_z is not null then

    -- Bad — wraps too aggressively when one line would fit: if l_x is not null and l_y is not null and l_z is not null then

    -- Good — fits on one line, no wrap: if l_x is not null and l_y is not null and l_z is not null then

    -- Good — when wrap is genuinely needed, connector at end: if l_x is not null and l_y is not null and l_action=xxen_upload.g_action_create or xxen_upload.value_modified('p_x') or xxen_upload.value_modified('p_y') then

44. Prefer language views (_vl) over manual _b + _tl joins. Oracle EBS provides _vl views (e.g. fa_additions_vl, mtl_system_items_vl, hr_all_organization_units_vl, fnd_lookup_values_vl) that already join the base table to its _tl translations table for userenv('lang'). Using the _vl view is shorter, more elegant, and harder to get wrong.

    -- Bad (manual _b + _tl join):
    from
    fa_additions_b fab,
    fa_additions_tl fat
    where
    fab.asset_id=fat.asset_id and
    fat.language=userenv('lang') and
    fab.in_use_flag='Y'
    -- Good (language view):
    from
    fa_additions_vl fav
    where
    fav.in_use_flag='Y'

    Applies in main SQL, LOV queries, Success SQL, PL/SQL cursors. The handful of cases where you do need the underlying _b (e.g. you need rowid for xxen_util.display_flexfield_value, or you need a non-translated column not exposed by _vl) are exceptions — document why in a comment.

    Reinforces existing rule #33: when you switch to a _vl view, the now-redundant column aliases (e.g. fav.description description) must be removed.

    Items: use mtl_system_items_vl msiv — NOT mtl_system_items_b_kfv msibk — for any query that displays the item DESCRIPTION. mtl_system_items_b_kfv is built on the base table mtl_system_items_b, so its description is the untranslated (install-base-language) value; in a non-US session it shows the wrong language. mtl_system_items_vl is the language view (_b + _tl for userenv('lang')) and — it also carries concatenated_segments plus all the base columns an item LOV needs (recipe_enabled_flag, serial_number_control_code, primary_uom_code, organization_id, inventory_item_id). So mtl_system_items_vl is a complete drop-in replacement: one view gives both the KFF concat (the item "value") AND the translated description — never join _b_kfv for segments + something else for the description, and never reach for a separate _kfv table. Use mtl_system_items_b_kfv only when you genuinely need a column _vl doesn't expose AND translation is irrelevant. Item LOVs, item-description columns, main-SQL item joins, and Success SQL must all source the item from mtl_system_items_vl.

    -- Bad (untranslated description from the base KFF view):
    select msibk.concatenated_segments id, msibk.concatenated_segments value, msibk.description description
    from mtl_system_items_b_kfv msibk where msibk.recipe_enabled_flag='Y'
    -- Good (translated description, same concatenated_segments, one view):
    select msiv.concatenated_segments id, msiv.concatenated_segments value, msiv.description
    from mtl_system_items_vl msiv where msiv.recipe_enabled_flag='Y'

45. CRITICAL: Users must always see DISPLAY VALUES, never IDs or codes. Blitz Report and Blitz Upload outputs are user-facing. Whenever output is shown to a user (template XLSM, result XLSM, parameter prompts, LOV dropdowns, error messages), the value must be the human-readable, translated display value — never the underlying internal code or ID.

    • Status / Action: Use xu.status_ and xu.action_ (display values via xxen_upload.status_meaning / action_meaning), never xu.status_code_ / xu.action_code_ — and never hardcoded 'S', 'V', 'E', 'C', 'U', 'D' etc.
    • LOV-bound columns: When the LOV has id/value columns, display the value, not the id.
    • Lookup codes (FND lookups, e.g. MTL_EAM_ACTIVITY_TYPE): Translate via xxen_util.meaning(code, lookup_type, app_id) — never display the raw lookup_code.
    • Yes/No flags: Use xxen_util.meaning('Y','YES_NO',0) for "Yes", and blank/null for "No" (so the cell renders empty rather than the literal "No"). Never display raw 'Y'/'N'.
    • Date columns: Let the framework's standard date formatting render them. Where a date must be text (an LOV value column), use fnd_date.date_to_displaydate(d); there is no xxen_util.user_date.
    • Never to_date() a Date parameter's bind: the engine binds Date parameters as DATE, so to_date(:p,'DD-Mon-YYYY') converts it back through the session's nls_date_format, and a concurrent request's DD-MON-RR turns 2027 into year 0027. Use the bind as it is; an LOV that passes a date as its id passes text in an explicit format (to_char(d,'YYYY/MM/DD'), converted back with the same mask).
    • IDs (numeric or rowid): Never expose to users in display columns. Even hidden helper columns that hold IDs should be marked <HIDDEN>Y</HIDDEN> so users don't see them.

      
      -- Bad (hardcoded code, user sees "S"):
      'S' status_,

    -- Good (display value, user sees "Success" / "Erfolgreich"): xu.status_,

    -- Bad (raw lookup code): pcr.dependent_flag,

    -- Good (translated display, blank for 'N'): decode(pcr.dependent_flag,'Y',xxen_util.meaning('Y','YES_NO',0)) dependent,

46. Use the most restrictive Oracle data-dictionary view possible. Narrower views are faster and convey intent — user_objects says "my own schema", all_objects "any object I can see", dba_objects "the whole database, needs SELECT ANY DICTIONARY". The choice follows the OWNER of the object (the EBS custom-development storage split):

    • user_* — objects owned by the schema running the script (apps for our install/upgrade flow): APPS-owned packages, views, types, synonyms, our customized Oracle bodies (FND_WEBFILE), APPS' own privileges (user_sys_privs, user_tab_privs), the schema's ORDS objects (user_ords_*). Drop redundant owner='APPS' / grantee='APPS' predicates.
    • all_* — objects in another schema visible to APPS: the storage objects (TABLE, INDEX, SEQUENCE, their triggers) of the custom schema (XXEN by default, customers may choose another — from APPS user_tables returns nothing for them), JA, APPLSYS, SYS via SELECT ANY DICTIONARY, ECC, ORDS_METADATA.
    • dba_* — only when there is no narrower alternative: DB-wide scope (dba_objects do where do.status<>'VALID' after an install), tablespace metadata (dba_tablespaces — user_tablespaces shows only quota'd ones), foreign admin-user lookups (dba_users du where du.username in ('EBS_SYSTEM','SYSTEM')).

      
      -- Bad (XXEN_SCHUB is an APPS-owned package):
      select 'Y' from dba_objects do where do.object_name='XXEN_SCHUB' and do.object_type='PACKAGE' and rownum=1
      -- Good:
      select 'Y' from user_objects uo where uo.object_name='XXEN_SCHUB' and uo.object_type='PACKAGE' and rownum=1

    -- Bad (XXEN_REPORTS lives in the custom schema — user_tables from APPS returns nothing): select ut.tablespace_name from user_tables ut where ut.table_name='XXEN_REPORTS' and rownum=1 -- Good: select at.tablespace_name from all_tables at where at.table_name='XXEN_REPORTS' and rownum=1

    -- Bad (checking APPS' own system privilege): select dsp.privilege from dba_sys_privs dsp where dsp.grantee='APPS' and dsp.privilege='SELECT ANY DICTIONARY' -- Good: select usp.privilege from user_sys_privs usp where usp.privilege='SELECT ANY DICTIONARY'

    
    - **Column-availability trap — all_* is not dba_* minus a filter.** all_users lacks default_tablespace, temporary_tablespace, created, account_status, profile (they exist only on dba_users/user_users); select au.default_tablespace from all_users au parses on its own but fails at runtime with ORA-00904, which inside an embedded cursor cascades into PLS-00364: loop index variable 'C' use is invalid on every reference. Before tightening dba_X → all_X, desc all_X and confirm every selected column exists; foreign admin-user metadata always stays on dba_users.

47. fnd_api.g_miss_* sentinels are NOT null. The Oracle FND API "no-change" sentinels are real values masquerading as missing:

    • fnd_api.g_miss_char = chr(0)
    • fnd_api.g_miss_num = 9.99e125
    • fnd_api.g_miss_date = '01-JAN-4712'

    Never assume is null will detect them. When calling an Oracle API that uses these as "no change" markers, watch for two failure modes:

    • Wrapping the value yourself: if l_value is not null then ... skips logic that should run for g_miss; use if l_value <> fnd_api.g_miss_* (with type-matched comparison) or nvl(l_value,sentinel)=sentinel instead.
    • API-side is not null checks that misfire on the sentinel: some Oracle public APIs gate work on if p_date is not null and use g_miss_date as no-change concurrently. Passing g_miss_date trips the is not null check and triggers the work as if you'd supplied a real value. Known case: inv_loc_wms_pub.update_locator (bug 7297065) — see upload-api-patterns skill § INV Stock Locator Upload for the documented workaround pattern.

48. Generate rows / split a delimited string with xxen_util.rowgen(n) — never connect by level or table(cast(multiset(...))). xxen_util.rowgen(p_rows in number) return fnd_table_of_number deterministic returns n rows; reference the generated counter as rowgen.column_value. It is the standard Blitz idiom (used throughout XXEN_REPORT.pkb / XXEN_XDO.pkb) for the "explode a row into N rows" pattern, e.g. extracting each token of a delimited list with regexp_substr(...,1,rowgen.column_value). Count the items with the same pattern you extract — no +1.

    -- Bad (verbose collection-cast / connect-by row generator):
    table(cast(multiset(select level from dual connect by level<=regexp_count(c.list,';')+1) as sys.odcinumberlist)) l
    ... regexp_substr(c.list,'[^;]+',1,l.column_value)
    -- Good (rowgen — count the token pattern, no +1):
    table(xxen_util.rowgen(regexp_count(c.list,'[^;]+'))) rowgen
    ... regexp_substr(c.list,'[^;]+',1,rowgen.column_value)

    Typical full pattern — split a semicolon-delimited column into one row per token:

    select regexp_substr(c.list,'[^;]+',1,rowgen.column_value) token
    from
    my_table c,
    table(xxen_util.rowgen(regexp_count(c.list,'[^;]+'))) rowgen
    where
    c.list is not null

    For the "extract the Nth :bind" use case the codebase counts the bind pattern: table(xxen_util.rowgen(regexp_count(x.sql_text,':\w+'))) rowgen + regexp_substr(x.sql_text,':\w+',1,rowgen.column_value). rowgen is deterministic, so it costs nothing extra in repeated calls.

49. No redundant "belt-and-braces" defensive code — a guard that cannot fire hides a broken assumption. Never add a condition, filter, null-check, or exclusion that is provably unreachable given the surrounding logic. It is confusing (it implies the impossible can happen), bloats the code, and — exactly like a broad when others (rule #17) — silently absorbs an inconsistency so you never discover that the code or your initial assumption was wrong. Write the predicate/guard that is exactly necessary and no more, so a wrong assumption fails loudly instead of being papered over.

    • Before adding a guard "just in case", verify whether the case can actually occur (query/inspect the data). If it cannot, omit the guard. If you are genuinely unsure, surface the uncertainty to the user rather than defending against it silently.
    • Worked example: a delete that targets rows carrying marker X must NOT also carry and description not like 'Y%' when Y-rows provably never contain X (verify: count of Y-rows that match X is 0). The X marker is already the exact discriminator, so the Y-exclusion guards an empty set — and worse, it would silently skip a mislabelled Y-row that genuinely contained X, the one case you would actually want surfaced.
    • Same family as #2 (no redundant nvl), #13 (no code duplication), #17 (no needless exception handling) and #37 (minimalistic code is paramount): keep code concise, clean and exact.

50. Trunc both sides of date comparisons in validation logic — calendar-day semantics, not instant-in-time. When PL/SQL validation logic compares a date value (column, bind variable, or user-supplied parameter) against sysdate for correctness purposes (e.g. "must be ≥ today", "no more than N years in the future", "cannot be in the past"), apply trunc to BOTH sides of the comparison. Calendar-day semantics: a "today" check must accept any time during today, not just midnight; a "yesterday" check must reject yesterday regardless of clock time.

    • The canonical pattern: trunc(p_date)<trunc(sysdate) for "must be ≥ today" rejection.
    • An asymmetric form like p_date<trunc(sysdate) silently lets "yesterday at 11pm" through whenever sysdate has elapsed past midnight, because trunc strips time on one side only — the bind still carries 23:00:00, the right side is 00:00:00, and the comparison says "false, accept" when it should say "true, reject".
    • For "N years from today" caps: use trunc(p_date)>trunc(sysdate)+N*365 (calendar-day arithmetic with both sides truncated).
    • Pairs with — does not conflict with — rule #42 (which forbids trunc on indexed date columns in WHERE clauses for performance). Rule #42 is about SQL queries against base tables (kills index access → full table scan). This rule is about PL/SQL validation logic comparing user-supplied dates to sysdate (correctness — no index involved). The two rules apply in different contexts and have different goals.

      
      -- Good (trunc both sides — calendar-day semantics, "today" accepted any time of day):
      if trunc(p_effective_date)<trunc(sysdate) then
      message_:='Effective date cannot be in the past.';
      raise le_validation_exception;
      end if;
      if trunc(p_end_date)>trunc(sysdate)+300*365 then
      message_:='End date cannot be more than 300 years in the future.';
      raise le_validation_exception;
      end if;

    -- Bad (no trunc — time-of-day discriminates within a single calendar day, "today 09:00" rejected at 10:00): if p_effectivedate<sysdate then message:='Effective date cannot be in the past.'; raise le_validation_exception; end if;

    -- Bad (asymmetric — lets yesterday-late-evening through): -- p_date holds e.g. yesterday 23:00:00; trunc(sysdate) is today 00:00:00; 23:00<00:00 is false → row accepted as "today" if p_effectivedate<trunc(sysdate) then message:='Effective date cannot be in the past.'; raise le_validation_exception; end if;

51. No backward-compatibility shims for superseded formats — migrate, don't dual-support. Code must be minimalistic; avoid technical debt that exists only to keep supporting outdated logic. When a stored format / code scheme / data convention is replaced (e.g. single-char assignment-level codes S/A/F → full words Site/Application/Form), do a clean cut-over: a one-time data migration converts existing rows, and ALL code (views, parsers, importers, exporters, forms) speaks ONLY the new format. Do NOT add tolerant "accept both old and new" logic (decode(x,'F','Form',x), in ('F','Form')) to keep reading the outdated format at runtime — that debt lingers forever, obscures intent, and silently hides bugs (same family as #13, #17, #37, #50). The ONLY legitimate place the old format may appear is the migration statement itself, which must read the old code once to rewrite it. If you believe a dual-support shim is genuinely needed, surface the specific scenario to the user first — usually there isn't one.

52. Initialize variables in their declaration - When the value is a simple expression available at block entry (e.g. a fnd_profile.value() call), write l_x type:=<expr>; in the declare section instead of declaring the variable and then assigning it on a separate line later in the begin block. Fewer lines, single source of truth. Pairs with #2 (no redundant nvl).

53. Minimalistic comments in delivered code (PL/SQL, SQL, shell, etc.) - Keep comments to 1-2 short lines that state WHY a non-obvious line exists. Do NOT narrate the full mechanism, restate the code, justify the design at length, enumerate edge cases, or cite external references (Oracle notes, MOS doc IDs, blog URLs) inside shipped code. A multi-line comment block that dwarfs the code it documents is noise. Deep background (root cause, history, supporting evidence) belongs in the commit message or the .claude/rules/*.md knowledge files, not in the delivered source. Same minimalism principle as #37 — applies to all delivered code.

    • NEVER write a comment that describes the CHANGE, the previous behaviour, or a comparison against it. A comment documents the code as it stands now, for a reader who has never seen any earlier version. Change history belongs in the commit message and git log/git blame; it must not accumulate in the source. This is the single most common comment failure when editing existing code, because the change is what is on the author's mind at the time — and it is exactly what the next reader does not need.
    • Banned phrasings (each is a rewrite trigger, not a style preference): previously ..., used to ..., no longer ..., was ... now ..., removed the ..., added ... because, changed from ... to ..., instead of re-querying/re-parsing/looping ..., avoids the extra ..., fixes ..., this was a bug, and any reference to a former implementation, an old parameter, or a deleted line.
    • The test: delete the comment and ask what a new reader still cannot infer from the code. Write only that. If the answer is "nothing", write no comment.

      
      -- Bad (narrates the change and the code it replaced):
      p_template_id:=xxen_report.template_id(p_report_id,p_template_name); --resolve through the core instead of re-querying xxen_report_templates
      l_xml xmltype:=xmltype(p_clob); --parse once, previously every statement re-parsed the full clob
      regexp_substr(fnd_webfile.get_url(...),'[^=]*$'); --removed the hardcoded oapc/oas parameters, which were captured from one instance

    -- Good (states a constraint the code cannot show, or no comment at all): p_template_id:=xxen_report.template_id(p_report_id,p_template_name); l_xml xmltype:=xmltype(p_clob); l_key varchar2(32767):=lower(p_vchr)||'|'||p_col_num||'|'||p_col; --delimited key to avoid collisions between value and column name concatenations

54. Every Oracle identifier must be ≤30 bytes — 19c masks a bug that breaks 11g/12.1 (and 11i) installs. Blitz Report ships to EBS 11i, R12.1 and R12.2 — all of which run on a pre-12.2 database (11.2.0.4 / 12.1.0.2) that enforces the classic 30-byte identifier limit. Only Oracle Database 12.2+ (e.g. the 19c development instances) raises the limit to 128 bytes — so a 31-char name compiles and installs fine on 19c dev and then fails on the customer. Same 19c-masks-it trap as the create_update_person_extra_info note in upload-api-patterns skill, generalised to all identifiers:

    • DDL objects (tables, indexes, constraints, sequences, columns, views, triggers, types, synonyms) → over-30 raises ORA-00972: identifier is too long at create time and the object is silently not created (installers tolerate the error and continue), so a missing unique index/constraint can also let bad data in.
    • PL/SQL identifiers (package names, and procedure/function names inside a package spec, standalone procedures/functions, parameter names) → over-30 raises PLS-00114: identifier too long (and cascades as PLS-00201/PLS-00307 at call sites).
    • The generated upload view name xxen_<prefix>_<hash>_u is bounded (prefix truncated to 18 + 4-char hash), so it is safe — but any hand-named view/table you add is not.
    • A report or upload SQL COLUMN ALIAS is an identifier too — an over-30 alias raises the same ORA-00972 when the report SQL is parsed, so the report fails for every customer on a pre-12.2 database while running fine on the 19c dev instances. Abbreviate the alias, not the entity prefix, following the existing content (postal_code -> post_code); the column heading is derived from the alias, so the rename is user-visible and must be carried into every template COLUMN_NAME and the matching workbook cacheField name. Check with lengthb(column_name)>30 against xxen_report_sql_columns after parsing (exclude the xxen_util.dff_columns output, whose names come from the site's own flexfield segments).
    • Enforced mechanically: ${CLAUDE_PLUGIN_ROOT}/scripts/lint_plsql.py rule R55 flags any over-30 procedure/function name or DDL object name (pre-commit hook and --all) — a 32-byte procedure name once passed every 19c dev compile and broke the package on a customer's 12.1.0.2 database.

55. Never parse SQL text with a greedy .+ regex under the n flag — scan it left to right instead. regexp_substr patterns chaining several unbounded .+ over a whole statement (dot matches newline under 'n') backtrack combinatorially AND pick the wrong text: .+ latches onto the LAST id/value alias anywhere in the statement (scalar subqueries, inline views, later union all branches), whereas Oracle names result columns from the FIRST select. xxen_api.value_equal_to_id is the reference scanner.

    • Symptom: pure CPU inside the regex engine — ASH shows ON CPU against whatever SQL called it and no slow SQL is findable; read the ASH Procedure/Code column instead of hunting for a statement.
    • Scanner shape: strip comments preserving literals; track string literals, quoted identifiers and bracket depth; split the select list on depth-0 commas up to the depth-0 from; take the trailing identifier as the alias (as optional, a bare/qualified column is named after itself, "Quoted" honoured); descend into the inline view or with block for select x.* from (..) x. Bracket depth is what keeps a scalar subquery's inner from from ending the select list.
    • PL/SQL trim/rtrim/ltrim strip only SPACES, not newlines or tabs: trim with an explicit set, ltrim(rtrim(s,g_ws),g_ws) where g_ws:=' '||chr(9)||chr(10)||chr(13)||chr(12).
    • Package-body constants must precede every subprogram body (PLS-00103 otherwise).
    • If a regex must stay, gate it behind a cheap linear regexp_instr for a literal token it requires (a non-matching regex still pays full backtracking), and \s is NOT whitespace inside a bracket expression — Oracle follows POSIX, so [\s.] matches backslash, s and dot; use [[:space:].].
    • Validate a parser against the whole LOV population AND synthetic cases (id-last select lists), comparing captured groups, not just the boolean result.

56. Prefer in (subquery) over exists (correlated subquery) for a plain membership test — it is shorter, needs no correlation predicate and no select null, and reads as what it is. Same minimalism principle as #37.

    -- Bad (correlated, longer, repeats the join column on both sides):
    exists (select null from fnd_conc_pp_actions fcpa where fcr.request_id=fcpa.concurrent_request_id and fcpa.action_type=7)
    -- Good:
    fcr.request_id in (select fcpa.concurrent_request_id from fnd_conc_pp_actions fcpa where fcpa.action_type=7)

    Keep exists only where in cannot express the test: a multi-column correlation that is not a simple (a,b) in (...), a not exists anti-join where the subquery column is nullable (not in returns no rows when the subquery yields a single null — see #43), or a correlation on something other than equality. Both forms give the same plan for a simple membership test, so the choice is readability, not performance. Note this is about a membership predicate, not about replacing a join: where the row must be filtered but not multiplied, a subquery (either form) is still required — a join fans the driving row out once per matching child row.

57. A predicate on an OUTER-JOINED table written without (+) silently DROPS the driving row — it does not "keep the row and null the column". With (+) syntax only predicates carrying (+) are join conditions; everything else is a post-join filter. a.id=b.id(+) and <filter on b without (+)> therefore behaves like an inner join whenever the driving row HAS matches: rows failing the filter are removed and Oracle does not re-null-extend the driving row, so a driving row disappears when ALL its matched rows fail. Rows with NO match still pass (null satisfies nvl(col,x)=x), which is exactly what hides the bug in testing — it only bites the entities that have child rows.

    • The shape to watch for is an nvl(<outer table>.<col>,<sentinel>)=<sentinel> "parent-level child row" filter. It must carry the (+) inside the nvl:

      -- Bad: an account whose receipt methods are ALL site-level loses its account row completely
      hca.cust_account_id=rcrm.customer_id(+) and
      nvl(rcrm.site_use_id,0)=0 and
      -- Good: stays a join condition, non-matching rows are null-extended
      hca.cust_account_id=rcrm.customer_id(+) and
      nvl(rcrm.site_use_id(+),0)=0 and

    • Audit rule: in any from list with old-style outer joins, every predicate naming an outer-joined alias must either carry (+) or be a deliberate "matched rows only" filter you can justify; sibling child tables joined the same way (profiles / contacts / bank accounts / receipt methods) must all be written the same — an odd one out missing the (+) is a typo. Verify by counting parents with vs without children before and after.
    • The remedy is not always (+): a filter on the outer-joined table's OWN column (e.g. nvl(rcrm.primary_flag,'Y')=decode(:p,'Y',nvl(rcrm.primary_flag,'Y'),'Y')) belongs in an inline view applied BEFORE the join — (select /*+ inline */ ... from ra_cust_receipt_methods rcrm where rcrm.primary_flag=decode(:p,'Y',rcrm.primary_flag,'Y')) rcrm — so non-primary rows never enter the join. Audit heuristic: a predicate line that names an outer-joined alias and contains no (+) token at all is a filter, not a join condition.
    • When backporting such a fix to a customer's older build, check which UNION branch that customer's PARAMETERS actually execute (e.g. Update Level = Site never runs the account branch); the request log's Parameters and binds section prints the deployed SQL, so confirm the predicates there rather than inferring them. ra_cust_receipt_methods has no org_id (keyed on customer_id + site_use_id only), so a site use in ANY operating unit can match.
    • Related to #23 (join flow) and #50 (no guard that cannot fire): this is the opposite failure — a predicate that fires when it must not.

58. Building a delimited list: derive the separator from the POSITION, never from the accumulator being non-null. l_list:=l_list||case when l_list is not null then '|' end||<value> is only correct when <value> can never be null: a null FIRST element leaves the accumulator null, the second element is appended with no separator, and the list comes back one element short — every positional consumer is then off by one (a null element in the middle is harmless). Fix: case when c.column_number>1 then '|' end (or a counter) — identical for a non-null first element, so a safe drop-in. The API output's column-data-type row in XXEN_REPORT.pkb is built this way for exactly this reason (a number column with no registered format has a NULL c.format); its sibling accumulators (l_api_column_headers, l_column_width) are safe only because their values cannot be null — do not "fix" them (#50).

59. Never put an XMLTABLE-based subquery inside a set-based DML predicate against a real table — the optimizer re-shreds the XML once per row of the target table. An in/exists subquery reading xmltable(... passing <clob/xmltype> ...) gets unnested into a semi-join; because the optimizer has no cardinality for XMLTABLE (8168 rows by default, cubed across a three-level shred) it drives from the real table and pushes the predicate into the subquery view, so the entire shred re-executes for every target row. The statement looks set-based and its cost is target_rows × cost_of_one_full_shred.

    • Symptom: a VIEW PUSHED PREDICATE VW_NSO_* (or a FILTER) whose subtree contains XMLTABLE EVALUATION, on the inner side of a NESTED LOOPS SEMI whose outer side scans the DML target; wildly inflated E-Rows for a tiny XML is the tell.
    • Fix: drive a cursor loop from the XML ALONE (no table in the cursor) so it is shredded exactly once, and make the DML body a per-iteration statement keyed on an indexed column (one iteration per entity in the XML, so the loop is not the cost). import_templates' template parameter exclusion insert in XXEN_REPORT.pkb is the reference. Cost scales with the target table AND the xml, so a batch import or a bigger customer is far worse than the dev measurement — same family as #58.
    • A plain JOIN of xmltables to a table has the same trap: with a nested-loop order driven by the table, the xmltables are re-evaluated per table row (a 34 MB batch file: 0.8 s xml-first, over 10 minutes parameters-first). The dev instances pick the xml-first hash plan, so only a customer shows it. Pin every such statement with leading(<xml aliases>), as import_templates does.
    • Testing caveat for xml round trips: xxen_api.import_xml(<clob>) does NOT strip CRs, while the real file path (bin/import_xml → FNDGFU → xxen_report_ebs.import_xml_file_name → import_xml_file) does, and export_file_data converts every chr(10) to chr(13)||chr(10) on the way out — feeding an export_report clob straight back into import_xml adds one chr(13) per line on each round trip. Round-trip tests go through a file and bin/import_xml.

60. decode(x,0,null,x) returns VARCHAR2, not NUMBER — use nullif(x,0). Oracle types decode from its FIRST result, and a null first result converts the return value to varchar2, so the "blank out a zero" idiom silently turns a number column into text: the report column parses as varchar2, Excel writes it as a string, and the user cannot sum or chart it. nullif(x,0) is the exact equivalent (both treat NULL input as NULL) and keeps the native type.

    • Do NOT "fix" it with to_number(decode(...)) — that round-trips number → varchar2 → number and is NLS-dependent. nullif is also shorter: decode(a-b-c,0,null,a-b-c) → nullif(a-b-c,0).
    • The type leak compounds in an aggregate: max(decode(x,0,null,x)) is a STRING max ('9' > '10'); sum() still works via implicit conversion but is NLS-dependent.
    • Detect with regexp_like(sql_text,'decode\(([A-Za-z0-9_.-]+),0,null,\1\)').
    • Verify the swap by re-parsing, not by reading the diff: the affected columns must flip varchar2 → number in xxen_report_sql_columns (the flip itself proves every operand was numeric), and the Excel output must write them as numeric cells (no t="s"). Identical on 11.2, so no 11i twin is needed.

61. xxen_util.clob_replace loops until nothing matches — never call it when the replacement contains the search text. It repeats replace_first_occurrence while dbms_lob.instr(result,search)>0, so an insertion that keeps the searched text inside it (<anchor> → <predicate> and <anchor>) re-matches its own output and grows the CLOB until temp space is gone; the session shows one UPDATE on write complete waits with lob reads in the millions. For a single insertion use xxen_util.replace_first_occurrence after proving the text occurs once; clob_replace is for replacements that remove or transform the searched text. The trap is data-dependent (only when the character before the anchor makes the search re-match), so a script that passed hundreds of reports can still hang on one — same family as #56 (parsers validated on the whole population).

62. A PL/SQL function inside a subquery's range or filter predicate is evaluated once per CANDIDATE ROW, not once per subquery execution — wrap it in (select f(...) from dual). The optimizer cannot prove a non-deterministic function is invariant, so a bound such as between col and mrp_calendar.date_offset(outer.a,outer.b,outer.c,1)-1 lands in the table-access filter and runs for every row the index range returns, although its arguments are constant for that execution. The scalar-subquery form evaluates it once and caches it, for identical output.

    • The call count is the KEY RANGE's row count, not the result's, whenever the bound column is missing from the index (mrp_forecast_updates.forecast_update_date is not in MRP_FORECAST_UPDATES_N1): every row of the key is fetched and filtered.
    • Signature: ASH shows the top-level statement ON CPU with no sql_plan_line_id and there is no slow SQL to find; the cost sits in v$sql as a recursive cursor belonging to the called package with executions in the hundreds of thousands. Read those recursive cursors' executions, not the plan.
    • Do NOT add deterministic to someone else's function to get the same effect; and when counting the saving, remember a select count(*) wrapper discards the select list, so it measures neither the scalar subqueries nor their function calls.

63. Never guess whether an input is an id or a display value - a routine is told which one it receives. Trying to_number(p_x) as an id and falling back to a lookup by code, testing regexp_like(p_x,'^[0-9]+$'), or accepting 'Y' and the translated Yes alike all misfire as soon as a code or name is numeric: a department code 5101 is taken as department id 5101. Numeric codes are common at customers (departments, lines, organizations, segment values, document numbers), while Vision data is mostly alphabetic, so such code passes every internal test.

    • Give each routine one input form. When the callers hold different forms, convert in the caller (an upload's server-side backstop resolves its ids to the display values the add-in sends, as upload_std_op does before calling validate_std_op_excel_row).
    • A reusable routine that must accept both takes an explicit indicator of what is passed ('id' or 'value', as validate_val's p_bind_column in XXEN_REPORT) and never infers it from the content.
    • The rule is about inputs that are processed or validated. A display column may fall back to the raw code when no translation exists (nvl(xxen_util.meaning(code,...),code)): showing the code is better than a blank.

64. Never give a result_cache function a high-cardinality key (code combination, segment value, row id) or have it read a table that transactions change; cache such results per session in a package collection, as Oracle's flex engine does for value security (fnd_plsql_cache in FND_FLEX_SERVER). Each result takes at least one 1 KB block of the instance-wide cache, a committed DML on any table the function reads invalidates every result of the function (a new gl_code_combinations row flushes all cached code combination results), and once the cache is full every new result scans the LRU chain under the Result Cache: RC Latch, which makes a full cache far slower than no cache. Keep result_cache for small, stable key spaces such as setup lookups. Store a session cache entry only after its evaluation succeeds: a default stored first stays cached when the evaluation raises, and for security that means access granted.

    • Signature: ASH on the report or LOV SQL dominated by latch free (P2 = the Result Cache: RC Latch number in v$latchname) and enq: RC - Result Cache: Contention, or by CPU in the calling PL/SQL frame; DBA Result Cache Statistics shows Current Size equal to Maximum Size with Delete Count Valid rising as fast as Create Count Success; DBA Result Cache Objects and Dependencies lists the function with hundreds of thousands of results.

65. Test a code for the value you mean, not for the absence of another: security_enabled_flag='N', not security_enabled_flag<>'Y'. When a column's values are known (a Y/N flag, an I/E indicator, a value set validation type), compare with the value the condition is about (include_exclude_indicator='E', validation_type in ('D','Y','F')) instead of negating the other one: it reads directly, and both forms exclude null, so they are equivalent whenever the listed values are all the column holds. Keep <>/not in for open-ended values; for an Enginatics Y-or-null flag (#41) the other value is is null.