I Would like to remove all occurences of the below pattern in my string.
SUPPLEMENTAL LOG GROUP "SAMPLESL" ("PIKEY") ALWAYS,
SUPPLEMENTAL LOG GROUP "SAMPLE2" ("UIKEY") ALWAYS,
How do I create a regular expression for this?
Essentially, I do not want any SUPPLEMENTAL LOG GROUP statements. This is generated by dbms_metadata.get_ddl.
You could use:
WITH cte(R) as (select
'CREATE TABLE TAB (X int)
--- something
SUPPLEMENTAL LOG GROUP "SAMPLESL" ("PIKEY") ALWAYS,
SUPPLEMENTAL LOG GROUP "SAMPLE2" ("UIKEY") ALWAYS,
--else
'
FROM DUAL
)
select R, regexp_replace(R, 'SUPPLEMENTAL LOG GROUP.*', '')
from CTE;
You may also use DBMS_METADATA.SET_TRANSFORM_PARAM to set the CONSTRAINTS ( which includes SUPPLEMENTAL LOG GROUP) to false before running DBMS_METADATA.GET_DDL. But the problem is that this will also disable the display of all other non-referential table constraints. I feel Oracle should have had SUPPLEMENTAL LOG GROUP as a separate Transform
parameter.
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,
'CONSTRAINTS',
FALSE);
select DBMS_METADATA.GET_DDL('TABLE','YOURTABLE','YOURSCHEMA') ddl from dual;
Refer SET_TRANSFORM_PARAM for more options.
