1 Reply Latest reply on Nov 8, 2013 9:34 AM by Jim Smith

    I used REGEXP_REPLACE to delete Scott. is there any way to delete Scott and Storage information same time using REGEXP_REPLACE...?

    Erhan_toronto

      I used REGEXP_REPLACE to delete Scott. is there any way to delete Scott and Storage information same time using REGEXP_REPLACE...?


       

      select REGEXP_REPLACE(dbms_metadata.get_ddl('TABLE','EMP'),'("Scott".)','', 1, 0, 'i') from dual;


       

      result:

       

      CREATE TABLE "EMP"

      ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,

      "ENAME" VARCHAR2(10),

      "JOB" VARCHAR2(9),

      "MGR" NUMBER(4,0),

      "SAL" NUMBER(7,2),

      "DEPTNO" NUMBER(2,0)

      ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

      TABLESPACE "USERS"


      I want:


      CREATE TABLE "EMP"

      ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,

      "ENAME" VARCHAR2(10),

      "JOB" VARCHAR2(9),

      "MGR" NUMBER(4,0),

      "SAL" NUMBER(7,2),

      "DEPTNO" NUMBER(2,0)

      )