1

We are using liquibase to create and manage tables in Oracle. I have an issue while creating a sequence with max value of ID column from a table. In liquibase I have tried the following.

<sql>Create Sequence id_sequence starts with (Select max(id) from tableName)</sql>

It shows an Invalid number error. I think it is due to select query does not return a number. I also tried to number and got the same error for the following

<sql>Create Sequence id_sequence starts with TO_NUMBER((Select max(id) from tableName))</sql>

Is there a way to avoid this error with liquibase. I don't find a solution based for liquibase. So I asked here

venkat
  • 442
  • 5
  • 17

1 Answers1

1

I don't know Liquibase, but - as you tagged it with Oracle tag, then it just won't work that way.

Select MAX value first, then use it in CREATE SEQUENCE. You'll need dynamic SQL. Here's an example:

SQL> declare
  2    l_max number;
  3  begin
  4    select max(deptno) into l_max from dept;
  5
  6    execute immediate 'create sequence id_sequence start with ' || l_max;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select id_sequence.nextval from dual;

   NEXTVAL
----------
        40

SQL> select id_sequence.nextval from dual;

   NEXTVAL
----------
        41

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Yes, I have tried it in the SQL plus and it worked. I am on the way to make this work with liquibase. Any reason, why the sub query cannot directly return the number to create sequence query? – venkat Oct 03 '19 at 12:15
  • Documentation says that it must be an integer. Doesn't say anything about subqueries, but - they just don't work. I don't know why (except that Oracle programmed it that way). – Littlefoot Oct 03 '19 at 12:25
  • Thanks, I found a way in liquibase to run procedural SQL! – venkat Oct 03 '19 at 12:27