PL/SQL is a case-independent language, unless you specifically set up Oracle to be case-sensitive or explicitly define object names within the “” marks. In our careers, we have seen the use of camelBack, all_lower_case, crammedupvariablenames, and Mixed_Variations. For clarity, this reference will use the standards outlined in Oracle’s wiki—mainly these:

  • All keywords and Oracle supplied objects are UPPERCASE.

  • User-defined variables and objects are lowercase.

  • There will be no camelBackScrunchedUpVariablesThatAreDifficultToRead.

  • All words are separated with an underscore (_) symbol.

 

We also align all long-running code blocks vertically, as follows:

SQL> SELECT  contact_id
  2       ,  member_id
  3       ,  first_name
  4       ,  last_name
  5       ,  last_updated_by
  6       ,  last_update_date
  7    FROM  video_store.contact
  8   WHERE  member_id = 1001
  9     AND  contact_type = 1003
 10  /

Variable Data Types:

Data Type

Prefix

Example

Comment

Boolean

B

lb_employee_status

Prefix common variables

Date

D

ld_hire_date

Number

N

gv_bonus_percentage

Varchar2

V

gv_department

Cursor

cur, c

c_item

Prefix cursors and records like this

Record

rec, r

r_employee

Type

typ, t

typ_monthly_sale

Associative array

aa

aa_sales_rank

Lexicon varies

Varray

va

va_top_sales_item

Lexicon varies

Nested table

Nt

nt_customer_address

Lexicon varies

Exception

ex, e

ex_bonus_too_high

Table type

Tt

tt_customer

Lexicon varies

 

Variables:

Scope

Prefix

Example

Comment

Global Private Variable

G

gd_hire_date

d – date

Local Variable

L

lv_first_name

v – varchar

Parameter

i, o, io

pi_employee_id

i – in, o – out, io – in out
Advertisements

Published by Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.