How to convert varchar2 input parameter to timestamp in stored procedure

We are using a proprietary product to develop our application. This product is capable of connecting to Oracle DB and executes SQL queries and stored procedures. However, today we found a weird issue that this product is not able to execute a stored procedure if there is a timestamp input parameter in stored procedure.

We are now trying to change ‘p_timestamp’ input parameter from timestamp to varchar2. So, application will send timestamp value as a string and then before/while executing insert statement inside stored procedure we want to convert this varchar2 value into timestamp. Also, this timestamp should be stored in UTC time zone.

How to convert the ‘p_timestamp’ varchar2 input parameter into timestamp?

Table:

CREATE TABLE MYAPPROVALS_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(64 CHAR),
    ERROR_CODE VARCHAR2(256 CHAR),
    ERROR_MESSAGE VARCHAR2(4000 CHAR),
    MESSAGE VARCHAR2(4000 CHAR)
);

Stored procedure:

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

  INSERT INTO MYAPPROVALS_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, TO_TIMESTAMP(trunc(p_timestamp)) at time zone 'UTC', p_request_Number, p_module_Name, p_process_name, p_version, p_task, p_error_Code, p_error_Message, p_message);

  COMMIT;

END;
/

Answer

Assuming that your input is in ISO8601 format with fractional seconds and a time zone then:

CREATE OR REPLACE PROCEDURE MYAPPROVALS_AUDIT_INSERT_RECORD(
  p_message_Type   IN myapprovals_audit.message_type%TYPE,
  p_component_Name IN myapprovals_audit.component_name%TYPE,
  p_username       IN myapprovals_audit.USERNAME%TYPE,
  p_timestamp      IN varchar2,
  p_request_Number IN myapprovals_audit.request_number%TYPE,
  p_module_Name    IN myapprovals_audit.module_name%TYPE,
  p_process_Name   IN myapprovals_audit.process_name%TYPE,
  p_version        IN myapprovals_audit.version%TYPE,
  p_task           IN myapprovals_audit.task%TYPE,
  p_error_Code     IN myapprovals_audit.error_code%TYPE,
  p_error_Message  IN myapprovals_audit.error_message%TYPE,
  p_message        IN myapprovals_audit.message%TYPE
)
IS
BEGIN
  INSERT INTO MYAPPROVALS_AUDIT (
    MESSAGE_TYPE,       -- You do not need to quote identifiers.
    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,
    TO_TIMESTAMP_TZ(                       -- Assuming TIMESTAMP WITH TIME ZONE
      p_timestamp,
      'YYYY-MM-DD"T"HH24:MI:SS.FF TZH:TZM' -- Your timestamp format model
    ) at time zone 'UTC',
    p_request_Number,
    p_module_Name,
    p_process_name,
    p_version,
    p_task,
    p_error_Code,
    p_error_Message,
    p_message
  );

  -- Do not COMMIT in the procedure.
  -- COMMIT when you finalise the transaction that way you can chain multiple
  -- procedures together and if one fails ROLLBACK the entire transaction.
END;
/

If it is in a different format then change the format model to suit your data.