I have a huge bunch of table scripts that needs to be changed, example script below.
Need to change the NUMBER(10) TO NUMBER(38)
Add 2 columns in the end of the script.
CREATE_DT char(10) NULL,
UPDATE_DT char(10) NULL,
How can this be done in a script.

The pattern I see now is there is a '/' delimiter at the end of each statement, so that the script can be read and everything with the delimiter can be considered a chunk and them if a CREATE TABLE is found, read line by line, replace the number(10) to number(38) and for the last line since there is no comma, add a comma and insert the next 2 lines..Is this how this can be approached. Any other ideas ?


/************************************************** ****
* *
* FILE.xqt Last Modified : 03/26/2003 07:52:50AM *
* *
************************************************** ****/

/********************************************
* TABLE : LOAD_FSA_CNTRB
********************************************/

declare
lObjectCount number;
begin
select
count(*)
into
lObjectCount
from
user_objects
where
object_name = 'LOAD_FSA_CNTRB' and
object_type = 'TABLE';

if(lObjectCount > 0) THEN
execute immediate 'drop table LOAD_FSA_CNTRB';
end if;
end;
/

CREATE TABLE LOAD_FSA_CNTRB
(
LOAD_ROW_ID number(10,0) ,
LOAD_CREATE_DTM date default sysdate,
LOAD_USUS_ID varchar2(20) default user,
LOAD_LOG_ACT_CD varchar2(1) default ' ' ,
LOAD_PHYS_ACT_CD varchar2(1) ,
LOAD_IMAGE_CD varchar2(1) ,
LOAD_TERM_ID varchar2(20) ,
TXN1_ROW_ID numeric(10,0) DEFAULT 0 NOT NULL,
LOAD_CK NUMBER(10) NOT NULL,
LOAD_PLAN_YEAR_DT TIMESTAMP(3) NOT NULL,
LOAD_FSA_TYPE VARCHAR2(01) NOT NULL,
LOAD_TXN_DT TIMESTAMP(3) NOT NULL,
LOAD_SEQ_NO NUMBER(5) NOT NULL,
GRGR_CK NUMBER(10) NOT NULL,
LOAD_PROCESS_DTM TIMESTAMP(3) NOT NULL,
LOAD_INPUT_SOURCE VARCHAR2(01) NOT NULL,
LOAD_EE_CNTRB_AMT NUMBER(18,4) NOT NULL,
LOAD_ER_CNTRB_AMT NUMBER(18,4) NOT NULL,
LOAD_MCTR_CRSN VARCHAR2(04) NOT NULL,
LOAD_LOCK_TOKEN NUMBER(5) NOT NULL,
SOURCE_ID TIMESTAMP(3) NOT NULL
)
/
declare
lObjectCount number;
begin
select
count(*)
into
lObjectCount
from
user_objects
where
object_name = 'LOADX_LOAD_ROW_ID' and
object_type = 'INDEX';
if(lObjectCount > 0) THEN
execute immediate 'drop index LOADX_LOAD_ROW_ID';
end if;
end;
/
CREATE UNIQUE INDEX LOADX_LOAD_ROW_ID
ON LOAD_FSA_CNTRB
(
LOAD_ROW_ID
)
/