SQL & PL/SQL

Hello,

I am using Oracle Database 11.2.0.

When querying my database with a common table expression which concatenates VARCHAR2 strings, I receive an ORA-01489 error although I'm doubting that I am concatenating more than 4000 characters.

To be more precise, I have a table entity which basically stores XML elements including their parent element and their sibling position among its siblings. The CREATE statement for this table is listed below.

CREATE TABLE Entity (
ID NUMBER(10,0) NOT NULL PRIMARY KEY,
Name VARCHAR2(100) NOT NULL,
Parent NUMBER(10,0) REFERENCES Entity(ID),
Sibling_Pos NUMBER(2,0) DEFAULT 0
)
;

Now, I would like for all elements to be concatenated with their ancestor elements to a structure like this:

"/root_element(sibling_pos)/.../ancestor_element(sibling_pos)/parent_element(sibling_pos)/current_element(sibling_pos)"
(where root_element, ancestor_element, parent_element and current_element are just values from the name column of the entity table)

In order to achieve this, I use a common table expression which concatenates the name and sibling_pos values as shown below:

WITH entity_cte (lvl, id, path) AS (
SELECT 1 AS lvl, id, '/' || name || '(0' || sibling_pos || ')' AS path
FROM entity
WHERE parent IS NULL
UNION ALL (
SELECT lvl + 1 AS lvl, e.id, entity_cte.path || '/' || e.name || '(' || cast(e.sibling_pos AS VARCHAR2(2)) || ')' AS path
FROM entity_cte, entity e
WHERE entity_cte.id = e.parent
)
)
SELECT lvl, id, path
FROM entity_cte e
;

After inserting certain values, I get the ORA-01489 error, that the result of the string concatenation is too long. The maximum is referred to as being 4000 characters for VARCHAR2 in the oracle documentation and in various websites. Of course, it is clear to me by just using the common table expression like that, I could run into such an error. However, due to the structure of my XML documents, I doubted that the resulting strings would be more than 4000 characters long.

So, I rearranged my query in order to count the characters to be concatenated instead of actually concatenating them. The query is stated below as well, the changes are marked bold:

WITH entity_cte (lvl, id, path) AS (
SELECT 1 AS lvl, id, length('/' || name || '(0' || sibling_pos || ')') AS path
FROM entity
WHERE parent IS NULL
UNION ALL (
SELECT lvl + 1 AS lvl, e.id, entity_cte.path + length('/' || e.name || '(' || cast(e.sibling_pos AS VARCHAR2(2)) || ')') AS path
FROM entity_cte, entity e
WHERE entity_cte.id = e.parent
)
)
SELECT lvl, id, path
FROM entity_cte e
ORDER BY path DESC
;

The result of the query gives me a maximum length of 319 characters.

To be sure, I also checked the maximum level depth (indicated by the column named lvl in the common table expression), meaning the maximum number of elements in my path (the concatenated string). The result is 18. As I use VARCHAR2(100) for the name column and add 5 charcaters in each level, the maximum number of characters expected for 18 levels would be 1890.

So, now I wonder is the ORA-01489 maybe raised for another reason? Or is there something else I am missing?
Any help would be appreciated. Further suggestions to track down the error are more than welcome. Thanks in advance.