Shell Programming and Scripting

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

try this

Code:

sed -e 's/\(CREATE TYPE \([^ ]*\) .*$\)/DROP TYPE \2 FORCE\n\1/g' -e 's/\(CREATE OR REPLACE TYPE \([^ ]*\) .*$\)/DROP TYPE \2 FORCE \n\1/g' Test.sql

cheers,
Devaraj Takhellambam

Hi,

Sorry its not working..

Thanks,
Rajan

Code:

sed -e 's/CREATE TYPE \([^ ]*\).*/DROP TYPE \1 FORCE;\n&/' -e 's/CREATE OR REPLACE TYPE \([^ ]*\).*/DROP TYPE \1 FORCE;\n&/' test.sql