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.
SELECT * FROM SPRIDEN;
SELECT * FROM SATURN.SPRIDEN;
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.
- the MINUS can be used to exclude results with great performance.
- 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 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.