12.1
I have a clob with DDL for a table, when a table has an identity column I want to rewrite it to use a default value of a sequence instead.
'create table test
(col1 NUMBER GENERATED ALWAYS AS IDENTITY ( START WITH 584997001 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 1000 NOORDER NOKEEP) NOT NULL,
col2 number,
col3 varchar2(10)
)'
OR if optional
'create table test
(col1 NUMBER GENERATED BY DEFAULT AS IDENTITY ( START WITH 584997001 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 1000 NOORDER NOKEEP) NOT NULL,
col2 number,
col3 varchar2(10)
)'
I want the text in the CLOB to look like this instead (assume Ive created my_sequence)
'create table test
(col1 NUMBER default MY_SEQUENCE.nextval,
col2 number,
col3 varchar2(10)
)'
So I have to find the first character of string 'GENERATED ' and replace everything up to where the next comma is, is this doable in one string replace or even programatically with PLSQL?