Why I'm getting this error: "ORA-22813: Operand value exceeds system limits"

Question

I have an oracle 11g database, runnint on a 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 made this views:

    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 query the last view, it works fine:

SELECT VSEG.*  FROM UTILS.VW_SEG_ACCESOS VSEG

But when i add 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 querying just a simple view throws the error:

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

enter image description here

There are some Oracle bugs that throw this error. You might have to go digging for a patch. http://www.dba-oracle.com/t_ora_22813_operand_value_exceeds_system_limits.htm — Tripp Kinetics, Jan 12 '15 at 21:50
Do the views even compile for you as shown? In 10gR2 the xmltable() calls get "ORA-19102: XQuery string literal expected", and in 11gR2 they get "ORA-02000: missing COLUMNS keyword". I don't have an 11gR1 instance to try this on. If this is what you're running, do you get a full error stack that referes back to one of those clauses in your views? — Alex Poole, Jan 13 '15 at 09:56
Thanks Alex, the views were compiled ok... how can i get the full error stack? — Edgar Fco Delgado Zamarripa, Jan 13 '15 at 16:42
Hmm, if it doesn't give you the full stack in that plain SQL query, then you could try querying inside a PL/SQL block - with select into or as cursor - and see if that gives you more information; it may not though. You shouldn't really have multiple values in your `ROLES_ACCESOS` column of course, but there are other ways to split that into separate values if you're stuck with it, I suppose. Can you add the output you expect for `PATY`, maybe cut from the unfiltered version that works? — Alex Poole, Jan 13 '15 at 17:42
Thanks Alex, i added a image, your right, i just want a queryable view, as shown in the image. — Edgar Fco Delgado Zamarripa, Jan 13 '15 at 18:31
SOLUTION: Finally found a solution at: http://stackoverflow.com/questions/14328621/oracle-10g-splitting-string-into-multiple-rows, just changed the way to create the view using regexp_substr. Thanks all... — Edgar Fco Delgado Zamarripa, Jan 13 '15 at 21:29

score 1 · Answer 1 · answered May 12 '16 at 09:58

I know that the OP found a workaround, but this actually 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;

Running

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

Returns 11 rows. I can't do a SQL Fiddle example because I don't think that XMLDB is installed (hence a ORA-20000 error about missing COLUMNS) so I expect that the answer would to be upgrade from 11.1

Why I'm getting this error: "ORA-22813: Operand value exceeds system limits"

1 Answers1