Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: PL/SQL Tip of the Month

Re: PL/SQL Tip of the Month

From: Joep Hendrix <joep.hendrix_at_wxs.nl>
Date: 1998/09/16
Message-ID: <3600038E.5902@wxs.nl>#1/1

info_at_revealnet.com wrote:
>
> From the "PL/SQL Pipeline" - a free internet community for Oracle developers
> worldwide. Visit the PL/SQL Pipeline to discuss technical issues with
> colleagues, download white papers and utilities, and more. The PL/SQL
> Pipeline is hosted by best-selling author Steven Feuerstein, and sponsored by
> RevealNet.
> -----------------------------------------------------------------------------
> -------------- SEPTEMBER PL/SQL TIP OF THE MONTH
>
> Qualify All PL/SQL References inside SQL Statements
>
> I have been telling PL/SQL developers for years now to never declare a
> variable or parameter with the same name database elements, such as tables
> and columns. You can give yourself a serious headache if you do this. For
> example, suppose I am working with the employee table. It has a column called
> employee_id. If I write a program like this:
>
> CREATE OR REPLACE PROCEDURE delete_employee (
> employee_id IN NUMBER)
> IS
> BEGIN
> DELETE FROM employee
> WHERE employee_id = employee_id;
> END;
>
> then my program clearly will not work as intended. In fact, it will always
> delete all rows from the table. You can fix this problem by changing the name
> of the parameter:
>
> CREATE OR REPLACE PROCEDURE delete_employee (
> emp_in IN NUMBER)
> IS
> BEGIN
> DELETE FROM employee
> WHERE employee_id = emp_in;
> END;
>
> But the problem doesn't necessarily go away forever. What if the DBA adds a
> column called "emp_in" to the employee table? Suddenly your program (which
> still compiles without error) works very differently. What's a developer to
> do?
>
> Well, this tip comes from L. Carl. Pedersen, and goes like this:
> Fully-qualify all references to PL/SQL data structures inside SQL. This rule
> would transform the delete_employee procedure to the following:
>
> CREATE OR REPLACE PROCEDURE delete_employee (
> emp_in IN NUMBER)
> IS
> BEGIN
> DELETE FROM employee
> WHERE employee_id = delete_employee.emp_in;
> END;
>
> Now, even if the DBA does add that emp_in column, the procedure will still
> work as designed, because there isn't any possibility of confusion.
>
> You can, by the way, qualify your PL/SQL references even if you are in an
> unnamed or anonymous block
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

I think the best solution in this matter is to use the Custom Development Method (CDM) which is developed by oracle Netherlands I think.

They use the advise the following convention: example:
cursor : c_cursor-name
cursor parameter: b_parameter-name

local variable	: l_variable-name
parameter	: p_paramer-name

etc.

Joep Hendrix Received on Wed Sep 16 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US