Argos Coding Standards

PL/SQL Standards and Best Practices

Use Schemas instead of Public Synonyms

- Avoid the use of public synonyms. Always use explicit references to objects.

- Even though this makes it easier to code, sometimes a synonym may not exist.

- Also, if local tables exist, they are accessed first and then synonyms.

In this example, the SATURN Schema is used with the Table Name.

Incorrect

SELECT *
  FROM SPRIDEN;

Correct

SELECT *
  FROM SATURN.SPRIDEN;

Tuning considerations

Please verify that your SQL uses an efficient execution plan; use indexed columns in your WHERE clause to avoid full table scans, be sure that all tables are joined somehow in the WHERE clause to avoid cartesian products, etc. If available, use EXPLAIN PLAN or a tuning tool to verify that you are using an efficient execution plan.

Alignment, Casing and Indentation

- Keywords / Reserverd Words => UPPERCASE

- Align keywords, columns, statements and criteria

SELECT s.spriden_pidm
     , s.spriden_last_name || ', ' || s.spriden_first_name || ' ' || s.spriden_mi FullName
     , a.spraddr_atyp_code
     , CASE a.spraddr_atyp_code
            WHEN 'OF' THEN 'OFFICE'
            WHEN 'HR' THEN 'HOME'
            ELSE 'OTHER'
       END AS spraddr_desc
     , a.spraddr_street_line1
     , a.spraddr_street_line2
     , a.spraddr_street_line3
     , a.spraddr_city
     , a.spraddr_stat_code
     , a.spraddr_zip
     , c.amrcont_contact_date
     , c.amrcont_iden_code || ' - ' ||  g.guriden_desc
     , c.amrcont_scnt_code
  FROM ALUMNI.AMRCONT  c,
       SATURN.SPRIDEN  s,
       GENERAL.GURIDEN g,
       SATURN.SPRADDR  a
 WHERE c.amrcont_pidm                  = s.spriden_pidm
   AND s.spriden_pidm                  = a.spraddr_pidm
   AND s.spriden_change_ind           IS NULL
   AND a.spraddr_status_ind           IS NULL
   AND a.spraddr_atyp_code            IN ('OF', 'HR', 'PT', '01')
   AND c.amrcont_iden_code             = g.guriden_iden_code
   AND c.amrcont_contact_date    BETWEEN :main_DT_Start AND :main_DT_End
 ORDER BY c.amrcont_contact_date DESC
;

Avoid "NOT IN" clauses

- a NOT IN subquery may return a NULL value.

- instead, use NOT EXISTS, which invokes a correlated sub-query.

- A correlated subquery is a subquery that contains a reference to a table that also appears in the outer (main) query.

select s.*
from saturn.spriden s
where not exists (select p.pebempl_pidm
                    from payroll.pebempl p
                   where p.pebempl_pidm = s.spriden_pidm
                     and p.pebempl_empl_status ='T');

IN -vs- EXISTS

- In older versions of Oracle, an IN was less efficient than an EXISTS.

- However, now all IN sub-queries are rewritten by Oracle to use an EXISTS.

MINUS

- the MINUS can be used to exclude results with great performance.

JOINS

- INNER JOIN

- LEFT OUTER, RIGHT OUTER or FULL JOIN

Use either the New Technic of coding <type> JOIN / ON, or use the older style of joining in WHERE clause. Example of INNER JOIN:

** New Technique **

SELECT s.spriden_pidm
     , s.spriden_last_name || ', ' || s.spriden_first_name || ' ' || s.spriden_mi FullName
     , a.spraddr_atyp_code
  FROM SATURN.SPRIDEN s
 INNER JOIN SATURN.SPRADDR a
    ON s.spriden_pidm        = a.spraddr_pidm
   AND s.spriden_pidm       IN (385455)
   AND s.spriden_change_ind IS NULL
   AND a.spraddr_status_ind IS NULL;

** Old Technique **

SELECT s.spriden_pidm
     , s.spriden_last_name || ', ' || s.spriden_first_name || ' ' || s.spriden_mi FullName
     , a.spraddr_atyp_code
  FROM SATURN.SPRIDEN s,
       SATURN.SPRADDR a
 WHERE s.spriden_pidm        = a.spraddr_pidm
   AND s.spriden_pidm       IN (385455)
   AND s.spriden_change_ind IS NULL
   AND a.spraddr_status_ind IS NULL;

Cartesian Joins or Products

- Be CAREFUL to avoid accidental Cartesian Joins

- Be sure tables are joined on table keys / columns

Examples of a Cartesian Joins

SELECT * FROM SATURN.SPRIDEN, GENERAL.GOREMAL;

SELECT *
  FROM SATURN.SPRIDEN  s,
       GENERAL.GOREMAL e
 WHERE s.spriden_pidm = 385455
   AND e.goremal_emal_code = 'OAKU';

Note: If for some strange reason you actually want a Cartesian Join, you should use the CROSS JOIN. This is NORMALLY not recommended.

Argos Report Writing Standards

Schedules

• Schedules should be set to run during business hours or early evening. Running schedules prior to 8:00 AM should be avoided. This is the UTS Maintenance Window. During certain times or emergencies, UTS may need to use other early mornings for needed enhancements. Therefore, it is recommended to avoid times prior to 8:00 AM on any day.
• Schedule Names should be meaningful. A good recommendation is to use part of the Dashboard or Report Name. - If the schedule is for a special purpose, include that as well. (Ex. Monthly, Weekly, Annual, Specific Day)

Argos Data Block Design Standards

Forms and Objects

• When possible, forms should use a Oakland University or Department Branding.
• When using variables, buttons should be used to control execution.
• When using charts, logic should be used to control execution and even display.
• Queries should be tested outside of Argos for efficiency.

Note: As we learn more about the Evisions Argos product, these standards and best practices will grow and be revised to suit our environment more closely.

---