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;
Add a comment
|
2 Answers
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;
Comments
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';
answered Nov 16, 2017 at 12:41
Sanders the Softwarer
2,5061 gold badge15 silver badges29 bronze badges