Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Bernard.

Asked: September 27, 2019 - 3:03 pm UTC

Last updated: October 07, 2019 - 12:42 am UTC

Version: 12.2

Viewed 1000+ times

You Asked

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.

and Connor said...

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



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library