Oracle - update timestamp to GMT before inserting into the table-CodePudding

I am trying to implement the audit mechanism for our application so that all messages, errors, request/response etc. will be logged centrally in one table 'PROJECT_AUDIT'. Below stored proc will be used to insert the data into 'PROJECT_AUDIT' table. Here, I want to convert 'p_timestamp' value received as input to this stored proc into GMT time zone.

Can you please help. Stored Procedure:

    CREATE OR REPLACE PROCEDURE PROJECT_AUDIT_INSERT_RECORD(
       p_message_Type IN PROJECT_AUDIT.message_type%TYPE,
       p_component_Name IN PROJECT_AUDIT.component_name%TYPE,
       p_username IN PROJECT_AUDIT.USERNAME%TYPE,
       p_timestamp IN PROJECT_AUDIT.timestamp%TYPE,
       p_request_Number IN PROJECT_AUDIT.request_number%TYPE,
       p_module_Name IN PROJECT_AUDIT.module_name%TYPE,
       p_process_name IN PROJECT_AUDIT.process_name%TYPE,
       p_version IN PROJECT_AUDIT.version%TYPE,
       p_task IN PROJECT_AUDIT.task%TYPE,
       p_error_Code PROJECT_AUDIT.error_code%TYPE,
       p_error_Message PROJECT_AUDIT.error_message%TYPE,
       p_message PROJECT_AUDIT.message%TYPE
)
IS
BEGIN

  INSERT INTO PROJECT_AUDIT ("MESSAGE_TYPE", "COMPONENT_NAME", "USERNAME", "TIMESTAMP", "REQUEST_NUMBER", "MODULE_NAME", "PROCESS_NAME", "VERSION", "TASK", "ERROR_CODE", "ERROR_MESSAGE", "MESSAGE") 
  VALUES (p_message_Type, p_component_Name, p_username, p_timestamp, p_request_Number, p_module_Name, p_process_name, p_version, p_task, p_error_Code, p_error_Message, p_message);

  COMMIT;

END;
/

Table:

CREATE TABLE PROJECT_AUDIT (
ID NUMBER GENERATED ALWAYS AS IDENTITY,
MESSAGE_TYPE VARCHAR2(64 CHAR),
COMPONENT_NAME VARCHAR2(64 CHAR),
USERNAME VARCHAR2(32 CHAR),
TIMESTAMP TIMESTAMP WITH TIME ZONE NOT NULL,
REQUEST_NUMBER VARCHAR2(64 CHAR),
MODULE_NAME VARCHAR2(256 CHAR),
PROCESS_NAME VARCHAR2(256 CHAR),
VERSION VARCHAR2(64 CHAR),
TASK VARCHAR2(128 CHAR),
ERROR_CODE VARCHAR2(256 CHAR),
ERROR_MESSAGE VARCHAR2(4000 CHAR),
MESSAGE VARCHAR2(4000 CHAR)
);

CodePudding user response：

Just use at time zone to convert it.

p_timestamp at time zone 'UTC'

will return a new timestamp with time zone which has been converted to UTC (aka GMT). See this fiddle for an example.