How to check if input parameter of timestamp type in oracle stored procedure is null?

Question

We have an oracle stored procedure which accepts multiple timestamp input parameters. This stored procedure is invoked from typescript. So, when typescript invokes this stored procedure, there is high chance that value for some of the timestamp input parameters could be sent as null.

Can you please help to check if timestamp is null or not in stored procedure. Please not that we cannot set any default value to timestamp as select statement stored procedure had conditions where it checks for "greater/less than, equals" conditions.

Below stored proc should send message back but it doesn't.

CREATE OR REPLACE PROCEDURE get_task_list (
    p_startdate         IN   TIMESTAMP,
    p_enddate           IN   TIMESTAMP,
    .
    .
    .
    p_message           OUT  VARCHAR2
)
AS
BEGIN
    IF p_startdate IS NULL THEN
        p_message := 'p_startdate is null ';
    END IF;
    
END get_task_list;

Below is the Typescript code snippet for startdate bind parameter.

    const p_startdate: OracleDB.BindParameter = {
        dir: OracleDB.BIND_IN,
        val: new Date(request.tasklist.startdate as unknown as string),
        type: OracleDB.DB_TYPE_TIMESTAMP,
    }

While debugging, we found out that typescript evaluates startdate bind parameter as below:

p_startdate: {
     "dir": 3001,
     "val": null,
     "type": {
         "num": 2012,
         "name": "DB_TYPE_TIMESTAMP",
         "columnTypeName": "TIMESTAMP",
         "_bufferSizeFactor": 11,
         "_oraTypeNum": 180,
         "_csfrm": 0
     }
 }

Versions Used:
"node": "^21.6.1",
"oracledb": "^6.3.0",
"typescript": "^5.3.3"

Answer 1

Have you tried checking if the value is null or an empty string in typescript (assuming from your comment that request.tasklist.startdate has the types string or null)?

const p_startdate: OracleDB.BindParameter = {
    dir: OracleDB.BIND_IN,
    val: (
            (request.tasklist.startdate === null || request.tasklist.startdate === "")
            ? null
            : new Date(request.tasklist.startdate)
         ),
    type: OracleDB.DB_TYPE_TIMESTAMP,
}

---

More generally:

From your comment that the value of DUMP(p_startdate) is Typ=180 Len=7: 255,100,0,0,0,0,0 then your typescript code is creating an invalid timestamp (with the year 15500, month 0, day 0, hours -1, minutes -1 and seconds -1 - which is invalid in every component).

fiddle

You can reduce the problem space and just directly pass null to the procedure:

const p_startdate: OracleDB.BindParameter = {
    dir: OracleDB.BIND_IN,
    val: null,
    type: OracleDB.DB_TYPE_TIMESTAMP,
}

If that generates the expected message then you know the issue is with the code new Date(request.tasklist.startdate as unknown as string) and if it still generates the same issue then you know the problem is elsewhere.