Oracle PL/SQL Update Procedure

Here is a short Oracle stored procedure with an Update function. It took me longer then it probably should have to discover the correct syntax to get the Update working correctly. One thing to be sure of is to make sure your variable names are not the same as your column names, even if the case of the letters is different. For example if you have a column named FIELD_ONE, you do not want to have a variable named field_one. Here is the update example:

Here is the Package Header:


create or replace
PACKAGE PACKAGEUPDATE
IS
-- Procedure that updates values in the table
PROCEDURE ProcUpdate
(
id IN TABLE_NAME.FIELD_ID%TYPE,
in_field_one IN TABLE_NAME.FIELD_ONE%TYPE,
in_field_two IN TABLE_NAME.FIELD_TWO%TYPE
);

END PACKAGEUPDATE;


Here is the Package Body:


create or replace
PACKAGE BODY PACKAGEUPDATE
IS

PROCEDURE ProcUpdate
(
id IN TABLE_NAME.FIELD_ID%TYPE,
in_field_one IN TABLE_NAME.FIELD_ONE%TYPE,
in_field_two IN TABLE_NAME.FIELD_TWO%TYPE
)
IS
BEGIN
UPDATE MODAL_REQUEST
SET
FIELD_ONE = in_field_one,
FIELD_TWO = in_field_two
WHERE
FIELD_ID = id;

EXCEPTION
WHEN OTHERS THEN
PACKAGEERR.ERR_STOP;
RAISE;

END ProcUpdate;

END PACKAGEUPDATE;

The update procedure takes in 3 parameters, an id, and two fields. The procedure updates the columns FIELD_ONE and FIELD_TWO with the values passed in in_field_one and in_field_two respectively. It does this where the FIELD_ID column equals the value passed in to id.

The EXCEPTION is just a call to a general exception procedure (PACKAGEERR.ERR_STOP), if anyone wants to see an example let me know. Also, if anyone has any other questions or concerns feel free to leave me a comment.

Shane