Concatenate several VARCHAR2 + 4000 characters

Navigation
0

I request your collaboration to indicate how I can concatenate more than one VARCHAR2 in a SELECT , this VARCHAR2 has a more than 4000 characters therefore this error comes out: 

[Error] Execution (31: 64): ORA-01489: el resultado de la concatenación de cadena de caracteres es demasiado largo

Form 1 

SELECT TO_CLOB(CONCAT(CONCAT(CONCAT('Ref: ', PRODUCTO), ' '), DETALLE))
  FROM DUAL

Form 2 

SELECT TO_CLOB('Ref: '||PRODUCTO|| ' '|| DETALLE))
  FROM DUAL

Form 3 

SELECT 'Ref: '||PRODUCTO|| ' '||DETALLE ||)
  FROM DUAL
    
asked by Gdaimon 26.09.2018 в 16:49
source

2 answers

1
  • The maximum size of a varchar2 is 4000 bytes
  • The result of concatenating several varchar2 will remain a varchar2 , there is no automatic promotion to a larger data

The solution is to "promote" any of the chains to concatenate to CLOB , for example: 

SELECT TO_CLOB('Ref: ') || PRODUCTO || ' '|| DETALLE
  FROM DUAL

In your tests, although you use TO_CLOB() you always do it after the engine evaluates the concatenation of varchar2 , hence you always have an error.

    
answered by 27.09.2018 / 16:34
source
0

SELECT coalesce (a, '') || coalesce (b, '') FROM foo; the coalesce is your best option since if it is null or something you will not have a problem in concatenating and etc. I use that. I leave that example apply to yours

    
answered by 26.09.2018 в 16:57
Move elements clicked from one array to another Laravel 5.5 how to print an object in the view?