Home » PL/SQL » Don’t do that in PL/SQL

Don’t do that in PL/SQL

Start here


1.Declare variables as VARCHAR2(N)

  • Use %TYPE instead of VARCHAR2(N), unless it is a “single point defintion”.
  • Incase the variable stand for combine column, use SUBTYPE and define subtype at the declaration of the package in order to tuning.

2.Overload lazily

  • Overleading means we have more than 1 func/proc have the same name with different parameter lists (aka static polymorphism)
  • Don’t copy the code, let reuse the code through subprogram.  The trick here is use uninitiated variable for  nonexistence parameters.

3.Use low-level error handling logic

  • Don‘t call RAISE_ERROR_APPLICATION/explicitly insert into log table or write to file/ call buil-in func in each handler.
  • Use generic or shared  error management.

4.Rely on row -by-row processing

  • With FOR ALL and BULK COLLECTION we can eliminate the context switches PL/SQL Runtime Engine to SQL Engine.

5.Write spaghetti code

  • Use top down technique to make the code easy to read and re-useable.
  • An executable block should not more than 50 lines long.


  1. haind says:

    Blog cua minh la http://haind.wordpress.com/
    Rat muon lam quen voi nguoi cung so thich. Co gi lien he nhe’.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: