CODEWITHSUNDEEP
oracle-database
Suresh Huse
Suresh Huse

Reputation: 97

Cant put Query inside Create sequence sql

The Below query is failing. How to correct it I need to put max id from TBL_PLANNING_REPOSITORY in the create sequence start with.

CREATE sequence auto_id_planning_repo 
start with (select MAX(ID) from TBL_PLANNING_REPOSITORY) increment by 1;

Upvotes: 0

Views: 38

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Use dynamic sql.

DECLARE
   v_startwith   NUMBER;
BEGIN
   SELECT MAX (ID) INTO v_startwith FROM TBL_PLANNING_REPOSITORY;

   EXECUTE IMMEDIATE
      'create sequence auto_id_planning_repo  start with ' || v_startwith || ' increment by 1';
END;

Upvotes: 2

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

DDL statements cannot be mixed with DML expressions. Correct way is something like this:

lock table tbl_planning_repository in exclusive mode;
var m number;
exec select max(id) into :m from tbl_planning_repository;
exec execute immediate 'create sequence ... start with ' || :m || ' increment by 1';

Upvotes: 0

Related Questions