What is wrong with query, how do I correct the error?

Question

INSERT ALL INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
     (1001,'Cost Savings',TIMESTAMP'2010-12-14 00:00:00.0','SESH',NULL,NULL),
     (1002,'Business Improvements',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-17 09:03:22.0','TEMP1'),
     (1003,'Health and Wellness',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-21 13:18:23.0','TEMP1'),
     (1004,'Complaints',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 09:34:42.0','SESH'),
     (1005,'Others',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 11:06:43.0','SESH')
    SELECT * FROM dual;

output SQL Error [928] [42000]: ORA-00928: missing SELECT keyword

I have also tried to do it like this:

INSERT ALL 
INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
     (1001,'Cost Savings',TIMESTAMP'2010-12-14 00:00:00.0','SESH',NULL,NULL)
INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
     (1002,'Business Improvements',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-17 09:03:22.0','TEMP1')
INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
     (1003,'Health and Wellness',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-21 13:18:23.0','TEMP1')
INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
     (1004,'Complaints',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 09:34:42.0','SESH')
INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY) VALUES
     (1005,'Others',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 11:06:43.0','SESH')
    SELECT * FROM dual;

but I get this output:

SQL Error [4091] [42000]: ORA-04091: table ITDEV.COM_CATEGORY_TB is mutating, trigger/function may not see it
ORA-06512: at "ITDEV.COM_CATEGORY_TR", line 4
ORA-04088: error during execution of trigger 'ITDEV.COM_CATEGORY_TR'

Need to repeatedly write the related keywords such as `INSERT ALL INTO com_category_tb(...) VALUES (...) INTO com_category_tb(...) VALUES (...) ... SELECT * FROM dual` — Barbaros Özhan, Apr 24 '23 at 13:04
ORA-04091 is a problem with the trigger on the table, not your modified insert. A simple for-each-row trigger can't query the same table it's defined against; it might have worked for a single-row insert but won't for multi-row inserts. You need to revisit your trigger logic and code. — Alex Poole, Apr 24 '23 at 14:53

score 1 · Answer 1 · answered Apr 24 '23 at 13:06

1

You can do it as follows :

INSERT INTO ITDEV.COM_CATEGORY_TB (CAT_CODE,CAT_DESCRIPTION,CREATED_ON,CREATED_BY,UPDATED_ON,UPDATED_BY)
select 1001,'Cost Savings',TIMESTAMP'2010-12-14 00:00:00.0','SESH',NULL,NULL from dual union all
select 1002,'Business Improvements',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-17 09:03:22.0','TEMP1' from dual union all
select 1003,'Health and Wellness',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-21 13:18:23.0','TEMP1' from dual union all
select 1004,'Complaints',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 09:34:42.0','SESH' from dual union all
select 1005,'Others',TIMESTAMP'2010-12-14 00:00:00.0','SESH',TIMESTAMP'2011-01-12 11:06:43.0','SESH' from dual;1-01-21 13:18:23.0','TEMP1' from dual;

answered Apr 24 '23 at 13:06

SelVazi

10,028
2
13
29

Thank you this worked. I first had to go disable the trigger, insert the data and then enable the trigger again. – Misha Rukazo Apr 25 '23 at 14:25
However, it is quit a lot of work having to repeatedly write the related keywords such as insert in to and dual in every row of my data, I am dealing with very big data sets, is there an easier way ? – Misha Rukazo Apr 25 '23 at 14:28
If you have a very big dataset, then your best choice is by load your data from an external file using Oracle SQL*Loader https://www.oracletutorial.com/oracle-administration/oracle-sqlloader/ – SelVazi Apr 25 '23 at 14:55

What is wrong with query, how do I correct the error?

1 Answers1