| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: stored proc for update
A copy of this was sent to Rahul Sood <rsood_at_panix.com> (if that email address didn't require changing) On Mon, 30 Mar 1998 21:59:20 -0500, you wrote:
>The following simple update stored proc seems not to be having the
>desired effect. If I just execute the body of commands in the procedure
>as an anonymous block, it works. But the same block executed as a stored
>proc doesn't. Is one of the DMBMS packages reqd for this? I do have
>DBMS_STANDARD and DBMS_OUTPUT.
>
>select work_tel from tblEmployees where empid=1; // value of work_tel
>before invoking stored proc
>(Output from PL/SQL:)
>WORK_TEL
>---------------------------------
>411
>
>create or replace procedure spUpdate(id in number, phone in varchar2) as
>
>begin
> update tblEmployees
> set work_tel=phone
> where empid=id;
> commit;
>end;
>/
>(Output from PL/SQL:) Procedure created
>
>execute spUpdate(1,'5551212');
>(Output from PL/SQL:) procedure successfully completed.
>select work_tel from tblEmployees where empid=1; // value of work_tel
>after invoking stored proc
>(Output from PL/SQL:)
>WORK_TEL
>---------------------------------
>411
>
>the value is unchanged
>
>Pls post, or replies to rsood_at_panix.com
Well, you don't give us the whole table structure but I'll bet there is a column called ID in the table. If there is, the the scoping rules for PL/SQL would be such that the ID in the update statement is from the TABLE, not from the pl/sql parameter.
I like to preface all of my pl/sql variables with one of P_, L_, or G_. I use P_ for parameters to a routine. So, in your case, id would be p_id. L is local (local variables on the stack) and G is for global (global variables in a package body or specification).
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
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 Tue Mar 31 1998 - 00:00:00 CST
 |
 |