Concatenate columns in oracle

Navigation
1

Good morning. I have a problem when concatenating columns in oracle and I do not know what to do. This would be a simplified query of what I do: 

select t1.c1 || '##' || t1.c2  || '##' ||  t2.c3 from t1, t2
order by t1.c1 asc, t1.c2 asc, t2.c3 asc;

In my case the query is much larger, uses many more tables and concatenates many more columns. When concatenated, oracle launches the following error: 

ORA-01489: el resultado de la concatenación de cadena de caracteres es demasiado largo

Is there any way to make that concatenation without limitation of characters? for example, using clob functions ...

I can not change the format because the answer is consumed by an external service and needs to be strings separated by two almoadillas ...

I hope you can help me.

    
asked by Alejandro 12.09.2017 в 10:24
source

1 answer

1

A VARCHAR or VARCHAR2 has a limit of 4000 characters, this we can verify it like this: 

select RPAD('A', 4001, '0') || 'B' FROM dual;

This will give us an error like the one you mention: ORA-01489: result of string concatenation is too long . To avoid this, you have an alternative that is to use the function TO_CLOB 

select to_clob(RPAD('A', 4001, '0')) || 'B' FROM dual

In this case the result will be an 'A' followed by 4000 0 plus the character B , when promoting a column to an object CLOB , the complete concatenation is promoted to CLOB avoiding the error . Anyway, this type of objects also has its limit, which according to the documentation is much broader:

  

Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8   TB to 128 TB)

    
answered by 12.09.2017 / 18:09
source
VPN creates me subnets Increase the session time in shared hosting