sqloracle-databasesubqueryora-00936

How to pass Timestamp to Oracle Function?


I have a table and storing date value in it and I need to fetch that value and pass that as parameter to function. But whenever I try to execute my code it's throwing error like

ORA-00936: missing expression.

Following is my code sample:

SELECT refno, 
       Tatcalculate(to_timestamp(SELECT h_modified_date
                                   FROM TPADETAILS
                                  WHERE refno = 'WOC0021946','DD/MM/YYYY HH24:MI:SS'))
 FROM Table1;

Table1 is my table and h_modified_date is the column. Tatcalculate() is the function.

When I run inner query it's returning the date value from the table but when I execute complete above query then its throwing the error.
Anybody please help me to resolve this.


Solution

  • I would put the to_timestamp() around the column name, rather than around the select statement.

    Also, you need to enclose the select statement in its own brackets. Thus:

    SELECT refno, Tatcalculate((SELECT to_timestamp(h_modified_date,'DD/MM/YYYY HH24:MI:SS') FROM TPADETAILS WHERE refno='WOC0021946'))
    FROM Table1;
    

    ETA: Given the extra information that the tatcalculate parameter is DATE datatype and also the h_modified_date column is a DATE, there is absolutely no need to do any datatype conversions at all. Therefore, the query becomes:

    SELECT refno, Tatcalculate((SELECT h_modified_date FROM TPADETAILS WHERE refno='WOC0021946'))
    FROM Table1;