How to convert varchar2 input parameter to timestamp in stored procedure-CodePudding

We are using a proprietory 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.

PFB SQL queries for the table and 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.

Can you please help 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;
/

CodePudding user response：

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.

CodePudding user response：

Input will be like '2022-04-01T17:32:22.223Z'

If you're on a recent version of Oracle you can use TO_UTC_TIMESTAMP_TZ, i.e. instead of

TO_TIMESTAMP(trunc(p_timestamp)) at time zone 'UTC'

you can do

TO_UTC_TIMESTAMP_TZ(p_timestamp)

That will give you a timestamp with time zone, as UTC; if you want a plain timestamp you can cast it, implicitly during your insert, or explicitly:

CAST(TO_UTC_TIMESTAMP_TZ(p_timestamp) AS TIMESTAMP)

If the passed string is always exactly that format and always UTC, with the Z indicator, and you want to end up with a plain timestamp, you could also do:

TO_TIMESTAMP(p_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF"Z"')

Quick db<>fiddle showing all three, with your example string value.

It would probably still be better to investigate why the product is having a problem with timestamps, so you can make it pass the correct data type and avoid the conversion.