CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Jovan Jovanović
Jovan Jovanović

Reputation: 92

Adding a constraint to a body of a distinct type PL/SQL

I need to make a PL/SQL type that can take only numbers in range from 10000000 to 9999999999999 (all positive numbers from 8 to 13 numbers in length). I'm using SQL Developer 11g. So far I've been successfully using:

CREATE OR REPLACE TYPE someType AS OBJECT (
tip NUMBER (13)
) FINAL;

CREATE TABLE someTable(
    ID NUMBER PRIMARY KEY,
    PIN someType,
    check ( PIN.tip between 100000000 and 9999999999999)
    )

How can I put the check clause from someTable into body of someType?

CREATE OR REPLACE TYPE someType AS OBJECT (
    tip NUMBER (13)
    ) FINAL;

CREATE TYPE BODY someType as...?

Upvotes: 1

Views: 102

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

You could create a CONSTRUCTOR which raises user defined EXCEPTION for values beyond range.

 CREATE OR REPLACE TYPE sometype AS object
(
  tip NUMBER (13),
  CONSTRUCTOR
  FUNCTION sometype ( tip NUMBER )
    RETURN SELF AS RESULT
 ) FINAL; 


CREATE 
OR REPLACE TYPE BODY sometype AS 
 CONSTRUCTOR FUNCTION sometype 
                 ( tip NUMBER )
        RETURN SELF AS RESULT AS tip_beyond_range EXCEPTION;
        PRAGMA EXCEPTION_INIT(tip_beyond_range, -20101);
        BEGIN
                IF tip BETWEEN 100000000
                        AND 9999999999999 THEN
                        self.tip := tip;
                        RETURN;
                  ELSE
                        raise_application_error(-20101, 'ERROR : TIP SHOULD BE BETWEEN 100000000 and 9999999999999');
                END IF;
      END ;
END;

Now, the second call in the below block will result in error.

DECLARE
  t1  sometype;

BEGIN
  t1 := NEW sometype(100000000);
  t1 := NEW sometype(10000000);
END;
/    

ORA-20101: ERROR : TIP SHOULD BE BETWEEN 100000000 and 9999999999999 ORA-06512: at "SQL_ZVRGRVBJDGVKVLZDKPHRMMRDP.SOMETYPE", line 12
ORA-06512: at line 6
ORA-06512: at "SYS.DBMS_SQL", line 1721

Upvotes: 1

Related Questions