I use XMLTable on 11.2.0.4 and 12.1.0.2 DB. Here is a simplified scenario. The two functions of PCK_TEST are same, except the default value of the param (p1 := null). I call both functions with explicit value of p1 (20), so default should not be used. But get_xquery_string1 is failed (ORA-06502) and get_xquery_string2 is succeeded, if the xquery string length is greather than 1000. Do you have idea, what causes this differences?
Thanks for answers: lados.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
SQL> create or replace package PCK_TEST is
2 function get_xquery_string1(p1 integer := null) return varchar2;
3 function get_xquery_string2(p1 integer) return varchar2;
4 end;
5 /
Package created.
SQL>
SQL> create or replace package body PCK_TEST is
2 --get_xquery_string1
3 function get_xquery_string1(p1 integer := null) return varchar2 is
4 begin
5 return rpad(' ',1000,' ')||'<x>{/*}</x>';
6 end;
7 --get_xquery_string2
8 function get_xquery_string2(p1 integer ) return varchar2 is
9 begin
10 return rpad(' ',1000,' ')||'<x>{/*}</x>';
11 end;
12 end;
13 /
Package body created.
SQL>
SQL> prompt test get_xquery_string1
test get_xquery_string1
SQL>
SQL> select x from XMLTable(
2 PCK_TEST.get_xquery_string1(20)
3 PASSING XMLTYPE('<y>1</y>')
4 COLUMNS x varchar2(4000) PATH '.'
5 )
6 ;
PCK_TEST.get_xquery_string1(20)
*
ERROR at line 2:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
SQL>
SQL> prompt test get_xquery_string2
test get_xquery_string2
SQL>
SQL> select x from XMLTable(
2 PCK_TEST.get_xquery_string2(20)
3 PASSING XMLTYPE('<y>1</y>')
4 COLUMNS x varchar2(4000) PATH '.'
5 )
6 ;
X
--------------------------------------------------------------------------------
1
SQL>
SQL> prompt test direct xquery string
test direct xquery string
SQL>
SQL> select x from XMLTable(
2 rpad(' ',1000,' ')||'<x>{/*}</x>'
3 PASSING XMLTYPE('<y>1</y>')
4 COLUMNS x varchar2(4000) PATH '.'
5 )
6 ;
X
--------------------------------------------------------------------------------
1
SQL>
SQL> drop package PCK_TEST;
Package dropped.
SQL>