Bullshit Bingo

Appearance

Naming conventions ​

Usage of object types ​

Ok to useWith cautionRed flag
TablesViewsBusiness logic in triggers
SequencesTypesJava / C / etc. procedures
IndexesStandalone procedures and functions
PackagesMaterialized views

Common ​

All object names are in single and self describing.

Length of object names is limited to 30 characters.

Object names have underscore separated three letter prefix describing business domain.

ANSII SQL syntax must be used wherever possible.

All object names must have prefix of 3 letters representing modules.

Oracle reserved words must be in upper case

sql
SELECT word FROM v$reserved_words WHERE reserved = 'Y';

Tables ​

Table names are in single and self describing.

Tables and columns must have comments.

If name is too long, then use abbreviations.

Allowed data types: VARCHAR2(X CHAR) - max 2000 char, CHAR, NUMBER(X,Y), CLOB, DATE, TIMESTAMP(6), BLOB.

Column names are in single.

Primary key is named: id. Data type: NUMBER(19, 0).

Foreign keys are named: id_table name.

NOT NULL constraints must be defined, where needed.

Comments are mandatory for tables and columns.

Primary key constraint must be named: pk_table name.

Foreign key constraint must be named: fk_table name. N.B. Index is not automatically created and should be added as well.

Enumerated fields must have check constraints.

JSON field must have JSON constraint.

sql
CREATE TABLE app_storage (
   id NUMBER(19) NOT NULL,
   guid CHAR(32 CHAR) DEFAULT SYS_GUID() NOT NULL,
   id_user NUMBER(19),
   file_name VARCHAR2(2000 CHAR),
   file_size NUMBER(19),
   file_ext VARCHAR2(30 CHAR),
   content BLOB,
   sharing CHAR(1 CHAR) DEFAULT 'Y' NOT NULL,
   created TIMESTAMP(6) DEFAULT SYSTIMESTAMP NOT NULL
)
LOB(content) STORE AS SECUREFILE(
   CACHE
   NOLOGGING
);
/

COMMENT ON TABLE app_storage IS 'Table for storing and processing attachment data';
..
COMMENT ON COLUMN app_storage.id_user IS 'User who created attachment. Reference to APP_USERS.ID';
COMMENT ON COLUMN app_storage.sharing IS 'Is attachment shareable to other users (Y - yes, N - No)';
/

ALTER TABLE app_storage ADD CONSTRAINT pk_app_storage PRIMARY KEY (id);
/

ALTER TABLE app_storage ADD CONSTRAINT fk_app_storage_user FOREIGN KEY (id_user) REFERENCES app_users(id);
CREATE INDEX idx_app_storage_user ON app_storage(id_user) ONLINE;
/

ALTER TABLE app_storage ADD CONSTRAINT ch_app_storage_sharing CHECK sharing IN ('Y','N');
/

Views ​

View names must have prefix v_.

Sequences ​

Sequence shall be named seq_table_name.

Indexes ​

Indexes shall be named idx_table_name_column_name(s).

Unique indexes shall be named idq_.

PL\SQL ​

Package, procedure and variable names are in single an self describing.

Input and output variables must reference table column data types (..%TYPE) whenever possible.

Prefix, purpose

  • p_ Incoming parameters
  • v_ Local variables
  • g_ Global variables
  • vc_ Local constants
  • gc_ Global constants
  • c_ Cursors
  • e_ Exception
  • r_ Outgoing parameters
  • r_ Record
  • t_ Type

See Ordsify for procedure naming conventions.