Oracle Development - Best Practice

From: <jaysingh1_at_optonline.net>
Date: Sat, 07 Feb 2004 18:35:07 -0500
Message-id: <1260796125e302.125e3021260796@optonline.net>

Dear All,

We are starting a new oracle development project and my boss wants me to prepare "Oracle Development- Best practice" document/presentation kind of stuff. Basically this is to avoid common mistakes during the development cycle.

I have few points..

For example,

1. While writing pl/sql, use the correct datatype so that implicit conversion will be avoided
2. Use bind variable to avoid unnecessary parsing
3. Use BULK COLLECT, % ATTRIBUTES wherever required
4. MODULARITY Write the code that fit into your screen size. Through successive refinement, you can reduce a complex problem to a set of simple problems that have easy-to-implement solutions.
5. EXCEPTION WHEN OTHERS is almost always a BUG unless it is immediately followed by a RAISE.Use WHEN OTHERS exception as the last resort and handle exceptions.

For Example,
EXCEPTION
  WHEN OTHERS THEN

      if (sqlcode=-54) then
            .... deal with it.
      else
            RAISE;
      end if;

6) Tom's Mantra         

	If (possible in SQL)
		do it;
	else if(possible in PL/SQL)
		do it;
	else if(possible in JAVA)
		do it;
	else


	..

	..

	end if;
	

7)% ATTRIBUTES Use %TYPE and %ROWTYPE attributes. No code change is required when schema structure changes.

8) BEFORE VS AFTER TRIGGER NEVER USE BEFORE TRIGGER FOR VALIDATIONS. Use BEFORE triggers ONLY to modify :NEW value.

AFTER row triggers are slightly more efficient than BEFORE row triggers. With BEFORE row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with AFTER row triggers, the data blocks must be read only once for both the triggering statement and the trigger.

These are only few points w.r.t oracle developers. I like to get more info from you.

Your help would be really appreciated.

Thanks
Jay

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sat Feb 07 2004 - 17:35:07 CST