Mixing
ORA-22903: MULTISET expression not allowed
I have a table which have a field as nested table. I have a trigger on the first table, but it does not work and results on a :ORA-22903: MULTISET expression not allowed
CREATE OR REPLACE TYPE DIA_T as object
(dia varchar2(9),
hora varchar2(6));
CREATE OR REPLACE TYPE DIA_TAB IS TABLE OF DIA_T;
CREATE TABLE T_TALLER(
PK NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
...
DIAS DIA_TAB,
FEC_INI DATE NOT NULL,
FEC_FIN DATE NOT NULL,
...
CONSTRAINT CST_PRIMKEY_TALLER PRIMARY KEY (PK),
...
) NESTED TABLE DIAS STORE AS DIAS_TAB;
My trigger is:
CREATE OR REPLACE TRIGGER TRG_TALLER_AU_FEC_FIN AFTER UPDATE OF FEC_FIN ON T_TALLER FOR EACH ROW
BEGIN
FOR REC IN (SELECT T.DIA , T.HORA
FROM TABLE(:NEW.DIAS) T) LOOP
dbms_output.put_line(REC.DIA||' '||REC.HORA);
END LOOP;
END;
When I try something like that:
update t_taller set fec_fin = fec_fin + 20 where pk = 10;
I get the following error:
ORA-22903: MULTISET expression not allowed
ORA-06512: at "ESTAMPAS.TRG_TALLER_AU_FEC_FIN", line 3
ORA-04088: error during execution of trigger 'ESTAMPAS.TRG_TALLER_AU_FEC_FIN'
Can you help me to solve this problem?
Thanks in advance.
Regards,
UPDATE
The trigger I posted is a dummy, but the error i get for the real one is the same, my real trigger is this
CREATE OR REPLACE TRIGGER TRG_TALLER_AU_FEC_FIN AFTER UPDATE OF FEC_FIN ON T_TALLER FOR EACH ROW
BEGIN
IF :NEW.FEC_FIN >= :OLD.FEC_FIN THEN
Pkg_Utilidades.p_ins_taller_clase_grupo(:NEW.PK,(:OLD.FEC_FIN) + 1,:NEW.FEC_FIN,:NEW.DIAS,:NEW.AU_USU_INS);
ELSE
DELETE T_TALLER_CLASE
WHERE FK_TALLER = :NEW.PK
AND FEC_CLASE BETWEEN :NEW.FEC_FIN + 1 AND :OLD.FEC_FIN;
END IF;
END;
Something else to say, I have a "AFTER INSERT" Trigger, and it Works fine:
CREATE OR REPLACE TRIGGER TRG_TALLER_AI_CLASE AFTER INSERT ON T_TALLER FOR EACH ROW
BEGIN
DELETE T_TALLER_CLASE WHERE FK_TALLER = :NEW.PK;
Pkg_Utilidades.p_ins_taller_clase_grupo(:NEW.PK,:NEW.FEC_INI,:NEW.FEC_FIN,:NEW.DIAS,:NEW.AU_USU_INS);
END;
The procedure is:
PROCEDURE p_ins_taller_clase_grupo (p_taller NUMBER,
p_fec_ini DATE,
p_fec_fin DATE,
p_dias DIA_TAB,
p_user VARCHAR2) IS
p_output VARCHAR2(100);
v_dia NUMBER;
BEGIN
FOR REC IN (SELECT p_fec_ini + LEVEL - 1 FECHA,
DECODE(TO_CHAR(p_fec_ini + LEVEL - 1 , 'DAY'),'MONDAY ',1,'TUESDAY ',2,'WEDNESDAY',3,'THURSDAY ',4,'FRIDAY ',5,'SATURDAY ',6,7) DIA
FROM DUAL
CONNECT BY LEVEL <= p_fec_fin - p_fec_ini + 1) LOOP
BEGIN
SELECT D INTO v_dia
FROM (
SELECT decode(upper(T.dia),'LUNES',1,'MARTES',2,'MIERCOLES',3,'MIÉRCOLES',3,'JUEVES',4,'VIERNES',5,'SABADO',6,'SÁBADO',6,7) D
FROM TABLE(p_dias) T
)
WHERE D = REC.DIA;
P_INS_TALLER_CLASE (p_taller,REC.FECHA,Pkg_conf.CST_HORA,p_user,p_output);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
END p_ins_taller_clase_grupo;
The insert Works fine:
SQL> insert into t_taller (FK_profesor,fk_danza,fk_local,fk_periodicidad,fec_ini,fec_fin,dias,AU_USU_INS) values (1,1,1,1,to_date('05/01/2019','dd/mm/yyyy'),to_date('27/01/2019','dd/mm/yyyy'),dia_tab(dia_t('SABADO','10:30'),dia_t('DOMINGO','10:30')),'EP_PL01');
1 row created.
SQL> commit;
Commit complete.
SQL> update t_taller set fec_fin = fec_fin + 20 where pk = 24;
update t_taller set fec_fin = fec_fin + 20 where pk = 24
*
ERROR at line 1:
ORA-22903: MULTISET expression not allowed
ORA-06512: at "ESTAMPAS.PKG_UTILIDADES", line 451
ORA-06512: at "ESTAMPAS.TRG_TALLER_AU_FEC_FIN", line 5
ORA-04088: error during execution of trigger 'ESTAMPAS.TRG_TALLER_AU_FEC_FIN'
The line 451 of the package, is inside the procedure, exactly here:
SELECT D INTO v_dia
FROM (
SELECT decode(upper(T.dia),'LUNES',1,'MARTES',2,'MIERCOLES',3,'MIÉRCOLES',3,'JUEVES',4,'VIERNES',5,'SABADO',6,'SÁBADO',6,7) D
FROM TABLE(p_dias) T
)
WHERE D = REC.DIA;
Sorry for not posted all the details from the begining, i wanted to summayrize and show just the error.
Regards
oracle plsql
asked Jan 2 at 2:30
LevLev
79111
add a comment |
I have a table which have a field as nested table. I have a trigger on the first table, but it does not work and results on a :ORA-22903: MULTISET expression not allowed
CREATE OR REPLACE TYPE DIA_T as object
(dia varchar2(9),
hora varchar2(6));
CREATE OR REPLACE TYPE DIA_TAB IS TABLE OF DIA_T;
CREATE TABLE T_TALLER(
PK NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
...
DIAS DIA_TAB,
FEC_INI DATE NOT NULL,
FEC_FIN DATE NOT NULL,
...
CONSTRAINT CST_PRIMKEY_TALLER PRIMARY KEY (PK),
...
) NESTED TABLE DIAS STORE AS DIAS_TAB;
My trigger is:
CREATE OR REPLACE TRIGGER TRG_TALLER_AU_FEC_FIN AFTER UPDATE OF FEC_FIN ON T_TALLER FOR EACH ROW
BEGIN
FOR REC IN (SELECT T.DIA , T.HORA
FROM TABLE(:NEW.DIAS) T) LOOP
dbms_output.put_line(REC.DIA||' '||REC.HORA);
END LOOP;
END;
When I try something like that:
update t_taller set fec_fin = fec_fin + 20 where pk = 10;
I get the following error:
ORA-22903: MULTISET expression not allowed
ORA-06512: at "ESTAMPAS.TRG_TALLER_AU_FEC_FIN", line 3
ORA-04088: error during execution of trigger 'ESTAMPAS.TRG_TALLER_AU_FEC_FIN'
Can you help me to solve this problem?
Thanks in advance.
Regards,
UPDATE
The trigger I posted is a dummy, but the error i get for the real one is the same, my real trigger is this
CREATE OR REPLACE TRIGGER TRG_TALLER_AU_FEC_FIN AFTER UPDATE OF FEC_FIN ON T_TALLER FOR EACH ROW
BEGIN
IF :NEW.FEC_FIN >= :OLD.FEC_FIN THEN
Pkg_Utilidades.p_ins_taller_clase_grupo(:NEW.PK,(:OLD.FEC_FIN) + 1,:NEW.FEC_FIN,:NEW.DIAS,:NEW.AU_USU_INS);
ELSE
DELETE T_TALLER_CLASE
WHERE FK_TALLER = :NEW.PK
AND FEC_CLASE BETWEEN :NEW.FEC_FIN + 1 AND :OLD.FEC_FIN;
END IF;
END;
Something else to say, I have a "AFTER INSERT" Trigger, and it Works fine:
CREATE OR REPLACE TRIGGER TRG_TALLER_AI_CLASE AFTER INSERT ON T_TALLER FOR EACH ROW
BEGIN
DELETE T_TALLER_CLASE WHERE FK_TALLER = :NEW.PK;
Pkg_Utilidades.p_ins_taller_clase_grupo(:NEW.PK,:NEW.FEC_INI,:NEW.FEC_FIN,:NEW.DIAS,:NEW.AU_USU_INS);
END;
The procedure is:
PROCEDURE p_ins_taller_clase_grupo (p_taller NUMBER,
p_fec_ini DATE,
p_fec_fin DATE,
p_dias DIA_TAB,
p_user VARCHAR2) IS
p_output VARCHAR2(100);
v_dia NUMBER;
BEGIN
FOR REC IN (SELECT p_fec_ini + LEVEL - 1 FECHA,
DECODE(TO_CHAR(p_fec_ini + LEVEL - 1 , 'DAY'),'MONDAY ',1,'TUESDAY ',2,'WEDNESDAY',3,'THURSDAY ',4,'FRIDAY ',5,'SATURDAY ',6,7) DIA
FROM DUAL
CONNECT BY LEVEL <= p_fec_fin - p_fec_ini + 1) LOOP
BEGIN
SELECT D INTO v_dia
FROM (
SELECT decode(upper(T.dia),'LUNES',1,'MARTES',2,'MIERCOLES',3,'MIÉRCOLES',3,'JUEVES',4,'VIERNES',5,'SABADO',6,'SÁBADO',6,7) D
FROM TABLE(p_dias) T
)
WHERE D = REC.DIA;
P_INS_TALLER_CLASE (p_taller,REC.FECHA,Pkg_conf.CST_HORA,p_user,p_output);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
END p_ins_taller_clase_grupo;
The insert Works fine:
SQL> insert into t_taller (FK_profesor,fk_danza,fk_local,fk_periodicidad,fec_ini,fec_fin,dias,AU_USU_INS) values (1,1,1,1,to_date('05/01/2019','dd/mm/yyyy'),to_date('27/01/2019','dd/mm/yyyy'),dia_tab(dia_t('SABADO','10:30'),dia_t('DOMINGO','10:30')),'EP_PL01');
1 row created.
SQL> commit;
Commit complete.
SQL> update t_taller set fec_fin = fec_fin + 20 where pk = 24;
update t_taller set fec_fin = fec_fin + 20 where pk = 24
*
ERROR at line 1:
ORA-22903: MULTISET expression not allowed
ORA-06512: at "ESTAMPAS.PKG_UTILIDADES", line 451
ORA-06512: at "ESTAMPAS.TRG_TALLER_AU_FEC_FIN", line 5
ORA-04088: error during execution of trigger 'ESTAMPAS.TRG_TALLER_AU_FEC_FIN'
The line 451 of the package, is inside the procedure, exactly here:
SELECT D INTO v_dia
FROM (
SELECT decode(upper(T.dia),'LUNES',1,'MARTES',2,'MIERCOLES',3,'MIÉRCOLES',3,'JUEVES',4,'VIERNES',5,'SABADO',6,'SÁBADO',6,7) D
FROM TABLE(p_dias) T
)
WHERE D = REC.DIA;
Sorry for not posted all the details from the begining, i wanted to summayrize and show just the error.
Regards
oracle plsql
asked Jan 2 at 2:30
LevLev
79111
Why are you trying to dodbms_outputin a trigger ? Triggers are used for any kind of events handling on the table.
– XING
Jan 2 at 4:43
add a comment |
I have a table which have a field as nested table. I have a trigger on the first table, but it does not work and results on a :ORA-22903: MULTISET expression not allowed
CREATE OR REPLACE TYPE DIA_T as object
(dia varchar2(9),
hora varchar2(6));
CREATE OR REPLACE TYPE DIA_TAB IS TABLE OF DIA_T;
CREATE TABLE T_TALLER(
PK NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
...
DIAS DIA_TAB,
FEC_INI DATE NOT NULL,
FEC_FIN DATE NOT NULL,
...
CONSTRAINT CST_PRIMKEY_TALLER PRIMARY KEY (PK),
...
) NESTED TABLE DIAS STORE AS DIAS_TAB;
My trigger is:
CREATE OR REPLACE TRIGGER TRG_TALLER_AU_FEC_FIN AFTER UPDATE OF FEC_FIN ON T_TALLER FOR EACH ROW
BEGIN
FOR REC IN (SELECT T.DIA , T.HORA
FROM TABLE(:NEW.DIAS) T) LOOP
dbms_output.put_line(REC.DIA||' '||REC.HORA);
END LOOP;
END;
When I try something like that:
update t_taller set fec_fin = fec_fin + 20 where pk = 10;
I get the following error:
ORA-22903: MULTISET expression not allowed
ORA-06512: at "ESTAMPAS.TRG_TALLER_AU_FEC_FIN", line 3
ORA-04088: error during execution of trigger 'ESTAMPAS.TRG_TALLER_AU_FEC_FIN'
Can you help me to solve this problem?
Thanks in advance.
Regards,
UPDATE
The trigger I posted is a dummy, but the error i get for the real one is the same, my real trigger is this
CREATE OR REPLACE TRIGGER TRG_TALLER_AU_FEC_FIN AFTER UPDATE OF FEC_FIN ON T_TALLER FOR EACH ROW
BEGIN
IF :NEW.FEC_FIN >= :OLD.FEC_FIN THEN
Pkg_Utilidades.p_ins_taller_clase_grupo(:NEW.PK,(:OLD.FEC_FIN) + 1,:NEW.FEC_FIN,:NEW.DIAS,:NEW.AU_USU_INS);
ELSE
DELETE T_TALLER_CLASE
WHERE FK_TALLER = :NEW.PK
AND FEC_CLASE BETWEEN :NEW.FEC_FIN + 1 AND :OLD.FEC_FIN;
END IF;
END;
Something else to say, I have a "AFTER INSERT" Trigger, and it Works fine:
CREATE OR REPLACE TRIGGER TRG_TALLER_AI_CLASE AFTER INSERT ON T_TALLER FOR EACH ROW
BEGIN
DELETE T_TALLER_CLASE WHERE FK_TALLER = :NEW.PK;
Pkg_Utilidades.p_ins_taller_clase_grupo(:NEW.PK,:NEW.FEC_INI,:NEW.FEC_FIN,:NEW.DIAS,:NEW.AU_USU_INS);
END;
The procedure is:
PROCEDURE p_ins_taller_clase_grupo (p_taller NUMBER,
p_fec_ini DATE,
p_fec_fin DATE,
p_dias DIA_TAB,
p_user VARCHAR2) IS
p_output VARCHAR2(100);
v_dia NUMBER;
BEGIN
FOR REC IN (SELECT p_fec_ini + LEVEL - 1 FECHA,
DECODE(TO_CHAR(p_fec_ini + LEVEL - 1 , 'DAY'),'MONDAY ',1,'TUESDAY ',2,'WEDNESDAY',3,'THURSDAY ',4,'FRIDAY ',5,'SATURDAY ',6,7) DIA
FROM DUAL
CONNECT BY LEVEL <= p_fec_fin - p_fec_ini + 1) LOOP
BEGIN
SELECT D INTO v_dia
FROM (
SELECT decode(upper(T.dia),'LUNES',1,'MARTES',2,'MIERCOLES',3,'MIÉRCOLES',3,'JUEVES',4,'VIERNES',5,'SABADO',6,'SÁBADO',6,7) D
FROM TABLE(p_dias) T
)
WHERE D = REC.DIA;
P_INS_TALLER_CLASE (p_taller,REC.FECHA,Pkg_conf.CST_HORA,p_user,p_output);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
END p_ins_taller_clase_grupo;
The insert Works fine:
SQL> insert into t_taller (FK_profesor,fk_danza,fk_local,fk_periodicidad,fec_ini,fec_fin,dias,AU_USU_INS) values (1,1,1,1,to_date('05/01/2019','dd/mm/yyyy'),to_date('27/01/2019','dd/mm/yyyy'),dia_tab(dia_t('SABADO','10:30'),dia_t('DOMINGO','10:30')),'EP_PL01');
1 row created.
SQL> commit;
Commit complete.
SQL> update t_taller set fec_fin = fec_fin + 20 where pk = 24;
update t_taller set fec_fin = fec_fin + 20 where pk = 24
*
ERROR at line 1:
ORA-22903: MULTISET expression not allowed
ORA-06512: at "ESTAMPAS.PKG_UTILIDADES", line 451
ORA-06512: at "ESTAMPAS.TRG_TALLER_AU_FEC_FIN", line 5
ORA-04088: error during execution of trigger 'ESTAMPAS.TRG_TALLER_AU_FEC_FIN'
The line 451 of the package, is inside the procedure, exactly here:
SELECT D INTO v_dia
FROM (
SELECT decode(upper(T.dia),'LUNES',1,'MARTES',2,'MIERCOLES',3,'MIÉRCOLES',3,'JUEVES',4,'VIERNES',5,'SABADO',6,'SÁBADO',6,7) D
FROM TABLE(p_dias) T
)
WHERE D = REC.DIA;
Sorry for not posted all the details from the begining, i wanted to summayrize and show just the error.
Regards
oracle plsql
asked Jan 2 at 2:30
LevLev
79111
I have a table which have a field as nested table. I have a trigger on the first table, but it does not work and results on a :ORA-22903: MULTISET expression not allowed
CREATE OR REPLACE TYPE DIA_T as object
(dia varchar2(9),
hora varchar2(6));
CREATE OR REPLACE TYPE DIA_TAB IS TABLE OF DIA_T;
CREATE TABLE T_TALLER(
PK NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
...
DIAS DIA_TAB,
FEC_INI DATE NOT NULL,
FEC_FIN DATE NOT NULL,
...
CONSTRAINT CST_PRIMKEY_TALLER PRIMARY KEY (PK),
...
) NESTED TABLE DIAS STORE AS DIAS_TAB;
My trigger is:
CREATE OR REPLACE TRIGGER TRG_TALLER_AU_FEC_FIN AFTER UPDATE OF FEC_FIN ON T_TALLER FOR EACH ROW
BEGIN
FOR REC IN (SELECT T.DIA , T.HORA
FROM TABLE(:NEW.DIAS) T) LOOP
dbms_output.put_line(REC.DIA||' '||REC.HORA);
END LOOP;
END;
When I try something like that:
update t_taller set fec_fin = fec_fin + 20 where pk = 10;
I get the following error:
ORA-22903: MULTISET expression not allowed
ORA-06512: at "ESTAMPAS.TRG_TALLER_AU_FEC_FIN", line 3
ORA-04088: error during execution of trigger 'ESTAMPAS.TRG_TALLER_AU_FEC_FIN'
Can you help me to solve this problem?
Thanks in advance.
Regards,
UPDATE
The trigger I posted is a dummy, but the error i get for the real one is the same, my real trigger is this
CREATE OR REPLACE TRIGGER TRG_TALLER_AU_FEC_FIN AFTER UPDATE OF FEC_FIN ON T_TALLER FOR EACH ROW
BEGIN
IF :NEW.FEC_FIN >= :OLD.FEC_FIN THEN
Pkg_Utilidades.p_ins_taller_clase_grupo(:NEW.PK,(:OLD.FEC_FIN) + 1,:NEW.FEC_FIN,:NEW.DIAS,:NEW.AU_USU_INS);
ELSE
DELETE T_TALLER_CLASE
WHERE FK_TALLER = :NEW.PK
AND FEC_CLASE BETWEEN :NEW.FEC_FIN + 1 AND :OLD.FEC_FIN;
END IF;
END;
Something else to say, I have a "AFTER INSERT" Trigger, and it Works fine:
CREATE OR REPLACE TRIGGER TRG_TALLER_AI_CLASE AFTER INSERT ON T_TALLER FOR EACH ROW
BEGIN
DELETE T_TALLER_CLASE WHERE FK_TALLER = :NEW.PK;
Pkg_Utilidades.p_ins_taller_clase_grupo(:NEW.PK,:NEW.FEC_INI,:NEW.FEC_FIN,:NEW.DIAS,:NEW.AU_USU_INS);
END;
The procedure is:
PROCEDURE p_ins_taller_clase_grupo (p_taller NUMBER,
p_fec_ini DATE,
p_fec_fin DATE,
p_dias DIA_TAB,
p_user VARCHAR2) IS
p_output VARCHAR2(100);
v_dia NUMBER;
BEGIN
FOR REC IN (SELECT p_fec_ini + LEVEL - 1 FECHA,
DECODE(TO_CHAR(p_fec_ini + LEVEL - 1 , 'DAY'),'MONDAY ',1,'TUESDAY ',2,'WEDNESDAY',3,'THURSDAY ',4,'FRIDAY ',5,'SATURDAY ',6,7) DIA
FROM DUAL
CONNECT BY LEVEL <= p_fec_fin - p_fec_ini + 1) LOOP
BEGIN
SELECT D INTO v_dia
FROM (
SELECT decode(upper(T.dia),'LUNES',1,'MARTES',2,'MIERCOLES',3,'MIÉRCOLES',3,'JUEVES',4,'VIERNES',5,'SABADO',6,'SÁBADO',6,7) D
FROM TABLE(p_dias) T
)
WHERE D = REC.DIA;
P_INS_TALLER_CLASE (p_taller,REC.FECHA,Pkg_conf.CST_HORA,p_user,p_output);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
END p_ins_taller_clase_grupo;
The insert Works fine:
SQL> insert into t_taller (FK_profesor,fk_danza,fk_local,fk_periodicidad,fec_ini,fec_fin,dias,AU_USU_INS) values (1,1,1,1,to_date('05/01/2019','dd/mm/yyyy'),to_date('27/01/2019','dd/mm/yyyy'),dia_tab(dia_t('SABADO','10:30'),dia_t('DOMINGO','10:30')),'EP_PL01');
1 row created.
SQL> commit;
Commit complete.
SQL> update t_taller set fec_fin = fec_fin + 20 where pk = 24;
update t_taller set fec_fin = fec_fin + 20 where pk = 24
*
ERROR at line 1:
ORA-22903: MULTISET expression not allowed
ORA-06512: at "ESTAMPAS.PKG_UTILIDADES", line 451
ORA-06512: at "ESTAMPAS.TRG_TALLER_AU_FEC_FIN", line 5
ORA-04088: error during execution of trigger 'ESTAMPAS.TRG_TALLER_AU_FEC_FIN'
The line 451 of the package, is inside the procedure, exactly here:
SELECT D INTO v_dia
FROM (
SELECT decode(upper(T.dia),'LUNES',1,'MARTES',2,'MIERCOLES',3,'MIÉRCOLES',3,'JUEVES',4,'VIERNES',5,'SABADO',6,'SÁBADO',6,7) D
FROM TABLE(p_dias) T
)
WHERE D = REC.DIA;
Sorry for not posted all the details from the begining, i wanted to summayrize and show just the error.
Regards
oracle plsql
oracle plsql
asked Jan 2 at 2:30
LevLev
79111
asked Jan 2 at 2:30
LevLev
79111
edited Jan 2 at 11:31
Lev
asked Jan 2 at 2:30
LevLev
79111
asked Jan 2 at 2:30
LevLev
79111
asked Jan 2 at 2:30
LevLev
79111
79111
Why are you trying to dodbms_outputin a trigger ? Triggers are used for any kind of events handling on the table.
– XING
Jan 2 at 4:43
add a comment |
Why are you trying to dodbms_outputin a trigger ? Triggers are used for any kind of events handling on the table.
– XING
Jan 2 at 4:43
Why are you trying to do
dbms_output in a trigger ? Triggers are used for any kind of events handling on the table.Jan 2 at 4:43
Why are you trying to do
dbms_output in a trigger ? Triggers are used for any kind of events handling on the table.Jan 2 at 4:43
add a comment |
2 Answers
2
active
oldest
votes
TABLE expression works if you are running a select query on the table itself.
Something like
SELECT T.DIA , T.HORA
FROM T_TALLER ,TABLE(:NEW.DIAS) T
But, you are not allowed to select from the Trigger owner as it leads to "table is mutating" error( ORA-04091 ).
You may instead loop through the nested table column using a simple for loop.
CREATE OR REPLACE TRIGGER trg_taller_au_fec_fin AFTER
UPDATE OF fec_fin ON t_taller
FOR EACH ROW
BEGIN
FOR i in 1 .. :new.dias.count
LOOP
dbms_output.put_line(:new.dias(i).dia || ' ' || :new.dias(i).hora);
END LOOP;
END;
/
By the way, as @XING mentioned, there's no use of dbms_output in a Trigger. You should rather consider logging them into a table.
Demo
answered Jan 2 at 5:38
Kaushik NayakKaushik Nayak
20.5k41332
add a comment |
As mentioned in my comments Triggers are used for handling of events on the table to . Although your posted script does not make much sense to me , however am giving the solution.
Also you simply cannot Select records from a Nested table. You need to use table operator to do so. Also in a trigger if you do Select on same table, you might land up is mutating, trigger/function. See below demo:
CREATE OR REPLACE TYPE dia_t AS OBJECT (
dia VARCHAR2(9),
hora VARCHAR2(6)
);
CREATE OR REPLACE TYPE DIA_TAB IS TABLE OF DIA_T;
CREATE TABLE t_taller (
pk NUMBER ,
dias dia_tab,
fec_ini DATE NOT NULL,
fec_fin DATE NOT NULL,
CONSTRAINT cst_primkey_taller PRIMARY KEY ( pk )
)
NESTED TABLE dias STORE AS dias_tab;
INSERT INTO t_taller VALUES (
1,
dia_tab(dia_t(
'A',
'B')
),
TO_DATE('10-Dec-2018','DD-Mon-YYYY'),
TO_DATE('10-Dec-2018','DD-Mon-YYYY')
);
CREATE OR REPLACE TRIGGER trg_taller_au_fec_fin
AFTER UPDATE OF fec_fin ON t_taller
FOR EACH ROW
BEGIN
FOR rec IN ( SELECT t.dia,
t.hora
FROM t_taller e,TABLE (e.dias ) t) --This is how you select records from nested table
LOOP
dbms_output.put_line(rec.dia || ' ' || rec.hora);
END LOOP;
END;
Output:
Error starting at line : 40 in command -
update t_taller set fec_fin = fec_fin + 20 where pk = 1
Error report -
ORA-04091: table SYSTM.T_TALLER is mutating, trigger/function may not see it
ORA-06512: at "SYSTM.TRG_TALLER_AU_FEC_FIN", line 2
ORA-04088: error during execution of trigger 'SYSTM.TRG_TALLER_AU_FEC_FIN'
So again you need to revisit your requirement.
answered Jan 2 at 5:34
XINGXING
7,54931030
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54000576%2fora-22903-multiset-expression-not-allowed%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
TABLE expression works if you are running a select query on the table itself.
Something like
SELECT T.DIA , T.HORA
FROM T_TALLER ,TABLE(:NEW.DIAS) T
But, you are not allowed to select from the Trigger owner as it leads to "table is mutating" error( ORA-04091 ).
You may instead loop through the nested table column using a simple for loop.
CREATE OR REPLACE TRIGGER trg_taller_au_fec_fin AFTER
UPDATE OF fec_fin ON t_taller
FOR EACH ROW
BEGIN
FOR i in 1 .. :new.dias.count
LOOP
dbms_output.put_line(:new.dias(i).dia || ' ' || :new.dias(i).hora);
END LOOP;
END;
/
By the way, as @XING mentioned, there's no use of dbms_output in a Trigger. You should rather consider logging them into a table.
Demo
answered Jan 2 at 5:38
Kaushik NayakKaushik Nayak
20.5k41332
add a comment |
TABLE expression works if you are running a select query on the table itself.
Something like
SELECT T.DIA , T.HORA
FROM T_TALLER ,TABLE(:NEW.DIAS) T
But, you are not allowed to select from the Trigger owner as it leads to "table is mutating" error( ORA-04091 ).
You may instead loop through the nested table column using a simple for loop.
CREATE OR REPLACE TRIGGER trg_taller_au_fec_fin AFTER
UPDATE OF fec_fin ON t_taller
FOR EACH ROW
BEGIN
FOR i in 1 .. :new.dias.count
LOOP
dbms_output.put_line(:new.dias(i).dia || ' ' || :new.dias(i).hora);
END LOOP;
END;
/
By the way, as @XING mentioned, there's no use of dbms_output in a Trigger. You should rather consider logging them into a table.
Demo
answered Jan 2 at 5:38
Kaushik NayakKaushik Nayak
20.5k41332
add a comment |
TABLE expression works if you are running a select query on the table itself.
Something like
SELECT T.DIA , T.HORA
FROM T_TALLER ,TABLE(:NEW.DIAS) T
But, you are not allowed to select from the Trigger owner as it leads to "table is mutating" error( ORA-04091 ).
You may instead loop through the nested table column using a simple for loop.
CREATE OR REPLACE TRIGGER trg_taller_au_fec_fin AFTER
UPDATE OF fec_fin ON t_taller
FOR EACH ROW
BEGIN
FOR i in 1 .. :new.dias.count
LOOP
dbms_output.put_line(:new.dias(i).dia || ' ' || :new.dias(i).hora);
END LOOP;
END;
/
By the way, as @XING mentioned, there's no use of dbms_output in a Trigger. You should rather consider logging them into a table.
Demo
answered Jan 2 at 5:38
Kaushik NayakKaushik Nayak
20.5k41332
TABLE expression works if you are running a select query on the table itself.
Something like
SELECT T.DIA , T.HORA
FROM T_TALLER ,TABLE(:NEW.DIAS) T
But, you are not allowed to select from the Trigger owner as it leads to "table is mutating" error( ORA-04091 ).
You may instead loop through the nested table column using a simple for loop.
CREATE OR REPLACE TRIGGER trg_taller_au_fec_fin AFTER
UPDATE OF fec_fin ON t_taller
FOR EACH ROW
BEGIN
FOR i in 1 .. :new.dias.count
LOOP
dbms_output.put_line(:new.dias(i).dia || ' ' || :new.dias(i).hora);
END LOOP;
END;
/
By the way, as @XING mentioned, there's no use of dbms_output in a Trigger. You should rather consider logging them into a table.
Demo
answered Jan 2 at 5:38
Kaushik NayakKaushik Nayak
20.5k41332
answered Jan 2 at 5:38
Kaushik NayakKaushik Nayak
20.5k41332
answered Jan 2 at 5:38
Kaushik NayakKaushik Nayak
20.5k41332
answered Jan 2 at 5:38
Kaushik NayakKaushik Nayak
20.5k41332
20.5k41332
add a comment |
add a comment |
As mentioned in my comments Triggers are used for handling of events on the table to . Although your posted script does not make much sense to me , however am giving the solution.
Also you simply cannot Select records from a Nested table. You need to use table operator to do so. Also in a trigger if you do Select on same table, you might land up is mutating, trigger/function. See below demo:
CREATE OR REPLACE TYPE dia_t AS OBJECT (
dia VARCHAR2(9),
hora VARCHAR2(6)
);
CREATE OR REPLACE TYPE DIA_TAB IS TABLE OF DIA_T;
CREATE TABLE t_taller (
pk NUMBER ,
dias dia_tab,
fec_ini DATE NOT NULL,
fec_fin DATE NOT NULL,
CONSTRAINT cst_primkey_taller PRIMARY KEY ( pk )
)
NESTED TABLE dias STORE AS dias_tab;
INSERT INTO t_taller VALUES (
1,
dia_tab(dia_t(
'A',
'B')
),
TO_DATE('10-Dec-2018','DD-Mon-YYYY'),
TO_DATE('10-Dec-2018','DD-Mon-YYYY')
);
CREATE OR REPLACE TRIGGER trg_taller_au_fec_fin
AFTER UPDATE OF fec_fin ON t_taller
FOR EACH ROW
BEGIN
FOR rec IN ( SELECT t.dia,
t.hora
FROM t_taller e,TABLE (e.dias ) t) --This is how you select records from nested table
LOOP
dbms_output.put_line(rec.dia || ' ' || rec.hora);
END LOOP;
END;
Output:
Error starting at line : 40 in command -
update t_taller set fec_fin = fec_fin + 20 where pk = 1
Error report -
ORA-04091: table SYSTM.T_TALLER is mutating, trigger/function may not see it
ORA-06512: at "SYSTM.TRG_TALLER_AU_FEC_FIN", line 2
ORA-04088: error during execution of trigger 'SYSTM.TRG_TALLER_AU_FEC_FIN'
So again you need to revisit your requirement.
answered Jan 2 at 5:34
XINGXING
7,54931030
add a comment |
As mentioned in my comments Triggers are used for handling of events on the table to . Although your posted script does not make much sense to me , however am giving the solution.
Also you simply cannot Select records from a Nested table. You need to use table operator to do so. Also in a trigger if you do Select on same table, you might land up is mutating, trigger/function. See below demo:
CREATE OR REPLACE TYPE dia_t AS OBJECT (
dia VARCHAR2(9),
hora VARCHAR2(6)
);
CREATE OR REPLACE TYPE DIA_TAB IS TABLE OF DIA_T;
CREATE TABLE t_taller (
pk NUMBER ,
dias dia_tab,
fec_ini DATE NOT NULL,
fec_fin DATE NOT NULL,
CONSTRAINT cst_primkey_taller PRIMARY KEY ( pk )
)
NESTED TABLE dias STORE AS dias_tab;
INSERT INTO t_taller VALUES (
1,
dia_tab(dia_t(
'A',
'B')
),
TO_DATE('10-Dec-2018','DD-Mon-YYYY'),
TO_DATE('10-Dec-2018','DD-Mon-YYYY')
);
CREATE OR REPLACE TRIGGER trg_taller_au_fec_fin
AFTER UPDATE OF fec_fin ON t_taller
FOR EACH ROW
BEGIN
FOR rec IN ( SELECT t.dia,
t.hora
FROM t_taller e,TABLE (e.dias ) t) --This is how you select records from nested table
LOOP
dbms_output.put_line(rec.dia || ' ' || rec.hora);
END LOOP;
END;
Output:
Error starting at line : 40 in command -
update t_taller set fec_fin = fec_fin + 20 where pk = 1
Error report -
ORA-04091: table SYSTM.T_TALLER is mutating, trigger/function may not see it
ORA-06512: at "SYSTM.TRG_TALLER_AU_FEC_FIN", line 2
ORA-04088: error during execution of trigger 'SYSTM.TRG_TALLER_AU_FEC_FIN'
So again you need to revisit your requirement.
answered Jan 2 at 5:34
XINGXING
7,54931030
add a comment |
As mentioned in my comments Triggers are used for handling of events on the table to . Although your posted script does not make much sense to me , however am giving the solution.
Also you simply cannot Select records from a Nested table. You need to use table operator to do so. Also in a trigger if you do Select on same table, you might land up is mutating, trigger/function. See below demo:
CREATE OR REPLACE TYPE dia_t AS OBJECT (
dia VARCHAR2(9),
hora VARCHAR2(6)
);
CREATE OR REPLACE TYPE DIA_TAB IS TABLE OF DIA_T;
CREATE TABLE t_taller (
pk NUMBER ,
dias dia_tab,
fec_ini DATE NOT NULL,
fec_fin DATE NOT NULL,
CONSTRAINT cst_primkey_taller PRIMARY KEY ( pk )
)
NESTED TABLE dias STORE AS dias_tab;
INSERT INTO t_taller VALUES (
1,
dia_tab(dia_t(
'A',
'B')
),
TO_DATE('10-Dec-2018','DD-Mon-YYYY'),
TO_DATE('10-Dec-2018','DD-Mon-YYYY')
);
CREATE OR REPLACE TRIGGER trg_taller_au_fec_fin
AFTER UPDATE OF fec_fin ON t_taller
FOR EACH ROW
BEGIN
FOR rec IN ( SELECT t.dia,
t.hora
FROM t_taller e,TABLE (e.dias ) t) --This is how you select records from nested table
LOOP
dbms_output.put_line(rec.dia || ' ' || rec.hora);
END LOOP;
END;
Output:
Error starting at line : 40 in command -
update t_taller set fec_fin = fec_fin + 20 where pk = 1
Error report -
ORA-04091: table SYSTM.T_TALLER is mutating, trigger/function may not see it
ORA-06512: at "SYSTM.TRG_TALLER_AU_FEC_FIN", line 2
ORA-04088: error during execution of trigger 'SYSTM.TRG_TALLER_AU_FEC_FIN'
So again you need to revisit your requirement.
answered Jan 2 at 5:34
XINGXING
7,54931030
As mentioned in my comments Triggers are used for handling of events on the table to . Although your posted script does not make much sense to me , however am giving the solution.
Also you simply cannot Select records from a Nested table. You need to use table operator to do so. Also in a trigger if you do Select on same table, you might land up is mutating, trigger/function. See below demo:
CREATE OR REPLACE TYPE dia_t AS OBJECT (
dia VARCHAR2(9),
hora VARCHAR2(6)
);
CREATE OR REPLACE TYPE DIA_TAB IS TABLE OF DIA_T;
CREATE TABLE t_taller (
pk NUMBER ,
dias dia_tab,
fec_ini DATE NOT NULL,
fec_fin DATE NOT NULL,
CONSTRAINT cst_primkey_taller PRIMARY KEY ( pk )
)
NESTED TABLE dias STORE AS dias_tab;
INSERT INTO t_taller VALUES (
1,
dia_tab(dia_t(
'A',
'B')
),
TO_DATE('10-Dec-2018','DD-Mon-YYYY'),
TO_DATE('10-Dec-2018','DD-Mon-YYYY')
);
CREATE OR REPLACE TRIGGER trg_taller_au_fec_fin
AFTER UPDATE OF fec_fin ON t_taller
FOR EACH ROW
BEGIN
FOR rec IN ( SELECT t.dia,
t.hora
FROM t_taller e,TABLE (e.dias ) t) --This is how you select records from nested table
LOOP
dbms_output.put_line(rec.dia || ' ' || rec.hora);
END LOOP;
END;
Output:
Error starting at line : 40 in command -
update t_taller set fec_fin = fec_fin + 20 where pk = 1
Error report -
ORA-04091: table SYSTM.T_TALLER is mutating, trigger/function may not see it
ORA-06512: at "SYSTM.TRG_TALLER_AU_FEC_FIN", line 2
ORA-04088: error during execution of trigger 'SYSTM.TRG_TALLER_AU_FEC_FIN'
So again you need to revisit your requirement.
answered Jan 2 at 5:34
XINGXING
7,54931030
answered Jan 2 at 5:34
XINGXING
7,54931030
answered Jan 2 at 5:34
XINGXING
7,54931030
answered Jan 2 at 5:34
XINGXING
7,54931030
7,54931030
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54000576%2fora-22903-multiset-expression-not-allowed%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Why are you trying to do
dbms_outputin a trigger ? Triggers are used for any kind of events handling on the table.– XING
Jan 2 at 4:43