CODEWITHSUNDEEP
sqloracle-database
Justin Meek
Justin Meek

Reputation: 1

SQL Insert Statement causing error in Oracle

I'm trying to do this

INSERT INTO INSPECTIONOFFICE (INSPOFFICE_ID, INSPOFFICE_NAME, INSPOFFICE_STREET, INSPOFFICE_CITY, INSPOFFICE_STATE, INSPOFFICE_ZIPCODE, INSPOFFICE_CONTPERSONFNAME, INSPOFFICE_CONTPERSONLNAME, INSPOFFICE_CONTPERSONPHONE, INSPOFFICE_CONTPERSONEMAIL)
VALUES (12, 'InspectionOffice1', '121 Ames st', 'Omaha', 'NE', 68128, 'Brad', 'Foley', '4022853487', '[email protected]');

but I get an error

Error starting at line : 22 in command -
INSERT INTO INSPECTIONOFFICE (INSPOFFICE_ID, INSPOFFICE_NAME, INSPOFFICE_STREET, INSPOFFICE_CITY, INSPOFFICE_STATE, INSPOFFICE_ZIPCODE, INSPOFFICE_CONTPERSONFNAME, INSPOFFICE_CONTPERSONLNAME, INSPOFFICE_CONTPERSONPHONE, INSPOFFICE_CONTPERSONEMAIL)
VALUES (12, 'InspectionOffice1', '121 Ames st', 'Omaha', 'NE', 68128, 'Brad', 'Foley', '4022853487', '[email protected]')
Error at Command Line : 22 Column : 13
Error report -
SQL Error: ORA-04098: trigger 'JMEEK.INSPECTIONOFFICE_INSPECTIONOFF' is invalid and failed re-validation
04098. 00000 - "trigger '%s.%s' is invalid and failed re-validation"
*Cause: A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger.
*Action: Options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger.

Any idea how to fix this?

Upvotes: 0

Views: 991

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

The trigger JMEEK.INSPECTIONOFFICE_INSPECTIONOFF, which I am guessing you wrote because it's in the JMEEK schema, which (again I'm guessing) seems to be yours, is invalid, meaning it has compilation errors. To find out what those errors are you should execute the query

SELECT *
  FROM ALL_ERRORS
  WHERE OWNER = 'JMEEK' AND
        NAME = 'INSPECTIONOFFICE_INSPECTIONOFF'
  ORDER BY SEQUENCE

None of us can do much else for you because we don't know what this trigger looks like. If you need to see the source code you can execute the following query:

SELECT LINE, TEXT
  FROM ALL_SOURCE
  WHERE OWNER = 'JMEEK' AND
        NAME = 'INSPECTIONOFFICE_INSPECTIONOFF'
  ORDER BY LINE

Or you can use an IDE such as Oracle's free SQL Developer product which will make all this a lot easier for you.

Upvotes: 1

Frederic
Frederic

Reputation: 1028

seems you do not have permission access to the trigger JMEEK.INSPECTIONOFFICE_INSPECTIONOFF

or have some compilation problem with it...

you should take a look in the trigger.

or if you don't know what a trigger is, call a experienced job's teammate. :)

btw, if you are alone, and it don't matter too much.. you can disable / drop the trigger..

this command disable, you can enable later

ALTER TRIGGER JMEEK.INSPECTIONOFFICE_INSPECTIONOFF DISABLE;

this command drop the trigger, you will lose it forever (if you don't have a backup)

drop trigger JMEEK.INSPECTIONOFFICE_INSPECTIONOFF;

Upvotes: 1

Related Questions