CODEWITHSUNDEEP
oracle-databaseplsqlconstantsconstraintsprocedure
user5507535
user5507535

Reputation: 1800

Oracle PL/SQL - Declare constant with constrainst inside procedure

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.

Upvotes: 0

Views: 1325

Answers (2)

EJ Egyed
EJ Egyed

Reputation: 6094

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;
/

Upvotes: 1

Elzzz
Elzzz

Reputation: 131

You can try sth. like this - inner block of code with declaration of your constant and assignment of the input param; you can also include exception block inside your procedure; For presentation purposes I added this block to block which is calling your proc.

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE proc (
    param1   IN   VARCHAR2,
    param2   IN   VARCHAR2,
    param3   IN   NUMBER
) IS
BEGIN
  -- Do something with other_c
    DECLARE
        my_const   CONSTANT VARCHAR2(50) := param1;
        other_c    CONSTANT VARCHAR2(50) := 'My other constant!';
    BEGIN
        dbms_output.put_line('other_c'
                             || chr(10)
                             || other_c);
        dbms_output.put_line('my_const'
                             || chr(10)
                             || my_const);
    END;
END;
/

and here's an example method call:

BEGIN
    proc(param1 => 'sdfasdfsadfsdfadsfasfasdfsadfa32234fdasdfsadfsdfsadfaf', param2 => 'something', param3 => 1);
EXCEPTION
    WHEN value_error THEN
        dbms_output.put_line('Invalid input param!');
END;
/

Upvotes: 1

Related Questions