Why am I getting this error: "ORA-22813: operand value exceeds system limits"

Question

I have an oracle 11g, runnint database on Windows Server 2008:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

I have two tables and three views:

CREATE TABLE UTILS.SEG_ROLES ( APLICACION VARCHAR2(12 BYTE), ROL VARCHAR2(12 BYTE), USUARIOS VARCHAR2(255 BYTE) ) SET DEFINE OFF; Insert into SEG_ROLES (APLICACION, ROL, USUARIOS) Values ('MULTIPLAN', 'ADMIN', 'ADMIN'); Insert into SEG_ROLES (APLICACION, ROL, USUARIOS) Values ('MULTIPLAN', 'CAR01', 'PATY '); Insert into SEG_ROLES (APLICACION, ROL, USUARIOS) Values ('MULTIPLAN', 'CAR02', 'FABIOLA, ERIKA'); Insert into SEG_ROLES (APLICACION, ROL, USUARIOS) Values ('MULTIPLAN', 'CON01', 'LUCY, PATY'); Insert into SEG_ROLES (APLICACION, ROL, USUARIOS) Values ('MULTIPLAN', 'CON02', 'VALERIA'); COMMIT; CREATE TABLE UTILS.SEG_ACCESOS ( APLICACION VARCHAR2(12 BYTE), ADMROL VARCHAR2(12 BYTE), MENU VARCHAR2(20 BYTE), OPCION VARCHAR2(20 BYTE), TIPO VARCHAR2(5 BYTE), OBJETO_MENU VARCHAR2(40 BYTE), ACCESO VARCHAR2(2 BYTE), ROLES_ACCESOS VARCHAR2(255 BYTE) ) SET DEFINE OFF; Insert into SEG_ACCESOS (APLICACION, ADMROL, MENU, OPCION, TIPO, OBJETO_MENU, ACCESO, ROLES_ACCESOS) Values ('MULTIPLAN', 'ADMIN', 'COLOCA', NULL, 'M', 'm_coloca', 'S', 'CAR01, CAR02'); Insert into SEG_ACCESOS (APLICACION, ADMROL, MENU, OPCION, TIPO, OBJETO_MENU, ACCESO, ROLES_ACCESOS) Values ('MULTIPLAN', 'ADMIN', 'COLOCA', 'PREVALIDA', 'SM', 'sm_prevalida', 'S', 'CAR01, CAR02'); Insert into SEG_ACCESOS (APLICACION, ADMROL, MENU, OPCION, TIPO, OBJETO_MENU, ACCESO, ROLES_ACCESOS) Values ('MULTIPLAN', 'ADMIN', 'COLOCA', 'PRECOLOCA', 'SM', 'sm_preColocacion', 'S', 'CAR01, CAR02'); Insert into SEG_ACCESOS (APLICACION, ADMROL, MENU, OPCION, TIPO, OBJETO_MENU, ACCESO, ROLES_ACCESOS) Values ('MULTIPLAN', 'ADMIN', 'COLOCA', 'COLOCACION', 'SM', 'sm_colocacion', 'S', 'CAR01'); Insert into SEG_ACCESOS (APLICACION, ADMROL, MENU, OPCION, TIPO, OBJETO_MENU, ACCESO, ROLES_ACCESOS) Values ('MULTIPLAN', 'ADMIN', 'COLOCA', 'REGCOLOCA', 'SM', 'sm_regcoloca', 'S', 'CAR01'); Insert into SEG_ACCESOS (APLICACION, ADMROL, MENU, OPCION, TIPO, OBJETO_MENU, ACCESO, ROLES_ACCESOS) Values ('MULTIPLAN', 'ADMIN', 'PLD', NULL, 'M', 'm_PLD', 'S', 'CAR01, CAR02, CON01, CON02'); Insert into SEG_ACCESOS (APLICACION, ADMROL, MENU, OPCION, TIPO, OBJETO_MENU, ACCESO, ROLES_ACCESOS) Values ('MULTIPLAN', 'ADMIN', 'PLD', 'LN_CONSULTA', 'SM', 'sm_PLD_LNConsulta', 'S', 'CAR01, CAR02'); Insert into SEG_ACCESOS (APLICACION, ADMROL, MENU, OPCION, TIPO, OBJETO_MENU, ACCESO, ROLES_ACCESOS) Values ('MULTIPLAN', 'ADMIN', 'PLD', 'LN_BARRIDOMASIVO', 'SM', 'sm_PLD_LNBarridoMasivo', 'S', 'CAR01'); Insert into SEG_ACCESOS (APLICACION, ADMROL, MENU, OPCION, TIPO, OBJETO_MENU, ACCESO, ROLES_ACCESOS) Values ('MULTIPLAN', 'ADMIN', 'PLD', 'REP_INICICLO', 'SM', 'sm_PLD_ReporIniciclo', 'S', 'CON01, CON02'); Insert into SEG_ACCESOS (APLICACION, ADMROL, MENU, OPCION, TIPO, OBJETO_MENU, ACCESO, ROLES_ACCESOS) Values ('MULTIPLAN', 'ADMIN', 'PLD', 'LN_BITACORA', 'SM', 'sm_PLD_LNBitacora', 'S', 'CON01'); COMMIT; 

--- And did it:

  CREATE VIEW UTILS.VW_ROL_USER AS select distinct APLICACION, ROL, trim(column_value) USUARIO from ( SELECT APLICACION, ROL, USUARIOS USUARIO FROM UTILS.SEG_ROLES WHERE USUARIOS IS NOT NULL ORDER BY APLICACION, ROL ) t, xmltable(('"' || replace(USUARIO, ',', '","') || '"')) order by APLICACION, ROL, trim(column_value) CREATE VIEW UTILS.VW_ACC_ROL AS select distinct APLICACION, ADMROL, MENU, OPCION, TIPO, OBJETO_MENU, ACCESO, trim(column_value) ROL from ( SELECT APLICACION, ADMROL, MENU, OPCION, TIPO, OBJETO_MENU, ACCESO, ROLES_ACCESOS ROL FROM UTILS.SEG_ACCESOS WHERE ROLES_ACCESOS IS NOT NULL ORDER BY APLICACION, ADMROL, MENU, OPCION ) t, xmltable(('"' || replace(ROL, ',', '","') || '"')) order by APLICACION, MENU, TIPO, OPCION, trim(column_value) CREATE VIEW UTILS.VW_SEG_ACCESOS AS SELECT VACR.APLICACION, VACR.MENU, VACR.TIPO, VACR.OPCION, VACR.OBJETO_MENU, VACR.ACCESO, VACR.ROL, VUSR.USUARIO FROM UTILS.VW_ACC_ROL VACR, UTILS.VW_ROL_USER VUSR WHERE VACR.ROL = VUSR.ROL ORDER BY VACR.APLICACION, VACR.ROL, VACR.MENU, VUSR.USUARIO, VACR.TIPO, VACR.OPCION 

When I request the latest view, it works fine:

 SELECT VSEG.* FROM UTILS.VW_SEG_ACCESOS VSEG 

But when I add the conditions:

  SELECT VSEG.* FROM UTILS.VW_SEG_ACCESOS VSEG WHERE VSEG.APLICACION = 'MULTIPLAN' AND VSEG.USUARIO = 'PATY' 

It throws this error:

 **ORA-22813: Operand value exceeds system limits** Cause: Object or Collection value was too large. The size of the value might have exceeded 30k in a SORT context, or the size might be too big for available memory. Action: Choose another value and retry the operation. 

Even just requesting a simple view causes an error:

 SELECT * FROM UTILS.VW_ROL_USER WHERE USUARIO = 'PATY' 

Answer 1

I know that OP found a workaround, but it really works fine in 11.2.0.4.0.

 CREATE TABLE seg_roles ( aplicacion VARCHAR2(12 BYTE), rol VARCHAR2(12 BYTE), usuarios VARCHAR2(255 BYTE) ); INSERT INTO seg_roles (aplicacion, rol, usuarios) VALUES ('MULTIPLAN', 'ADMIN', 'ADMIN'); INSERT INTO seg_roles (aplicacion, rol, usuarios) VALUES ('MULTIPLAN', 'CAR01', 'PATY '); INSERT INTO seg_roles (aplicacion, rol, usuarios) VALUES ('MULTIPLAN', 'CAR02', 'FABIOLA, ERIKA'); INSERT INTO seg_roles (aplicacion, rol, usuarios) VALUES ('MULTIPLAN', 'CON01', 'LUCY, PATY'); INSERT INTO seg_roles (aplicacion, rol, usuarios) VALUES ('MULTIPLAN', 'CON02', 'VALERIA'); COMMIT; CREATE TABLE seg_accesos ( aplicacion VARCHAR2(12 BYTE), admrol VARCHAR2(12 BYTE), menu VARCHAR2(20 BYTE), opcion VARCHAR2(20 BYTE), tipo VARCHAR2(5 BYTE), objeto_menu VARCHAR2(40 BYTE), acceso VARCHAR2(2 BYTE), roles_accesos VARCHAR2(255 BYTE) ); INSERT INTO seg_accesos (aplicacion, admrol, menu, opcion, tipo, objeto_menu, acceso, roles_accesos) VALUES ('MULTIPLAN', 'ADMIN', 'COLOCA', NULL, 'M', 'm_coloca', 'S', 'CAR01, CAR02'); INSERT INTO seg_accesos (aplicacion, admrol, menu, opcion, tipo, objeto_menu, acceso, roles_accesos) VALUES ('MULTIPLAN', 'ADMIN', 'COLOCA', 'PREVALIDA', 'SM', 'sm_prevalida', 'S', 'CAR01, CAR02'); INSERT INTO seg_accesos (aplicacion, admrol, menu, opcion, tipo, objeto_menu, acceso, roles_accesos) VALUES ('MULTIPLAN', 'ADMIN', 'COLOCA', 'PRECOLOCA', 'SM', 'sm_preColocacion', 'S', 'CAR01, CAR02'); INSERT INTO seg_accesos (aplicacion, admrol, menu, opcion, tipo, objeto_menu, acceso, roles_accesos) VALUES ('MULTIPLAN', 'ADMIN', 'COLOCA', 'COLOCACION', 'SM', 'sm_colocacion', 'S', 'CAR01'); INSERT INTO seg_accesos (aplicacion, admrol, menu, opcion, tipo, objeto_menu, acceso, roles_accesos) VALUES ('MULTIPLAN', 'ADMIN', 'COLOCA', 'REGCOLOCA', 'SM', 'sm_regcoloca', 'S', 'CAR01'); INSERT INTO seg_accesos (aplicacion, admrol, menu, opcion, tipo, objeto_menu, acceso, roles_accesos) VALUES ('MULTIPLAN', 'ADMIN', 'PLD', NULL, 'M', 'm_PLD', 'S', 'CAR01, CAR02, CON01, CON02'); INSERT INTO seg_accesos (aplicacion, admrol, menu, opcion, tipo, objeto_menu, acceso, roles_accesos) VALUES ('MULTIPLAN', 'ADMIN', 'PLD', 'LN_CONSULTA', 'SM', 'sm_PLD_LNConsulta', 'S', 'CAR01, CAR02'); INSERT INTO seg_accesos (aplicacion, admrol, menu, opcion, tipo, objeto_menu, acceso, roles_accesos) VALUES ('MULTIPLAN', 'ADMIN', 'PLD', 'LN_BARRIDOMASIVO', 'SM', 'sm_PLD_LNBarridoMasivo', 'S', 'CAR01'); INSERT INTO seg_accesos (aplicacion, admrol, menu, opcion, tipo, objeto_menu, acceso, roles_accesos) VALUES ('MULTIPLAN', 'ADMIN', 'PLD', 'REP_INICICLO', 'SM', 'sm_PLD_ReporIniciclo', 'S', 'CON01, CON02'); INSERT INTO seg_accesos (aplicacion, admrol, menu, opcion, tipo, objeto_menu, acceso, roles_accesos) VALUES ('MULTIPLAN', 'ADMIN', 'PLD', 'LN_BITACORA', 'SM', 'sm_PLD_LNBitacora', 'S', 'CON01'); COMMIT; CREATE OR REPLACE VIEW vw_rol_user AS SELECT DISTINCT aplicacion, rol, TRIM(COLUMN_VALUE) usuario FROM (SELECT aplicacion, rol, usuarios usuario FROM seg_roles WHERE usuarios IS NOT NULL ORDER BY aplicacion, rol ) t, XMLTABLE(('"' || Replace(Usuario, ',', '","') || '"')) ORDER BY aplicacion, rol, TRIM(COLUMN_VALUE); CREATE OR REPLACE VIEW vw_acc_rol AS SELECT DISTINCT aplicacion, admrol, menu, opcion, tipo, objeto_menu, acceso, TRIM(COLUMN_VALUE) rol FROM (SELECT aplicacion, admrol, menu, opcion, tipo, objeto_menu, acceso, roles_accesos rol FROM seg_accesos WHERE roles_accesos IS NOT NULL ORDER BY aplicacion, admrol, menu, opcion ) t, XMLTABLE(('"' || REPLACE(rol, ',', '","') || '"')) ORDER BY aplicacion, menu, tipo, opcion, TRIM(COLUMN_VALUE); CREATE OR REPLACE VIEW vw_seg_accesos AS SELECT vacr.aplicacion, vacr.menu, vacr.tipo, vacr.opcion, vacr.objeto_menu, vacr.acceso, vacr.rol, vusr.usuario FROM vw_acc_rol vacr, vw_rol_user vusr WHERE vacr.rol = vusr.rol ORDER BY vacr.aplicacion, vacr.rol, vacr.menu, vusr.usuario, vacr.tipo, vacr.opcion; 

Performance

 SELECT vseg.* FROM vw_seg_accesos vseg WHERE vseg.aplicacion = 'MULTIPLAN' AND vseg.usuario = 'PATY'; 

Returns 11 rows. I can’t do an SQL Fiddle example because I don’t think XMLDB is installed (hence ORA-20000 error about missing COLUMNS), so I expect the answer to be updated from 11.1