1

Topic: How to make restrictions in type pl/sql?

I want to make type such: number from 0 to 20. That there was a check at variable assignment. If is not present - to throw an exception.

2

Re: How to make restrictions in type pl/sql?

-2;
Which? There ton only. Give a reference, .

3

Re: How to make restrictions in type pl/sql?

AnnitaBells;
User-Defined PL/SQL Subtypes: Constrained Subtypes

4

Re: How to make restrictions in type pl/sql?

AnnitaBells;
Though pure number from 0 to 20 it will not be possible to set. Range it is accessible only for pls_integer or it . That implies restriction on .

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/block.html#GUID-9ACEB9ED-567E-4E1A-A16A-B8B35214FC9D wrote:

Restriction on RANGE high_value. low_value
Specify this clause only if base_type is PLS_INTEGER or a subtype of PLS_INTEGER (either predefined or user-defined)

5

Re: How to make restrictions in type pl/sql?

AnnitaBells wrote:

I Want to make type such: number from 0 to 20

It is possible to put any logic of check in pl/sql function. And exceptions therefrom to return.
Or so it is not interesting?

6

Re: How to make restrictions in type pl/sql?

merch wrote:

it is passed...
It is possible to put any logic of check in pl/sql function. And exceptions therefrom to return.
Or so it is not interesting?

And made. Thanks.
AmKad;
Thanks for an interesting variant!

7

Re: How to make restrictions in type pl/sql?

SQL> CREATE OR REPLACE
2 TYPE NUMBER_0_20
3 AS OBJECT (
4 N NUMBER;
5 CONSTRUCTOR FUNCTION NUMBER_0_20 (
6 SELF IN OUT NOCOPY NUMBER_0_20;
7 N NUMBER
8)
9 RETURN SELF AS RESULT
10)
11 /
Type created.
SQL> CREATE OR REPLACE
2 TYPE BODY NUMBER_0_20
3 AS
4 CONSTRUCTOR FUNCTION NUMBER_0_20 (
5 SELF IN OUT NOCOPY NUMBER_0_20;
6 N NUMBER
7)
8 RETURN SELF AS RESULT
9 IS
10 BEGIN
11 IF NVL (N, 1) BETWEEN 0 AND 20
12 THEN
13 SELF.N: = N;
14 RETURN;
15 END IF;
16 RAISE_APPLICATION_ERROR (
17-20500;
18 ' Value must be between 0 and 20.'
19);
20 END;
21 END;
22 /
Type body created.
SQL> set numwidth 20
SQL> SELECT NUMBER_0_20 (3.141592653589793238)
2 FROM DUAL
3 /
NUMBER_0_20 (3.141592653589793238) (N)
-----------------------------------------------------------------------------------------------------------------
NUMBER_0_20 (3.141592653589793238)
SQL> SELECT NUMBER_0_20 (55)
2 FROM DUAL
3 /
SELECT NUMBER_0_20 (55)
*
ERROR at line 1:
ORA-20500: Value must be between 0 and 20.
ORA-06512: at "SCOTT.NUMBER_0_20", line 15
SQL>

SY.

8

Re: How to make restrictions in type pl/sql?

SY;
How to make restrictions in type pl/sql ?
....
stax

9

Re: How to make restrictions in type pl/sql?

Stax wrote:

How to make restrictions in type pl/sql ?

Fractional it is similar in any way.
SY.