Error Position: 0 Return: 1843 - ORA-01843: not a valid month ORA-06512:

Toad for Oracle & DevOps Toolkit
1

Hi All,

I have written a trigger statement which updates my z table upon logging onto my dev system.

But i get the below error

File: E:\pt85109b-retail\peopletools\src\pssys\stmupd.cppSQL error. Stmt #: 2291 Error Position: 0 Return: 1843 - ORA-01843: not a valid month ORA-06512: at “SYSADM.Z_LOGIN2”, line 7 ORA-04088: error during execution of trigger ‘SYSADM.Z_LOGIN2’
Failed SQL stmt:UPDATE PSOPRDEFN SET LASTSIGNONDTTM = TO_TIMESTAMP(:1,‘YYYY-MM-DD-HH24.MI.SS.FF’) WHERE OPRID = :2

This is my CODE

CREATE OR REPLACE TRIGGER SYSADM.z_LOGIN2
AFTER UPDATE OF lastsignondttm
ON SYSADM.PSOPRDEFN
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
Mutating EXCEPTION;
PRAGMA EXCEPTION_INIT (Mutating, -4091);
errorm VARCHAR2 (2000);

BEGIN
INSERT INTO SYSADM.PS_FZAP_AUDIT_SOX (oprid, oprdefndesc,lastsignondttm,
fzap_reason_cd,dbname,lastupdoprid,lastupddttm)

select distinct :new.oprid,:new.oprdefndesc,:new.lastsignondttm,’ ‘,’ ',:new.lastupddttm,:new.lastupdoprid
from sysadm.psroleuser b
where b.roleuser = :new.oprid
and (b.rolename = ‘FAS Change Admin’ or b.rolename=‘PeopleSoft Administrator’);
EXCEPTION
WHEN Mutating
THEN
errorm := SQLERRM;
– DBMS_OUTPUT.PUT_LINE(‘prompt 13’);
–INSERT_LOG('Error at Level ‘||LOG_LEVEL||’ with error message '||errorm);

WHEN NO_DATA_FOUND
THEN
NULL;
– DBMS_OUTPUT.PUT_LINE(‘prompt 14’);
END;

NOTE : Compiled the trigger , it says trigger created with no compilation error.

2

you have obviously problems with values inserted in SYSADM.PS_FZAP_AUDIT_SOX table.

If I can see that table description, maybe I can help more.

3

Thanks for pointing out, yes it was an issue with the record SYSADM.PS_FZAP_AUDIT_SOX table the fields were in diff order and insert values were in diff order . Thanks