0

I have an issue with an INSERT query which I have boiled down to the following problem.

My application uses Jdbi (version 3) and micronaut to connect to an Oracle 12c database. The query is performed as follows:

jdbi.withHandle<ProjectId, StatementException> {
    val projectId = it.createUpdate("""
        INSERT INTO projects(
            "number",
            site,
            candidate,
            creator_id,
            creator_name,
            editor_id,
            editor_name,
            parent_id,
            status,
            last_status_change
        )
        SELECT
            107,
            'SITE',
            'CAND',
            'a',
            'a',
            'a',
            'a',
            null,
            'NEW',
            '02-FEB-22 09.00.00 AM EUROPE/ROME'
        FROM DUAL
        """.trimIndent())
            .executeAndReturnGeneratedKeys("id")
            .mapTo(ProjectId::class.java)
            .one()

}

This results in the following error:

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
 [statement:"INSERT INTO projects(
    "number",
    site,
    candidate,
    creator_id,
    creator_name,
    editor_id,
    editor_name,
    parent_id,
    status,
    last_status_change
)
SELECT
    107,
    'SITE',
    'CAND',
    'a',
    'a',
    'a',
    'a',
    null,
    'NEW',
    '02-FEB-22 09.00.00 AM EUROPE/ROME'
FROM DUAL", arguments:{positional:{}, named:{}, finder:[]}]

If I copy & paste the same query on a sqlplus console it works fine:

SQL> INSERT INTO projects(
  2      "number",
  3      site,
  4      candidate,
  5      creator_id,
  6      creator_name,
  7      editor_id,
  8      editor_name,
  9      parent_id,
 10      status,
 11      last_status_change
 12  )
 13  SELECT
 14      119,       -- changed number to avoid unique constraint issues
 15      'SITE',
 16      'CAND',
 17      'a',
 18      'a',
 19      'a',
 20      'a',
 21      null,
 22      'NEW',
 23      '02-FEB-22 09.00.00 AM EUROPE/ROME'
 24  FROM DUAL
 25  ;

1 row created.

How can I make the query work with Jdbi?

NOTE: this is a dumbed down version of the query, in my real query I cannot use VALUES in place of the SELECT ... FROM DUAL because I have to compute the "number", I simplified the query and endedup with reproducing the same problem with the above simple query.

The real query is more like:

INSERT INTO projects(
    "number",
    site,
    candidate,
    creator_id,
    creator_name,
    editor_id,
    editor_name,
    parent_id,
    status,
    last_status_change
)
SELECT
    (
        SELECT MAX("number") FROM (
            SELECT MAX("number")+1 as "number" FROM projects WHERE site = :site_1
            UNION
            SELECT 0 FROM DUAL WHERE NOT EXISTS (SELECT * FROM projects WHERE site = :site_2)
        )
    ),
    :site,
    :candidate,
    :creator_id,
    :creator_name,
    :editor_id,
    :editor_name,
    :parent_id,
    :status,
    :last_status_change
FROM DUAL
GACy20
  • 949
  • 1
  • 6
  • 14
  • 2
    I know nothing about tools you use, but - I've seen quite often that such an error happens when people terminate SQL statement with a semi-colon. There's no evidence (in what you posted) that you did the same, but - I thought that it won't hurt if I say it. **Apart from that**, I'd object to two more things: MAX + 1 will fail sooner or later in a multi-user environment; consider switching to a sequence. Also, "date" value you're inserting - that's a string, and it depends on NLS settings whether Oracle will covert it to a valid DATE datatype value. Consider inserting SYSDATE instead. – Littlefoot Feb 04 '22 at 11:00
  • @Littlefoot I don't have any semicolon. Also if that's was the case I would expect to see something in the error log. How can I use a sequence such that 1) The numbering restarts from 0 for every new site and 2) I don't have to create thousands of sequences (like one for each site)? That's why I'm using the max+1 approach. – GACy20 Feb 04 '22 at 11:17
  • Right; I though you don't have a semi-colon. That's not "it", then. Wouldn't know what is, sorry. As of MAX + 1: creating thousands of sequences is out of question, obviously. However, beware of duplicates! If two (or more) users run the same code at the same time, they might fetch the same MAX value, add 1 to it but only one of them will succeed to insert it (the one who commits first). Others will get dup_val_on_index error (if there's unique index on that column). If not, you'll have duplicates. How to fix it? One option is to lock the table (but that scales badly, others have to wait). – Littlefoot Feb 04 '22 at 11:21
  • 3
    I have a feeling like it happens due to double quotation for number column. Can you try to insert another table who has no column with double quotation ? If this is the reason, then maybe you can try escape characters before double quotation. – Yusuf Feb 04 '22 at 11:39
  • @Yusuf That's not it. I just tried definining an exact copy of that table renaming `"number"` to a plain `n` and the error remains the same: `ORA-00933: SQL command not properly ended [statement:" INSERT INTO projects2( n, ...` – GACy20 Feb 04 '22 at 13:34
  • For some reason using a table like `CREATE TABLE items(project varchar(5), n number(5))` and using a query like `INSERT INTO items(project, n) SELECT 'A', 1 FROM DUAL` works... so the `INSERT INTO ... SELECT ... FROM DUAL` *can* work in some cases... I really have no idea what Oracle doesn't like... – GACy20 Feb 04 '22 at 13:50
  • define number column as identity in db and remove from your insert statement. it could work? – Yusuf Feb 04 '22 at 15:23
  • @GACy20 - just a thought, but does your simpler working version still use `executeAndReturnGeneratedKeys` or just `execute`? – Alex Poole Feb 04 '22 at 18:28
  • Have you tried adding a semi-colon after DUAL at the end of the SQL statement? – NickW Feb 05 '22 at 22:48
  • @AlexPoole Now that I think about it I don't think so.. In the real case I have an `id` column for which I need to obtain the generated value. Today I will try to replicate adding an identical column to the simpler table and using `executeAndReturnGeneratedKeys` and we'll see.. – GACy20 Feb 07 '22 at 08:30
  • @NickW I believe I have tried but I'm not 100% sure, so I will try again later. AFAIK adding a semicolon to Jdbi queries should only create problems, not solve them but let's see if it has any effect. – GACy20 Feb 07 '22 at 08:31
  • I think you might either be hitting a [JDBI restriction](https://jdbi.org/#_generated_keys) - "test thoroughly", ha; or a [JDBC restriction](https://docs.oracle.com/database/121/JJDBC/jdbcvers.htm#JJDBC28099); or it's because you're using `select` not `values`. I know you said you can't use `values` because of your calculation; but you can still do `... values ((select ...), :site, ...)` as long as that subquery returns a single scalar value, which it will here. – Alex Poole Feb 07 '22 at 09:47
  • @AlexPoole I checked and yes, if I use a plain `execute()` this works. According to [this](https://stackoverflow.com/questions/5325033/plsql-insert-into-with-subquery-and-returning-clause) it seems that Oracle doesn't support using the `returning` clause except for `insert ... values`. I did not know that `values` also allowed subqueries.. and in fact using it does work. Thanks! – GACy20 Feb 07 '22 at 10:54
  • @GACy20 - great, glad you got it working. It might be worth you explaining the issue and your solution in an answer? (Incidentally, if all the :site variables have the same value then you could simplify the subquery to `SELECT COALESCE(MAX("number")+1,0)...` and remove the union.) – Alex Poole Feb 07 '22 at 12:05

0 Answers0