CODEWITHSUNDEEP
plsqlsql-update
Mushy
Mushy

Reputation: 2645

PL/SQL UPDATE -- Well, not updating

I am using a procedure 

CREATE OR REPLACE PROCEDURE update_rec(
    page_id   IN SSC_Page_Map.Page_ID_NBR%TYPE,
    page_type IN SSC_Page_Map.Page_Type%TYPE,
    page_dcpn IN SSC_Page_Map.Page_Dcpn%TYPE)
IS
BEGIN
  UPDATE SSC_Page_Map
  SET Page_Type     = page_type,
    Page_Dcpn       = page_dcpn
  WHERE Page_ID_NBR = page_id;
  COMMIT;
END;

to update my database table row. I confirm the procedure execute correctly but I don't see the update. I have commented out the update to confirm I have permission to modify the database and that succeeds.

Upvotes: 0

Views: 59

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

Use aliases for zero ambiguity:

CREATE OR REPLACE PROCEDURE update_rec(
  page_id   IN SSC_Page_Map.Page_ID_NBR%TYPE,
  page_type IN SSC_Page_Map.Page_Type%TYPE,
  page_dcpn IN SSC_Page_Map.Page_Dcpn%TYPE)
IS
BEGIN
  UPDATE SSC_Page_Map
  SET Page_Type     = update_rec.page_type,
    Page_Dcpn       = update_rec.page_dcpn
  WHERE SSC_Page_Map.Page_ID_NBR = update_rec.page_id;
  COMMIT;
END;

(Note: I have seen one case where someone added a column called something like p_xyz to a table, which caused no end of trouble for the client whose naming convention had p_ for all procedure/function parameters. The alias method, in contrast, works 100% of the time.)

Upvotes: 0

user330315
user330315

Reputation:

SET Page_Type = page_type updates the column with the current column value.

The visibility rules inside a PL/SQL program are such that the column name takes precedence over your parameter name. Give your parameters a different name and everything should be fine. A common coding convention is to prepend the parameters with p_ to identify them as parameters:

CREATE OR REPLACE PROCEDURE update_rec(p_page_id IN SSC_Page_Map.Page_ID_NBR%TYPE, 
                                       p_page_type IN SSC_Page_Map.Page_Type%TYPE, 
                                       p_page_dcpn IN SSC_Page_Map.Page_Dcpn%TYPE)

IS
BEGIN
   UPDATE SSC_Page_Map 
       SET Page_Type = p_page_type, 
           Page_Dcpn = p_page_dcpn 
   WHERE Page_ID_NBR = p_page_id;
   COMMIT;
END;

Upvotes: 3

Related Questions