CODEWITHSUNDEEP
sqloracle-database
Javi Torre
Javi Torre

Reputation: 824

Oracle xmlagg character string buffer too small

Because listagg was not enough to handle more than 3k characters, I had to try xmlagg. However, it's giving me the below error when trying to execute the below code. What am I doing wrong?

Error report -
ORA-19011: Character string buffer too small
ORA-06512: at line 24
19011. 00000 -  "Character string buffer too small" 
*Cause:    The string result asked for is too big to return back
*Action:   Get the result as a lob instead

DECLARE
    cols CLOB;--VARCHAR2(30000);
BEGIN
    SELECT
        rtrim(XMLAGG(XMLELEMENT(
            e, agr_name || ', '
        )
            ORDER BY
                agr_name
        ).extract('//text()'), ', ') agr_name
    INTO cols
    FROM
        dat_skills;

    EXECUTE IMMEDIATE q'[
        CREATE OR REPLACE VIEW vw_dat_skills AS
        SELECT
            *
        FROM
            dat_skills PIVOT (
                COUNT ( agr_name )
                FOR agr_name
                IN (]'
                      || cols
                      || q'[)
            )
        ]';
END;

EDIT: This is working now as per @mathguy answer.

SELECT
    XMLAGG(xmlelement(e, agr_name, ', ').extract('//text()')
        ORDER BY
            agr_name
    ).getclobval() agr_name
FROM
    (
        SELECT DISTINCT
            agr_name
        FROM
            dat_skills
    );

Upvotes: 0

Views: 1033

Answers (1)

user5683823
user5683823

Reputation:

There are a few mistakes in your code.

First, you are not calling the getclobval() method on the result of extract(), so what you are going to get is a varchar2 - or an error if the resulting aggregation is too long. It is too long, obviously (which is why you were using xmlagg in the first place) so you get that error. Fix it by adding .getclobval() after the call to extract(...).

Then, you are creating a list of strings (I assume agr_name is a column of type varchar2 or similar) to use in the in list of pivot. For that to work, the "agr names" must be enclosed in single-quotes in the in list; your code doesn't do that. That's likely to be the second error.

Then, are the values in the agr_name column unique? If they are not, you will get a complaint about "column ambiguously defined" after PIVOT is applied. You would be trying to create several copies of the same column in the view - obviously incorrect (and not what you want).

Finally, are there other columns in your base table, which should not participate in PIVOT?

Forget the whole PL/SQL code; start by writing a simple SELECT query that does exactly what you want. You may find out that a simple select * from ... pivot (...) is not enough. After you make that work, write a view based on that, and make sure it works as needed. Then look at the view and try to generate exactly the same string in PL/SQL. Only then write the code to use execute immediate. Attack the issues one at a time, otherwise you will spend two weeks trying to debug this and you may still not get it 100% right.

Upvotes: 3

Related Questions