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

i new in oracle sql , pl sql.i have write pl sql function takes data php in xml format.here pl sql code below , works fine:

function save_overtime(overtime_data nvarchar2 ) return clob ret clob;    xmldata xmltype;     v_code  number;    v_errm  varchar2(100);     begin     xmldata:=xmltype(overtime_data);    insert tbl_overtime select x.* 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 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, noticed oracle table accepting data bigger initial size! example, following columns accepting data bigger size 10.

 empid   nvarchar2(10)  

if data 12 in length takes 1st 10 chars.but me,i think should invalid/error.because, otherwise defining column size meaningless.i know adding constraints.but adding many constraints such small things boring , tedious.if did wrong/mistake,then please me understand that.thanks

the insert not allowing , silently truncating values longer column size. xmltable columns clause doing truncation; data type says returned xmltable projection , doesn't mean value @ specified path validated against data type size limit. adding constraint table make no difference.

you can see truncation if query xmltable expression, rather 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 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 xml element truncated fit columns clause. notice number of hours rounded fit constrained number type - 1234.5 rounded 1235. (as long value can converted specified precision won't complain. means long don't have more 4 digits before decimal point rounded; if have 5 or more digits before decimal point ora-01438.)

if try insert values doesn't complain, valid table definition:

insert tbl_overtime (ot_empid, ot_date, ot_hour) select x.ot_empid, x.ot_date, x.ot_hour 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 aren't going verify or manipulate value before inserted, , want error if xml node values more 10 characters, making xmltable column data type 1 character larger work. (if going validate value inj procedure make larger; more 10 here though).

   insert tbl_overtime select x.* 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 value path more 10 characters, x.ot_empid value truncated 11 characters, , insert fail because large real table column.

to demonstrate same data before;

select x.ot_empid, x.ot_date, x.ot_hour 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 ot_empid column has 11 characters. , insert fails:

insert tbl_overtime (ot_empid, ot_date, ot_hour) select x.ot_empid, x.ot_date, x.ot_hour 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 large column "schema"."tbl_overtime"."ot_empid" (actual: 11, maximum: 10)