Oracle PL/SQL - Declare constant with constrainst inside procedure

Question

Is it possible to declare constants with constraints inside a procedure?

Something like:

declare
procedure proc
   (param1 IN VARCHAR2,
    param2 IN VARCHAR2,
    param3 IN NUMBER)
is
BEGIN
  my_const CONSTANT VARCHAR2(50) := param1  -- Notice the constraint where my_const is VARCHAR2(50)
  other_c  CONSTANT VARCHAR2(50) := 'My other constant!'

  -- Do something with my_const and other_c
END;

The idea to have a constraint is that I want to cause an error if someone supplies a value too long.

It's not possible to define a constraint at the parameter. I can't do this (at least I tried):

declare
procedure proc
   (param1 IN VARCHAR2(50),
    param2 IN VARCHAR2(50),
    param3 IN NUMBER)
is
BEGIN
  other_c  CONSTANT VARCHAR2(50) := 'My other constant!'

  -- Do something with other_c
END;

I know I can just check the length with an if and emit an exception if too long, but I'm looking for a simpler way.

Answer 1

You can pass the parameter directly into the value of a constant, but if it doesn't fit you will get a VALUE_ERROR. You will need to catch that error in your exception handling, or remove the exception handling if you want the error to be raised.

DECLARE
    PROCEDURE proc (param1 IN VARCHAR2, param2 IN VARCHAR2, param3 IN NUMBER)
    IS
        my_const   VARCHAR2 (5) := param1;   -- Notice the constraint where my_const is VARCHAR2(5)
        other_c    VARCHAR2 (5) := 'other';
    BEGIN
        -- Do something with my_const and other_c
        NULL;
    END;
BEGIN
    proc ('long_val', 'test', 0);
EXCEPTION
    WHEN VALUE_ERROR
    THEN
        DBMS_OUTPUT.put_line ('Something doesn''t fit');
END;
/