Implicit rounding on INSERT

Last week I had another quiz on the PL/SQL Challenge. There was a twist in its tail which meant that only about a quarter of players got it right. 


Consider the following table and procedure.

CREATE TABLE plch_products
(
   product_id     NUMBER (2, 0)
 , product_name   VARCHAR2 (10)
 , stage          NUMBER (2, 0)
 , CONSTRAINT plch_products_pk PRIMARY KEY (product_id)
)
/

BEGIN
   INSERT INTO plch_products
        VALUES (1, 'Mouse', 10);
   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE ins_upd_product 
        (i_product_id     IN NUMBER
       , i_product_name   IN VARCHAR2)
IS
BEGIN
   INSERT INTO plch_products (product_id, product_name, stage)
        VALUES (i_product_id, i_product_name, 20);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      UPDATE plch_products
         SET product_name = i_product_name, stage = 30
       WHERE product_id = i_product_id;
   WHEN OTHERS
   THEN
      UPDATE plch_products
         SET product_name = i_product_name, stage = 40;
END;
/

If I pass in a product_id of 1.1, the value will get implicitly rounded during the INSERT to match the scale/precision of the table column. Because of this the product_id gets rounded to 1 and the insert fails due to a duplicate key. However in the exception handler, the UPDATE does NOT round the value for comparison so no rows match the criteria and none are updated.. 


So what can we do to prevent this problem.


Firstly, at least in 10g, the PL/SQL warnings don't cover scale/precision mismatches. They only cover data type mismatches (date to number, or even CHAR to VARCHAR2).


%TYPE anchoring can help, but it has to be in the declare section. Using %TYPE in the procedure signature doesn't help because, again, those just relate to data type.

CREATE OR REPLACE PROCEDURE ins_upd_product 
        (i_product_id     IN NUMBER
       , i_product_name   IN VARCHAR2)
IS
  v_product_id    plch_products.product_id%type;
  v_product_name  plch_products.product_name%type;
BEGIN
   v_product_id := i_product_id;
   v_product_name := i_product_name;
   INSERT INTO plch_products (product_id, product_name, stage)
        VALUES (v_product_id, v_product_name, 20);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      UPDATE plch_products
         SET product_name = v_product_name, stage = 30
       WHERE product_id = v_product_id;
   WHEN OTHERS
   THEN
      UPDATE plch_products
         SET product_name = v_product_name, stage = 40;
END;
/

With this variation, the rounding happens within the PL/SQL assignment. The rounded value is used in both the insert and the update, so the latter will succeed. Moreover, if someone passes an excessively large product name to the procedure, this will get picked up on the relevant assignment line, rather than in the execution of the update statement.


Personally, I prefer the assignment statements to happen after the BEGIN, rather than assigning the value in the declaration section. This allows for any exceptions to be caught in the local exception handler. The disadvantage is that you can't use a CONSTANT.