why this oracle table column accepting data bigger than initial size?

Question

i am new in oracle sql and pl sql.i have write pl sql function that takes data from php in xml format.Here is the pl sql code below and it works fine:

FUNCTION save_overtime(overtime_data NVARCHAR2 ) RETURN clob IS ret clob;
   xmlData XMLType;

   v_code  NUMBER;
   v_errm  VARCHAR2(100);

   BEGIN 
   xmlData:=XMLType(overtime_data);
   INSERT INTO TBL_OVERTIME SELECT x.* FROM XMLTABLE('/overtime'
                                                PASSING xmlData
                                                COLUMNS OT_EMPID   NVARCHAR2(10)     PATH   'employee_id',
                                                        OT_DATE   DATE     PATH    'date',                                                        
                                                        OT_HOUR   NUMBER(4,0)    PATH    'overtime_hour'                                                        
                                                        ) x;


     ret:=to_char(sql%rowcount);
COMMIT;

RETURN '<result><status affectedRow='||ret||'>success</status></result>';
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE (v_code || ' ' || v_errm);
-- '<result><status>Error</status> <error_message>'|| 'Error Code:' || v_code || ' ' || 'Error Message:' || v_errm ||'</error_message> </result>';
RETURN '<result><status>Error</status> <error_message>'|| 'Error Message:' || v_errm ||'</error_message> </result>';

END save_overtime;

While inserting data, I noticed that oracle table is also accepting data which are bigger than its initial size! For example, following columns accepting data which are bigger than size 10.

 EMPID   NVARCHAR2(10) 

if the data is 12 in length then it takes the 1st 10 chars.But to me,i think it should be an invalid/error.Because, otherwise defining column size is meaningless.I know about adding constraints.But adding many constraints for such small things is very boring and tedious.If i did any wrong/mistake,then please help me to understand that.thanks

Answer 1

The insert is not allowing and silently truncating values longer than the column size. The XMLTable columns clause is doing the truncation; the data type for that says what will be returned by XMLTable projection and doesn't mean the value at the specified path will be validated against the data type size limit. Adding a constraint to the table will make no difference.

You can see the truncation if you just query from the XMLTable expression, rather than inserting:

var overtime_data nvarchar2(4000);
exec :overtime_data := '<overtime><employee_id>invalidvalue</employee_id><date>2016-01-15</date><overtime_hour>1234.5</overtime_hour></overtime>';

select x.ot_empid, x.ot_date, x.ot_hour
from xmltable('/overtime'
  passing xmltype(:overtime_data)
  columns ot_empid nvarchar2(10) path 'employee_id',
    ot_date date path 'date',
    ot_hour number(4,0) path 'overtime_hour'
) x;

OT_EMPID   OT_DATE      OT_HOUR
---------- --------- ----------
invalidval 15-JAN-16       1235

The 12-character value from the XML element is truncated to fit the columns clause. Also notice that the number of hours is rounded to fit into the constrained number type - 1234.5 is rounded up to 1235. (As long as the value can be converted to the specified precision it won't complain. Essentially that means that as long as don't have more than four digits before the decimal point it will be rounded; if you have five or more digits before the decimal point it will get an ORA-01438.)

If you try to insert those values it doesn't complain, as they are valid for the table definition:

insert into tbl_overtime (ot_empid, ot_date, ot_hour)
select x.ot_empid, x.ot_date, x.ot_hour
from xmltable('/overtime'
  passing xmltype(:overtime_data)
  columns ot_empid nvarchar2(10) path 'employee_id',
    ot_date date path 'date',
    ot_hour number(4,0) path 'overtime_hour'
) x;

1 row inserted.

If you aren't going to verify or manipulate the value before it inserted, and you do want it to error if the XML node values is more than 10 characters, then making the XMLTable column data type even one character larger will work. (If you were going to validate the value inj the procedure you could make it much larger; anything more than 10 will do here though).

   INSERT INTO TBL_OVERTIME SELECT x.* FROM XMLTABLE('/overtime'
                                                PASSING xmlData
                                                COLUMNS OT_EMPID   NVARCHAR2(11)     PATH   'employee_id',
                                                        OT_DATE   DATE     PATH    'date',        
                                                        OT_HOUR   NUMBER(4,0)    PATH    'overtime_hour'                                                        
                                                        ) x;

The if the value from the path is more than 10 characters, the x.ot_empid value will be truncated to 11 characters, and the insert will then fail because that is too large for the real table column.

To demonstrate with the same data as before;

select x.ot_empid, x.ot_date, x.ot_hour
from xmltable('/overtime'
  passing xmltype(:overtime_data)
  columns ot_empid nvarchar2(11) path 'employee_id',
    ot_date date path 'date',
    ot_hour number(4,0) path 'overtime_hour'
) x;

OT_EMPID    OT_DATE      OT_HOUR
----------- --------- ----------
invalidvalu 15-JAN-16       1235

Note the ot_empid column now has 11 characters. And insert now fails:

insert into tbl_overtime (ot_empid, ot_date, ot_hour)
select x.ot_empid, x.ot_date, x.ot_hour
from xmltable('/overtime'
  passing xmltype(:overtime_data)
  columns ot_empid nvarchar2(11) path 'employee_id',
    ot_date date path 'date',
    ot_hour number(4,0) path 'overtime_hour'                                                        
) x;

Error report -
SQL Error: ORA-12899: value too large for column "SCHEMA"."TBL_OVERTIME"."OT_EMPID" (actual: 11, maximum: 10)