Using groovy: update timestamp oracle column

Question

I have two groovy scripts that make respectively an insert and an update on a oracle table that has a string column and two timestamp columns, created like as follows:

CREATE TABLE sn_token (
    "token" varchar2(500 char) NOT NULL,
    created_at timestamp NOT NULL,
    updated_at timestamp,
    PRIMARY KEY ("token")
);

In the first groovy script I make an insert on this table using this groovy code snippet (I omit the snippet code that makes the connection to the database):

import java.util.Date;
import org.joda.time.DateTime;

DateTime now = DateTime.now()
Date date = now.toDate()
def createdTimestamp = date.toTimestamp()
def map = [token : "myToken", created_at : createdTimestamp]
sql.execute "INSERT INTO sn_token (\"token\", created_at) VALUES ($map.token, $map.created_at)"

and all it's ok, and the timestamp that is stored, logged into console, is:

2018-07-20 09:38:24.191

In the second script I make an update on the same row, using this groovy code snippet (I omit the snippet code that makes the connection to the database):

import java.util.Date;
import org.joda.time.DateTime;

DateTime now = DateTime.now()
Date date = now.toDate()
def updatedTimestamp = date.toTimestamp()
def myToken = "myToken"
sql.execute 'UPDATE sn_token SET updated_at = \'' + updatedTimestamp + '\' WHERE "token" = \'' + myToken + '\''

The value of the updatedTimestamp variable logged into console is:

2018-07-20 09:40:44.706

and the query that is generated is:

UPDATE sn_token SET updated_at = '2018-07-20 09:40:44.706' WHERE "token" = "myToken"

Although the two timestamp variables have the same format, the update sql returns the error

ORA-01843: not a valid month

and I don't understand why..

Can someone help me to solve this problem?

Thanks

You are relying on the database settings for the date format. You should use the oracle to_date function in your query and supply the correct format eg: to_date('2018-07-20 09:20:10','rrrr-mm-dd hh24:mi:ss') — Graham H, Jul 20 '18 at 08:21

score 0 · Accepted Answer · answered Jul 20 '18 at 08:23

For your own sake, get rid of double quotes when creating Oracle objects. By default, they will be created in uppercase, but you can reference anyway you want (upper, lower, mixed case). If you insist on creating a column as "token", then you'll have to reference it that way, always (double quotes, name in lower case).

As of your question: see if TO_TIMESTAMP solves the problem. Here's an example:

SQL> create table sn_token
  2    (token      varchar2(10),
  3     updated_at timestamp
  4    );

Table created.

SQL>
SQL> insert into sn_token (token) values (1);

1 row created.

SQL> update sn_token set
  2    updated_at = to_timestamp('2018-07-20 09:40:44.706', 'yyyy-mm-dd hh24:mi:ss.ff3');

1 row updated.

SQL> select * from sn_token;

TOKEN      UPDATED_AT
---------- ------------------------------
1          20.07.18 09:40:44,706000

SQL>

Thanks a lot, with the to_timestamp function it worked. But how I need to use this function in the update statement? Ps: thanks for the suggestions about double quotes. — Luca, Jul 20 '18 at 08:56
You're welcome. Saying "how I need ..." - did you mean "why"? Well, because - if you try to insert some value enclosed into single quotes, Oracle treats it as a string. It tries to implicitly convert it to a timestamp, but - if it fails (because your system's timestamp format differs from what you provided) and raises an error. However, if you explicitly convert that string into a timestamp (using the TO_TIMESTAMP function), then there's no doubt. — Littlefoot, Jul 20 '18 at 09:20

Using groovy: update timestamp oracle column

1 Answers1