| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Can insert but not update in stored procedure ?
A copy of this was sent to "Gerry West" <gwest_at_skyconnect.com> (if that email address didn't require changing) On 30 Jan 1998 19:13:07 GMT, you wrote:
>Hi All,
>
>I've attempted my first stored procedure with partial success.
>I can insert but not update, do I need some kind of special privilege
>granted ?
No, the update worked -- just not as you expected.
>Also what is the difference between IS and AS in the procedure creation ?
>
none other then they are spelled different :)
>I executed it in SQL*PLUS using the following lines:
>
[snip]
>
>Compiled Procedure:
>
>CREATE OR REPLACE PROCEDURE sp_insert_user( userid IN NUMBER, user_name IN
>VARCHAR, password IN VARCHAR,
> security_level IN NUMBER,
>paging_phone_number IN VARCHAR,
> pin_number IN VARCHAR
> )AS
>BEGIN
> -- Update an existing user
> UPDATE usrs_users
> SET user_name = user_name,
> password = password,
> security_level = security_level,
> paging_phone_number = paging_phone_number,
> pin_number = pin_number
> WHERE user_id = userid;
>
The problem here is that password for example is both a column name in your table and a pl/sql variable. when the pl/sql parser sees "set password = password" it finds password the database column and stops. It is setting the database column password equal to the database column password -- it doesn't change anything.
2 ways around this
Way 1: qualify your pl/sql variables:
create or replace procedure foo( empno in number, ename in varchar2 )
as
begin
update emp set ename = foo.ename where empno = foo.empno;
end;
/
Use the procedure name to qualify the pl/sql variable name so that there is a difference between ENAME the column and FOO.ENAME the variable
Way 2: use a naming convention.
I like to prefix my pl/sql parameters with p_, local variables with l_. For example:
create or replace procedure foo( p_empno in number, p_ename in varchar2 ) as
l_some_local_variable number;
begin
update emp set ename = p_ename where empno = p_empno;
end;
/
Hope this helps.
>
>Thanks in advance,
>Gerry
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
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 Fri Jan 30 1998 - 00:00:00 CST
 |
 |