Re: Why can't I use a INput parameter to make a update ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/11/13
Message-ID: <34745cab.20310214@newshost>#1/1

Its because you are basically executing:

   update emp

      set ename = ename
    where empno = empno;

That update works, it just updated every record in the table, setting the ename field to the value in the ename field.

If you use a PL/SQL variable that is the same name as the column in the table, the parse will resolve that name to be the name in the table NOT the pl/sql variable.

When I write procedures, I like to preface my IN parameters with P_ and local variables with L_, for example:

procedure do_update( p_ename in varchar2, p_empno in number ) is
begin

   update emp set ename = p_ename where empno = p_empno; end;

That removes any ambiguity. Alternatively, you can use one of the two following methods to explicity refer to the pl/sql variables in the sql statement:

<<main>>
declare

    ename varchar2(25) default 'NewName';     empno number default 7934;
begin

    update emp set ename = ename where empno = empno;  

    dbms_output.put_line( sql%rowcount || ' rows updated' );  

    update emp

       set ename = main.ename
     where empno = main.empno;
 

    dbms_output.put_line( sql%rowcount || ' rows updated' ); end;
/

In the above block, the first update will update all records in the EMP table, since the emp table has the columns ename and empno. The second update though will only update 1 record (if any) since we have

• named the block <<main>>
• qualified the reference to ename and empno in the update statement.

The following procedure is virutally identical except we see in the procedure we can simply qualify our pl/sql variables with the procedure name...

create or replace procedure do_update( ename in varchar2, empno in number ) as
begin

    update emp set ename = ename where empno = empno;  

    dbms_output.put_line( sql%rowcount || ' rows updated' );  

    update emp

       set ename = do_update.ename
     where empno = do_update.empno;
 

    dbms_output.put_line( sql%rowcount || ' rows updated' ); end;
/
exec do_update( 'NewerName', 7934 )

See Chapter 2, section on 'Scoping and Visibility' in the pl/sql manual for more info.

On Thu, 13 Nov 1997 18:35:28 +0100, Jordi Bellver <jbellver_at_ipgrup.com> wrote:

>
>
> Hi to everybody !
>
> First of all, sorry for my poor level of english ! :(
>
> Second...
>
> Working with PL/SQL 1.3.3 under Oracle7, when in a procedure I
>receive some parameters (IN parameters), I can't use them to make a
>insert nor update:
>
> Imagine that profes is a parameter, then
>
>------------------------------------------
> UPDATE usuari u
> SET u.profes = profes
> WHERE u.nomrec = nomrec;
>------------------------------------------
> dosn't function, but
>
>------------------------------------------
> cadena char(30);
>
>begin
>
>cadena:=profes;
>
> UPDATE usuari u
> SET u.profes = cadena
> WHERE u.nomrec = nomrec;
>------------------------------------------
>
>works correctly !! Why ??
>
> thank's in advance !
>
>bye
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Nov 13 1997 - 00:00:00 CST