sql - solucion - STRAGG en Oracle Database 11g Enterprise Edition versión 11.2.0.4.0-Producción de 64 bits

ora 01034 oracle not available solucion

Question

Quiero usar la función STRAGG en este entorno: Oracle Database 11g Enterprise Edition Versión 11.2.0.4.0 - Producción de 64 bits, Versión PL / SQL 11.2.0.4.0 - Producción, Producción CORE 11.2.0.4.0, TNS para Linux: Versión 11.2.0.4.0 - Producción, NLSRTL Versión 11.2.0.4.0 - Producción:

Tengo una vista llamada V_USER_ROLE_DESC

LOGIN   DESC      

joanet  BS in ANDROID.C.3
joanet  DOB in ANDROID.C.3
joanet  DO in ANDROID.C.3
joanet  BS in ANDROID.C.4
joanet  UA in ANDROID.C.4
joanet  OV in ANDROID.C.4
joanet  OI in ANDROID.C.4
joanet  DO in ANDROID.C.4
joanet  DHoU in ANDROID.C.4
joanet  AOP in ANDROID.C.4

Ejecutar esta consulta

select
  login ,
  sys.STRAGG( UNIT_ROLE_DESC || '' - '' ) as string
from
  V_USER_ROLE_DESC
where login = ''joanet''
group by
  login
;

Este es el resultado:

joanet  BS in ANDROID.C.3 - DOB in ANDROID.C.3 - DO in ANDROID.C.3 - BS in ANDROID.C.4 - UA in ANDROID.C.4 - OV in ANDROID.C.4 - OI in ANDROID.C.4 - DO in ANDROID.C.4 - DHoU in ANDROID.C.4 - AOP in ANDROID.C.4 - 

Pero sin la cláusula where ...

   select
      login ,
      sys.STRAGG( UNIT_ROLE_DESC || '' - '' ) as string
    from
      V_USER_ROLE_DESC
    --where login = ''joanet''
    group by
      login
    ;

este es el resultado:

...
colau   DOB in ANDROID.D.2 - 
joanet  DOB in ANDROID.D.2 - 
sisa    DOB in ANDROID.D.2 - 
...

Utilizando

SELECT login ,listagg(unit_role_desc,'' - '') WITHIN GROUP (ORDER BY unit_role_desc) AS STRING FROM v_user_role_desc WHERE 1=1 GROUP BY LOGIN;

Tengo este error

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

Answer 1

Sugiero usar LISTAGG en lugar de STRAGG. LISTAGG es superior porque permite especificar el orden de los elementos agregados.

SELECT login
      ,listagg(unit_role_desc,'' - '')  WITHIN GROUP (ORDER BY unit_role_desc) AS STRING
  FROM v_user_role_desc
 WHERE 1=1
-- AND login = ''joanet''
 GROUP BY LOGIN;

Ver también un ejemplo de trabajo:

WITH
 v_user_role_desc AS (
 SELECT ''sisa'' login
        ,''BS in ANDROID.C.3'' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
 SELECT ''sisa'' login
        ,NULL UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
 SELECT ''joanet'' login
        ,''BS in ANDROID.C.3'' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT ''joanet'' login
        ,''DOB in ANDROID.C.3'' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT ''joanet'' login
        ,''DO in ANDROID.C.3'' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT ''joanet'' login
        ,''BS in ANDROID.C.4'' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT ''joanet'' login
        ,''UA in ANDROID.C.4'' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT ''joanet'' login
        ,''OV in ANDROID.C.4'' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT ''joanet'' login
        ,''OI in ANDROID.C.4'' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT ''joanet'' login
        ,''DO in ANDROID.C.4'' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT ''joanet'' login
        ,''DHoU in ANDROID.C.4'' UNIT_ROLE_DESC 
    FROM dual
  UNION ALL
  SELECT ''joanet'' login
        ,''AOP in ANDROID.C.4'' UNIT_ROLE_DESC 
    FROM dual) 
SELECT login
      ,listagg(unit_role_desc,'' - '')  WITHIN GROUP (ORDER BY unit_role_desc) AS STRING
  FROM v_user_role_desc
 WHERE 1=1
-- AND login = ''joanet''
 GROUP BY LOGIN;