Oracle stored procedure variable naming convention

Question

When writing a stored procedure in oracle, PL/SQL there are lots of naming convention followed for writing the parameter names.

1. PROCEDURE PROC_MY_STORED_PROCEDURE (sFirstName VARCHAR2, nId NUMBER, oCursor REF_CURSOR)

   PROCEDURE PROC_MY_STORED_PROCEDURE (first_name_in VARCHAR2, Id_in NUMBER, o_Cursor REF_CURSOR)

2. Should the local variable prefix with l or l_ and global with g or g_

Are there any other good naming convention to follow?

What are the best practices to follow.

Answer 1

I'd put the highest priority on picking a naming scheme that clearly differentiates between local (or global) variables and column names. Its not as important whether the prefix is l_ for local or v_ for variable or whatever.

Another situation to catch is where a parameter or variable contains a date (or potential date) in a string format (eg loaded from a file or passed from a web-page and not yet validated). Using a naming convention (such as an _DATEC suffix) allows you to clearly differentiating these from real dates and avoid implicit conversions. Depending on your application, you may want to differentiate sanitised variables from unsanitised one (ie whether they've been checked for potential SQL injection or XSS/other HTML malware).

Variable names aren't case-sensitive, and IDE formatters may change the case based on preferences. So I prefer underscore separators between words rather than relying on camelCase.