Unable to pass TO_TIMESTMAP and use filter condition in query oracle

Question

DECLARE
    v_sql_stmt VARCHAR2(2000);
    v_INSERT_DATETIME  TIMESTAMP(6);
   
BEGIN

SELECT
         MAX(INSERT_DATETIME)
     INTO V_INSERT_DATETIME
     FROM
         LOG_TABLE;
    v_sql_stmt := 'INSERT INTO LOG_TABLE (ID,USER,INSERT_DATETIME) SELECT ID,USER,CREATE_DATETIME FROM OLD_OLG_TABLE 
                  WHERE  (CREATE_DATETIME>:V_INSERT_DATETIME OR REVIEW_DATETIME >:V_INSERT_DATETIME)';
    EXECUTE IMMEDIATE v_sql_stmt USING :V_INSERT_DATETIME, :V_INSERT_DATETIME;
   
END;
/

HERE I am getting ORA-01830: date format picture ends before converting entire input string.

I tried to pass like

TO_TIMESTMAP(:V_INSERT_DATETIME, 'DD-MON-RR HH.MI.SSXFF AM') and 
TO_TIMESTMAP(:V_INSERT_DATETIME, 'DD-MON-RR HH12.MI.SS.FF6 AM')

But I have got ORA-00904: "TO_TIMESTMAP": invalid identifier.

The INSERT_DATETIME is TIMESTAMP(6) and has values like '28-AUG-25 08.03.28.577664 PM' in table.

Any suggestion is appreciated. Thanks in advance

Please edit the question and include a minimal reproducible example with the CREATE TABLE and INSERT statements for your tables and sample data so that we can replicate the issue. — MT0
– MT0, Commented Aug 28 at 21:15
This appears to be an XY problem. The actual issue is that you are using bind variables when you should be using the PL/SQL variable you have declared. The question you are asking about TO_TIMESTAMP is, I am assuming, that you are passing TO_TIMESTAMP(...) as the value to the bind variable and does not make sense as bind variables do not work like substitution variables and, if you fix the underlying issue then that issue becomes irrelevant. — MT0
– MT0, Commented Aug 28 at 21:29

MT0 · Accepted Answer · 2025-08-28 21:24:46Z

2

Assuming that LOG_TABLE and OLD_LOG_TABLE both have TIMESTAMP columns for their respective date-time columns then never use TO_TIMESTAMP on values that are already a TIMESTAMP.

If you want to fix your query then the USING clause should use the declared PL/SQL variable and not a bind variable:

DECLARE
  v_sql_stmt        VARCHAR2(2000);
  v_INSERT_DATETIME TIMESTAMP(6);   
BEGIN
  SELECT MAX(INSERT_DATETIME)
  INTO   V_INSERT_DATETIME
  FROM   LOG_TABLE;

  v_sql_stmt := 'INSERT INTO LOG_TABLE (ID,USER,INSERT_DATETIME)
                 SELECT ID,USER,CREATE_DATETIME
                 FROM   OLD_OLG_TABLE 
                 WHERE  CREATE_DATETIME > :1
                 OR     REVIEW_DATETIME > :2';

  EXECUTE IMMEDIATE v_sql_stmt
  USING V_INSERT_DATETIME, V_INSERT_DATETIME;
END;
/

However, you do not need dynamic SQL or bind variables or TO_TIMESTAMP:

DECLARE
  v_INSERT_DATETIME  TIMESTAMP(6);
BEGIN
  SELECT MAX(INSERT_DATETIME)
  INTO   V_INSERT_DATETIME
  FROM   LOG_TABLE;

  INSERT INTO LOG_TABLE (ID,USER,INSERT_DATETIME)
  SELECT ID,
         USER,
         CREATE_DATETIME
  FROM   OLD_OLG_TABLE                        -- should this be old_log_table?
  WHERE  CREATE_DATETIME > V_INSERT_DATETIME  -- This is a PL/SQL variable not a bind variable 
  OR     REVIEW_DATETIME > V_INSERT_DATETIME; -- This is a PL/SQL variable not a bind variable
END;
/

However, you don't need PL/SQL and can use a single SQL query:

INSERT INTO LOG_TABLE (ID,USER,INSERT_DATETIME)
SELECT ID,
       USER,
       CREATE_DATETIME
FROM   OLD_OLG_TABLE                        -- should this be old_log_table?
       CROSS JOIN (
         SELECT MAX(INSERT_DATETIME) as INSERT_DATETIME
         FROM   LOG_TABLE
       ) l
WHERE  CREATE_DATETIME > l.INSERT_DATETIME
OR     REVIEW_DATETIME > l.INSERT_DATETIME;

edited Aug 28 at 21:24

answered Aug 28 at 21:09

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

synccm2012 Aug 29 at 3:24

,The problem is larger than this piece. Can't use simple pl/sql as I need to pass TRNC(CREATE_DATETIME)>'15-AUG-25' for first time and above condition in second time. Can not use :1 and :2 as I have more than 1 variable. While it works in lower environment but fails in higher env.

MT0 Aug 29 at 10:23

In the question you have asked, you can use a single PL/SQL query. If you have another problem which your question is not representative of then we cannot comment on that problem as we have not seen it. However, you can probably modify the query so it not need dynamic SQL or bind variables using

WHERE (create_datetime > l.insert_datetime OR review_datetime > l.insert_datetime) AND create_datetime >= DATE '2025-08-16'

.

MT0 Aug 29 at 10:26

You can use :1 and :2, just use :3 and :4, etc for subsequent variables and it matches with the position of the variable in the USING clause. It is potentially more confusing to have repeated bind variables with the same name when they are actually being referenced positionally by the USING clause (rather than being referenced by name).

synccm2012 Aug 29 at 3:47

@MTO, The first approach by you looks brighter. Thanks

synccm2012 Sep 1 at 9:24

I figured out the real problem. The query is taking create_datetime > l.insert_datetime as create_datetime > '28-AUG-25 08.03.28.577664 PM ' which throws error "ORA-01830: date format picture ends before converting entire input string" I tried TO_TIMESTMAP('28-AUG-25 08.03.28.577664 PM ', 'DD-MON-RR HH.MI.FF6 PM') it works. Question is DD-MON-RR HH.MI.FF6 PM or AM ? the input can be anything

Collectives™ on Stack Overflow

Unable to pass TO_TIMESTMAP and use filter condition in query oracle

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related