pl/sql variable naming

Hi Friends,

I created a pl/sql program and the declare portion a have a variable
EMPNAME VARCHAR2(25);
This variable has the same empname column name in the table EMP that
im accessing.
How do i qualify the two so that i can reference each one?
The program didnt get a duplication naming error.

Thanks a lot

Generally variable naming conventions circumvent this sort of issue. However it is perfectly acceptable to prefix the variable name with its scope (i.e. the name of its package / procedure or block) to further qualify it, e.g. procedure_name.variable_name.

A popular method is to prefix parameter names with p_ and variables that are declared inside the procedure/function as l_ (for "local") so your empname would then be l_empname.

Don't neglect the use of %Type in your code either ...

Code:

---

l_empname emp.empname%Type;

---

... is more robust.

I prefer something like:

Code:

---

declare

  p1 VARCHAR2(1000);
  p2 DATE;
  p3 NUMBER;
begin...

---

That way, you can demonstrate your value to the corporation every time a change is needed. :D :D :D

I'd use v_empname, but you could use anything a-z

Heck, use local_variable_empname if you like to type alot.

Ken

Thanks mahal,

So wheres the answer dearests?

I have a code like this in my pl/sql program:

declare
empname1 emp.empname%Type;
cursor c1 is select empname from emp where status is null;
begin
open c1
loop
fetch c1 into empname1;
update emp set status='ACTIVE' where empname=empname1;
...blah blah blah

The question is I overlooked that the EMP table had also
an EMPNAME1 column, and the program used the column instead of
the declared variable, so it updated the whole table every time
it passes the loop. So how would I qualify so that I will reference
the empname1 in the declare portion, and not the EMPNAME1 column
in the table EMP.

Thanks

:)

Yes you would give the anonymous block a label and prefix the variable with the block label, e.g. anonymous_block.variable_name.

Alternatively stop using anonymous blocks for production code.