Shell Programming and Scripting

Hi,

Here is a sample of my Test File

$ cat TestFile1

Prompt Table DQZ_ALTER_SCHEMA_ID;
ALTER TABLE DQZ.DQZ_ALTER_SCHEMA_ID MONITORING;
ALTER TABLE DQZ.DQZ_ALTER_SCHEMA_ID STORAGE ( NEXT 3464K );
Prompt Table DQZ_ALTER_SCHEMA_ID;
ALTER TABLE DQZ.DQZ_ALTER_SCHEMA_ID
MOVE LOB (MESSAGE)
STORE AS (
TABLESPACE CLEAN_DATA1
STORAGE (
INITIAL 256K
NEXT 1384K
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 1
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
));
ALTER TABLE DQZ.DQZ_ALTER_SCHEMA_ID
MODIFY LOB (MESSAGE)
(
PCTVERSION 10
);

REVOKE EXECUTE ON DQZ.DQZ_FORMS_VALIDATEX FROM CLEANDATAVNDR;
REVOKE EXECUTE ON DQZ.DQZ_FORMS_REQUIREDX FROM CLEANDATAVNDR;
REVOKE EXECUTE ON DQZ.DQZ_FORMS_DEFAULTSX FROM CLEANDATAVNDR;
REVOKE EXECUTE ON DQZ.DQZ_FORMS_ALLOWEDX FROM CLEANDATAVNDR;
REVOKE EXECUTE ON DQZ.DQZ_FORMS_VALIDATEX FROM SECURITY_MASTER_RL;
REVOKE EXECUTE ON DQZ.DQZ_FORMS_REQUIREDX FROM SECURITY_MASTER_RL;
REVOKE EXECUTE ON DQZ.DQZ_FORMS_DEFAULTSX FROM SECURITY_MASTER_RL;
REVOKE EXECUTE ON DQZ.DQZ_FORMS_ALLOWEDX FROM SECURITY_MASTER_RL;
CREATE OR REPLACE TYPE DQZ."DQZ_SECURITY_OBJECT_CDS" as object (
BUS_DAYS_CNV varchar2(25),
SIP CHAR(8),
ISIN varchar2(12),
ACCRU_MONTH_DAY_COUNT varchar2(8),
ACCRU_YEAR_DAY_COUNT varchar2(8),
MAT_DT DATE,
DATED_DT DATE,
CPN_RT NUMBER,
OID_DT DATE,
CCODE varchar2(3),
EFF_DT DATE,
PMT_FREQ_CD varchar2(2))
/

End Of File

Here is my pattern filter conditions

1) Pattern Range must start with "ALTER TABLE"
2) Pattern Range ends when it finds ";"
3) Between this range i want to select all the patterns that contain pattern " MOVE "

From the above file here is what i want to select

Expected Output

ALTER TABLE DQZ.DQZ_ALTER_SCHEMA_ID
MOVE LOB (MESSAGE)
STORE AS (
TABLESPACE CLEAN_DATA1
STORAGE (
INITIAL 256K
NEXT 1384K
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 1
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
));

I do not mind a either a sed or an awk syntax to acheive this.Need this very urgently friends any quick response is very appreciated.

Thanks And Kind Regards,
Rajan.S

Hi,

this works for me:

Code:

sed -n '/ALTER TABLE/{N;/MOVE/!d};/ALTER TABLE/,/)\;/{p}' TestFile1

HTH Chris

Hi Chris,

Its giving me the below error

$sed -n '/ALTER TABLE/{N;/MOVE/!d};/ALTER TABLE/,/)\;/{p}' TestFile1
sed: command garbled: /ALTER TABLE/{N;/MOVE/!d};/ALTER TABLE/,/)\;/{p}

Thanks,
Rajan

Perhaps your sed version don't support the command separator ";", try this:

Code:

sed -n -e '/ALTER TABLE/{N;/MOVE/!d}' -e '/ALTER TABLE/,/)\;/p' file

Regards

Getting an error on this one too

$ sed -n -e '/ALTER TABLE/{N;/MOVE/!d}' -e '/ALTER TABLE/,/)\;/p' TestFile
sed: command garbled: /ALTER TABLE/{N;/MOVE/!d}

Copy and paste following lines to a file called sedfile:

Code:

/ALTER TABLE/{
N
/MOVE/!d
} 
/ALTER TABLE/,/;/p

then

Code:

sed -n -f sedfile Testfile1

Works Perfect Thanks Chris And Frank....