Re: Two problems with Oracle and sequences

Edi Weitz wrote:
>   ORA-04002: INCREMENT must be a non-zero integer
> [...]
>        (unless (zerop incr)

Hi Edi,

That check for zero increment tooks right to me. I'll add it.

> [...]
>   (sequence-last 'foo42)
> error from Oracle which is due to the SQL statement
>   SELECT LAST_VALUE FROM _CLSQL_SEQ_FOO42
> [..]
> Shouldn't it be possible to implement CLSQL's sequence functions on
> Oracle without such a table?

This is a caused because clsql-oracle.lisp lacks a specialization for
DATABASE-SEQUENCE-LAST. Thus, the generic CLSQL sequence function is
invoked which uses tables managed by CLSQL. (These generic functions
in sql/sequences.lisp are used for SQL implementations that lack
sequences).

The proper fix is to add database-sequence-last to
db-oracle/oracle-sql.lisp. I don't have an oracle server currently
available to me.

Given the definition of database-sequence-next in oracle-sql.lisp, I'd
guess the proper function would be something like the below[1].

Would you mind testing that function and seeing if my sql syntax guess
is correct?

Thanks for your help,

Kevin

[1]
(defmethod database-sequence-last (sequence-name (database oracle-database))
  (caar (database-query
         (concatenate 'string "SELECT "
                      (sql-escape sequence-name)
                      ".LASTVAL FROM dual")
          database :auto nil)))