04-22-2009
Include Line Before Pattern Using Sed / Awk
Hi,
I have a sql file that runs something like this
vi Test.sql
REVOKE EXECUTE ON DEMO_USER.SQC_SAMP FROM PUBLIC;
REVOKE EXECUTE ON DEMO_USER.SQC_SAMP FROM DEMO_READ;
REVOKE SELECT ON DEMO_USER.DEMO_NOMINEE_TEST FROM DEMO_READ;
REVOKE EXECUTE ON DEMO_USER.SQC_SAMP FROM DEMO_APP_RL;
REVOKE SELECT ON DEMO_USER.DEMO_NOMINEE_TEST FROM DEMO_APP_RL;
Prompt Drop SYNONYM DEMO_NEWS;
DROP SYNONYM DEMO_USER.DEMO_NEWS;
Prompt drop View DEMO_NOMINEE_TEST;
DROP VIEW DEMO_USER.DEMO_NOMINEE_TEST;
Prompt drop Function SQC_SAMP;
DROP FUNCTION DEMO_USER.SQC_SAMP;
Prompt Type DEMO_ID_REC;
-- Difference Details :
-- Actual Compared # of Uppercase Lowercase White Other
-- Length Length Lines Letters Letters Space Chars
-- ******************************************************************
-- Comp * 99 72 5 13 40 26 20 *
-- Ref * 132 97 7 32 40 34 26 *
-- ******************************************************************
Prompt Type DEMO_ID_REC;
--
-- DEMO_ID_REC (Type)
--
-- Dependencies:
-- STANDARD (Package)
--
CREATE TYPE DEMO_USER.DEMO_id_rec AS OBJECT
(
LINK_ID varchar2(12),
DEMO_ID varchar2(50),
SAMPLE_FLG CHAR(1)
)
/
SHOW ERRORS;
Prompt Type STRINGTABLETYPE;
--
-- STRINGTABLETYPE (Type)
--
-- Dependencies:
-- STANDARD (Package)
--
CREATE OR REPLACE TYPE DEMO_USER."STRINGTABLETYPE" AS TABLE
OF VARCHAR2 (255)
/
SHOW ERRORS;
Basically the Script must look for All Lines that have either CREATE TYPE / CREATE OR REPLACE TYPE and must pick the type name which is usually
3rd Word for Lines with CREATE TYPE TYPENAME and 5th word for lines containing CREATE OR REPLACE TYPE TYPENAME
The script must generated a DROP TYPE TYPENAME FORCE; and Place this DROP Command just before the CREATE / CREATE OR REPLACE Type commands.
Like in the above case the expected outcome is
vi out.sql
REVOKE EXECUTE ON DEMO_USER.SQC_SAMP FROM PUBLIC;
REVOKE EXECUTE ON DEMO_USER.SQC_SAMP FROM DEMO_READ;
REVOKE SELECT ON DEMO_USER.DEMO_NOMINEE_TEST FROM DEMO_READ;
REVOKE EXECUTE ON DEMO_USER.SQC_SAMP FROM DEMO_APP_RL;
REVOKE SELECT ON DEMO_USER.DEMO_NOMINEE_TEST FROM DEMO_APP_RL;
Prompt Drop SYNONYM DEMO_NEWS;
DROP SYNONYM DEMO_USER.DEMO_NEWS;
Prompt drop View DEMO_NOMINEE_TEST;
DROP VIEW DEMO_USER.DEMO_NOMINEE_TEST;
Prompt drop Function SQC_SAMP;
DROP FUNCTION DEMO_USER.SQC_SAMP;
Prompt Type DEMO_ID_REC;
-- Difference Details :
-- Actual Compared # of Uppercase Lowercase White Other
-- Length Length Lines Letters Letters Space Chars
-- ******************************************************************
-- Comp * 99 72 5 13 40 26 20 *
-- Ref * 132 97 7 32 40 34 26 *
-- ******************************************************************
Prompt Type DEMO_ID_REC;
--
-- DEMO_ID_REC (Type)
--
-- Dependencies:
-- STANDARD (Package)
--
DROP TYPE DEMO_USER.DEMO_id_rec FORCE;
CREATE TYPE DEMO_USER.DEMO_id_rec AS OBJECT
(
LINK_ID varchar2(12),
DEMO_ID varchar2(50),
SAMPLE_FLG CHAR(1)
)
/
SHOW ERRORS;
Prompt Type STRINGTABLETYPE;
--
-- STRINGTABLETYPE (Type)
--
-- Dependencies:
-- STANDARD (Package)
--
DROP TYPE DEMO_USER."STRINGTABLETYPE" FORCE;
CREATE OR REPLACE TYPE DEMO_USER."STRINGTABLETYPE" AS TABLE
OF VARCHAR2 (255)
/
SHOW ERRORS;
Any shell script compatible with ksh on Solaris 8 would be fine.
Thanks,
Rajan
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hi,
I need help with using an awk or sed filter on the below line
ALTER TABLE "ACCOUNT" ADD CONSTRAINT "ACCOUNT_PK" PRIMARY KEY ("ACCT_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "WMC_DATA" LOGGING ENABLE
Look for... (2 Replies)
Discussion started by: rajan_san
2 Replies
2. UNIX for Advanced & Expert Users
Hi,
I need help with using an awk or sed filter on the below line
ALTER TABLE "ACCOUNT" ADD CONSTRAINT "ACCOUNT_PK" PRIMARY KEY ("ACCT_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "WMC_DATA" LOGGING ENABLE
Look for... (1 Reply)
Discussion started by: rajan_san
1 Replies
3. Shell Programming and Scripting
hi guys,
I want to do pattern matching with awk or sed but I don't know how. here's what I want:
I have a line number for a pattern that I have already found using grep, and I know a pattern like "---" that happens a few lines above that certain line number. I want to print out the chunk... (1 Reply)
Discussion started by: alirezan
1 Replies
4. Shell Programming and Scripting
awk , sed Experts,
I want to remove first and last line after pattern match "vg" :
I am trying : # sed '1d;$d' works fine , but where the last line is not having vg entry it is deleting one line of data.
- So it should check for the pattern vg if present , then it should delete the line ,... (5 Replies)
Discussion started by: rveri
5 Replies
5. Shell Programming and Scripting
cat file |grep -v "xx" | grep -v "yy" |grep -v "zz" (3 Replies)
Discussion started by: yanglei_fage
3 Replies
6. Shell Programming and Scripting
Dear Unix Forums,
I am hoping you can help me with a pattern matching problem.
What am I trying to do?
I want to replace multiple lines of a text file (that match a multi-line pattern) with a single line of text. These patterns can span several lines and do not always have the same number of... (10 Replies)
Discussion started by: thefang
10 Replies
7. Shell Programming and Scripting
Hi All:
I need your help please.
I have a include file *.awk , and I would want to know if it's there a option's awk where I can use this file include, for example:
awk -f decodifica_bitmap.awk cadena_hex.txt -e /home/snh/Awk/include/funciones.awk > sal.out
it give error:
awk:... (1 Reply)
Discussion started by: solaris21
1 Replies
8. Shell Programming and Scripting
'Hi
I'm using the following code to extract the lines(and redirect them to a txt file) after the pattern match. But the output is inclusive of the line with pattern match.
Which option is to be used to exclude the line containing the pattern?
sed -n '/Conn.*User/,$p' > consumers.txt (11 Replies)
Discussion started by: essem
11 Replies
9. Shell Programming and Scripting
I am trying to combine lines with these conditions:
1. First line starts with text of "libname VALUE db2 datasrc" where VALUE can be any text.
2. If condition1 is met then continue to combine lines through a line that ends with a semicolon.
3. Ignore case when matching patterns and remove any... (5 Replies)
Discussion started by: Wes Kem
5 Replies
10. UNIX for Beginners Questions & Answers
In the awk piped to sed below I am trying to format file by removing the odd xxxx_digits and whitespace after, then move the even xxxx_digit to the line above it and add a space between them. There may be multiple lines in file but they are in the same format. The Filename_ID line is the last line... (4 Replies)
Discussion started by: cmccabe
4 Replies