pl/sql variable naming
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: pl/sql variable naming

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639

    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

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    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.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    I'd use v_empname, but you could use anything a-z

    Heck, use local_variable_empname if you like to type alot.

    Ken

  6. #6
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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

    Last edited by kris123; 04-11-2005 at 08:48 PM.

  7. #7
    Join Date
    Jan 2004
    Posts
    162
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width