Skip to Main Content
  • Questions
  • Problems with my trigger and my rest service

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Edisson.

Asked: January 25, 2022 - 5:11 am UTC

Last updated: January 26, 2022 - 5:27 pm UTC

Version: Oracle Database 19c

Viewed 100+ times

You Asked

Greetings,

I have a problem with a rest service that I made from Apex in version 21.1 and at the time of making the post method to insert I started to have problems with my trigger, and it indicates the following error:

<b>555 User Defined Resource Error</b>
The request could not be processed because an error occurred whilst attempting to evaluate the SQL statement associated with this resource. Please check the SQL statement is correctly formed and executes without error. SQL Error Code: 1843, Error Message: ORA-01843: not a valid month ORA-06512: at line 10


I do not understand the reason for the error, since the same thing does not happen to me from Apex, but I did the rest service fails, and I understand that it is because of the sysdate, but it does not make sense because it is server side, this is my code:

CREATE OR REPLACE TRIGGER "F_TICKETS_BIU" 
    BEFORE INSERT OR UPDATE  
    ON F_TICKETS
    FOR EACH ROW 
    DECLARE
        DATETO DATE;
        TIMETO VARCHAR2(200);
        USER_ID_R NUMBER;
BEGIN 
SELECT 
TO_DATE(TO_CHAR(FROM_TZ( CAST( SYSDATE AS TIMESTAMP ), 'UTC' )AT TIME ZONE 'AMERICA/BOGOTA','MM/DD/YYYY')),  
TO_CHAR(FROM_TZ( CAST( SYSDATE AS TIMESTAMP ), 'UTC' )AT TIME ZONE 'AMERICA/BOGOTA','HH24MISS'), (SELECT USER_ID FROM F_USERS WHERE UPPER(USER_EMAIL) = NVL(COALESCE(SYS_CONTEXT('APEX$SESSION','APP_USER'),USER),'TEST@SYWORK.NET'))
INTO DATETO,TIMETO, USER_ID_R
FROM   DUAL; 
    IF INSERTING THEN 
        :NEW.TICKET_OPEN_DATE := DATETO; 
        :NEW.TICKET_OPEN_TIME := TIMETO;
        :NEW.USER_ID := USER_ID_R;
        :NEW.TICKET_STATUS_ID := 1;
    END IF; 
        :NEW.TICKET_CLOSE_DATE := NULL; 
        :NEW.TICKET_CLOSE_TIME := NULL;
END F_TICKETS_BIU;




Thank you.


and Chris said...

Look carefully at the error message:

ORA-01843: not a valid month


There's a date conversion error somewhere - very likely due to implicit conversions in the to_char/to_date/cast conversions going on.

These expressions look overly complex to me. It looks like this is trying to get the current date with no time (midnight) in Bogota. If so you could:

- Call SYSTIMESTAMP to get the current datetime including the time zone for the DB server
- Use AT TIME ZONE to convert this to Bogota
- Pass this to TRUNC, which returns the value as a date with a time of midnight, e.g.:

select trunc ( 
         systimestamp at time zone 'america/bogota'  
       ) dt
from   dual;

DT
-----------------
26-JAN-2022 00:00


Though instead of doing this in a trigger, you'd be better off making this in the column default:

alter table ...
  modify insert_date default trunc ( 
  systimestamp at time zone 'america/bogota' 
)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.