Home » SQL & PL/SQL » SQL & PL/SQL » ORA-04098: trigger 'SCOTT.CHANGE_HIST' is invalid and failed re-validation (oracle - 9i)
ORA-04098: trigger 'SCOTT.CHANGE_HIST' is invalid and failed re-validation [message #315877] Wed, 23 April 2008 02:07 Go to next message
venki.august27
Messages: 3
Registered: April 2008
Location: vijayawada
Junior Member
CREATE OR REPLACE PROCEDURE DYNSQL
AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;

The following exception is raissed
what to be done
Re: ORA-04098: trigger 'SCOTT.CHANGE_HIST' is invalid and failed re-validation [message #315881 is a reply to message #315877] Wed, 23 April 2008 02:15 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Welcome to the forum. Spend some time in reading the forum guidelines. Also from next time post your actual code something like this. Spend some time in reading the oracle reference manual. It's very good.

SQL> CREATE OR REPLACE PROCEDURE DYNSQL
  2  AS
  3  cur integer;
  4  rc integer;
  5  BEGIN
  6  cur := DBMS_SQL.OPEN_CURSOR;
  7  DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
  8  rc := DBMS_SQL.EXECUTE(cur);
  9  DBMS_SQL.CLOSE_CURSOR(cur);
 10  END;
 11  /

Procedure created.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL>

Quote:

ORA-04098: trigger "string.string" 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.

http://www.oracle.com/pls/db102/portal.portal_db?selected=3

Regards

Raj

[Updated on: Wed, 23 April 2008 02:17]

Report message to a moderator

Re: ORA-04098: trigger 'SCOTT.CHANGE_HIST' is invalid and failed re-validation [message #315886 is a reply to message #315877] Wed, 23 April 2008 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What you posted is useless.
The error in on 'SCOTT.CHANGE_HIST' trigger and you posted DYNSQL procedure.

Regards
Michel
Re: ORA-04098: trigger 'SCOTT.CHANGE_HIST' is invalid and failed re-validation [message #315908 is a reply to message #315886] Wed, 23 April 2008 03:16 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that the trigger SCOTT.CHANGE_HIST is a DDL trigger designed to track creation/modification of tables in the SCOTT schema.

This trigger has an error in it, and so @venki gets this error when he tries to run the Dynamic Sql.

If you want to see the errors in the trigger, run this:
SELECT *
FROM all_errors
WHERE name = 'CHANGE_HIST'
Previous Topic: How to unlock table partition
Next Topic: Reg the Query Optimization in Oracle
Goto Forum:
  


Current Time: Sun Dec 04 20:34:03 CST 2016

Total time taken to generate the page: 0.17009 seconds