The XMLTABLE definition in Oracle 11.1 states that if the datatype is omitted at the columns definition then the datatype defaults to varchar2(4000):
"The datatype is required unless XMLTable is used with XML schema-based storage of XMLType, datatype. In this case, if you omit datatype, Oracle XML DB infers the datatype from the XML schema. If the database is unable to determine the proper type for a node, then a default type of VARCHAR2(4000) is used."
This paragraph does not exist anymore in the 12.1 documentation.
When I run a test on Oracle DB 12.2 the defaulting to varchar2 still seems to work:
declare
l_xml xmltype:= xmltype('
<set>
<a>abcde</a>
</set>
');
cursor c_test is
select col1
from xmltable('/set' passing l_xml
columns col1 varchar2(3) path 'a' -- datatype varchar2(3)
) x;
r_test c_test%rowtype;
cursor c_test2 is
select col1
from xmltable('/set' passing l_xml
columns col1 path 'a' -- no datatype defined
) x;
r_test2 c_test2%rowtype;
begin
open c_test;
fetch c_test into r_test;
dbms_output.put_line('r_test.col1 (with datatype varchar2(3): '||r_test.col1);
close c_test;
--
open c_test2;
fetch c_test2 into r_test2;
dbms_output.put_line('r_test2.col1 (with without datatype: '||r_test2.col1);
close c_test2;
end;
/
r_test.col1 (with datatype varchar2(3): abc
r_test2.col1 (with without datatype: abcde
PL/SQL procedure successfully completed.
In 12.1 and higher can I still rely on the defaulting to varchar2(4000))?
Another behaviour you see here is that when varchar2(3) is used as datatype the provided value, longer then 3 chars, is cut off without a warning. I would expect an error.
My understanding is that we took that out of the docs because it no longer is *fixed* at 4000 due to the implementation of the max_string_size changes in 12c onwards.
For example, on my 18c instance
SQL> create or replace view v as
2 select col1
3 from xmltable('/set' passing xmltype('<set><a>abcde</a></set>')
4 columns col1 path 'a'
5 ) x;
View created.
SQL>
SQL>
SQL> desc v
Name Null? Type
----------------------------- -------- --------------------
COL1 VARCHAR2(32767)
SQL> show parameter max_string
NAME TYPE VALUE
------------------------------------ ----------- -----------
max_string_size string EXTENDED
With regard to the truncation, I think that has always been the case, but I will ask around internally to get a clarification and report back if I get anything useful
Update: XMLTABLE is modelled on the same behaviour as CAST
SQL> select cast('12345' as varchar2(2)) from dual;
CA
--
12