oracle constraints datatype

Question

I have created a table with datatype smallint.

create table test(
     A smallint, 
     constraints ACHECK check(A between 1 and 5));

I want to add constraint that only allow users to add integer value range between 1~5. But, even with the constraints, I am still able to insert floating point value which gets round up automatically.

insert into test values(3.2);

How do I make this code to show an error? I am not allow to change datatype.

Answer 1

You cannot do what you want easily. Oracle is converting the input value 3.2 to an integer. The integer meets the constraint. The value 3 is what gets inserted. The conversion happens behind the scenes. The developers of Oracle figured this conversion is a "good thing".

You could get around this by declaring the column as a number and then checking that it is an integer:

create table test (
     A number, 
     constraints ACHECK check(A between 1 and 5 and mod(A, 1) = 0)
);

Answer 2

As far as the requirement is NOT TO CHANGE THE DATA TYPE, but it doesn't say anything regarding creating new objects, I came up with a very complicated solution which does the trick but I would have preferred by far to change the data type to number and use a normal constraint.

The main problem here is that the round up of the value is done after parsing the statement, but before executing. As is an internal mechanism , you can't do anything about it. You can easily see that happening if you use a trigger and display the value of :NEW before inserting or updating the column.

However, there is a trick. F.G.A. got the original value passed to the statement before parsing. So, using a policy with a handler and two triggers make the trick.

Let me go into details

SQL> create table testx ( xsmall smallint );

Table created.

SQL> create table tracex ( id_timestamp timestamp , who_was varchar2(50) , sqltext varchar2(4000) );

Table created.

SQL> create or replace procedure pr_handle_it (object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2) 
is
  begin
    -- dbms_output.put_line('SQL was: ' || SYS_CONTEXT('userenv','CURRENT_SQL'));
    insert into tracex values ( systimestamp , sys_context('userenv','session_user') , sys_context('userenv','current_sql') );
    commit;
end;
/

Procedure created.

SQL> BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema => 'MYSCHEMA',
      object_name => 'TESTX',
      policy_name => 'MY_NEW_POLICY',
      audit_condition => null,
      audit_column => 'XSMALL',
      handler_schema => 'MYSCHEMA',
      handler_module => 'PR_HANDLE_IT',
      enable => true,
      statement_types => 'INSERT, UPDATE, DELETE'
    );
END;
/

PL/SQL procedure successfully completed.

SQL> create or replace trigger trg_testx before insert or update on testx 
referencing new as new old as old
for each row
begin
if inserting or updating
then
    dbms_output.put('  New value is: ' || :new.xsmall);
    dbms_output.put_line('TRIGGER : The value for CURRENT_SQL is '||sys_context('userenv','current_sql'));
    insert into tracex values ( systimestamp , sys_context('userenv','session_user') , sys_context('userenv','current_sql') );
end if;
end;
/

Trigger created.

SQL> create or replace trigger trg_testx2 after insert or update on cpl_rep.testx 
referencing new as new old as old
for each row
declare
v_val pls_integer;
begin
    if inserting or updating 
    then
        select regexp_replace(sqltext,'[^0-9]+','') into v_val 
        from ( select upper(sqltext) as sqltext from tracex order by id_timestamp desc ) where rownum = 1 ;
        if v_val > 5 
        then 
            raise_application_error(-20001,'Number greater than 5 or contains decimals');
        end if;
    end if;
end ;
/ 

Trigger created.

These are the elements:

-One trace table to get the query before parsing

-One FGA policy over update and insert

-One procedure for the handler

-Two triggers one before ( got the query and the original value ) and one after to evaluate the value from the statement not the one rounded up.

Due to the fact that the triggers are evaluating in order, the before one inserts the original value with the decimal and it does it before the round up, the after analyses the value stored in the trace table to raise the exception.

SQL> insert into testx values ( 1 ) ;

1 row created.

SQL>  insert into testx values ( 5 ) ;

1 row created.

SQL>  insert into testx values ( 2.1 ) ;
 insert into testx values ( 2.1 )
             *
ERROR at line 1:
ORA-20001: Number greater than 5 or it contains decimals
ORA-06512: at "CPL_REP.TRG_TESTX2", line 10
ORA-04088: error during execution of trigger 'CPL_REP.TRG_TESTX2'

SQL> insert into testx values ( 6 ) ;
insert into testx values ( 6 )
            *
ERROR at line 1:
ORA-20001: Number greater than 5 or it contains decimals
ORA-06512: at "CPL_REP.TRG_TESTX2", line 10
ORA-04088: error during execution of trigger 'CPL_REP.TRG_TESTX2'

Summary: As @Gordon Linoff said there was no easy way to achieve what was asked. I believe the method is very complicated for the requirement. I just came up with it for the purpose to show that it was possible after all.