Shell Programming and Scripting

create or replace force view test_view_01
(
col1
col2
col3
)
as
   (select a,b,c from sometable );
create or replace view test_view_02
(
col4
col5
col6
)
as
   (select x,y,z from sometable );
create or replace view test_view_03
(
col8
col9
col1
)
as
   (select q,w,e from sometable );

create or replace package test_pkg
as
function f1
( blah in b1%type
  blah1 in b2%type)
return tab1 pipelined;
);
  
create or replace package body test_pkg
as

prompt view test_view_01
create or replace force view test_view_01
(
col1
col2
col3
)
as
   (select a,b,c from sometable );
prompt view test_view_02
create or replace view test_view_02
(
col4
col5
col6
)
as
   (select x,y,z from sometable );
   
   
prompt view test_view_03
create or replace view test_view_03
(
col8
col9
col1
)
as
   (select q,w,e from sometable );
prompt package test_pkg
create or replace package test_pkg
as
function f1
( blah in b1%type
  blah1 in b2%type)
return tab1 pipelined;
);
  
prompt package body test_pkg
create or replace package body test_pkg
as

$
$
$ cat -n f27.sql
     1  create or replace force view test_view_01
     2  (
     3  col1
     4  col2
     5  col3
     6  )
     7  as
     8     (select a,b,c from sometable );
     9  create or replace view test_view_02
    10  (
    11  col4
    12  col5
    13  col6
    14  )
    15  as
    16     (select x,y,z from sometable );
    17  create or replace view test_view_03
    18  (
    19  col8
    20  col9
    21  col1
    22  )
    23  as
    24     (select q,w,e from sometable );
    25
    26  create or replace package test_pkg
    27  as
    28  function f1
    29  ( blah in b1%type
    30    blah1 in b2%type)
    31  return tab1 pipelined;
    32  end;
    33  /
    34
    35  create or replace package body test_pkg
    36  as
    37    function f1
    38    ( blah in b1%type
    39      blah1 in b2%type)
    40    return tab1 pipelined
    41    is
    42    begin
    43      null;
    44    end;
    45  end;
    46  /
    47
$
$ perl -lne 'if (/^create.*\b(table|view|package)\b(.*)$/){print "prompt $1$2\n$_"} else {print}' f27.sql
prompt view test_view_01
create or replace force view test_view_01
(
col1
col2
col3
)
as
   (select a,b,c from sometable );
prompt view test_view_02
create or replace view test_view_02
(
col4
col5
col6
)
as
   (select x,y,z from sometable );
prompt view test_view_03
create or replace view test_view_03
(
col8
col9
col1
)
as
   (select q,w,e from sometable );
prompt package test_pkg
create or replace package test_pkg
as
function f1
( blah in b1%type
  blah1 in b2%type)
return tab1 pipelined;
end;
/
prompt package body test_pkg
create or replace package body test_pkg
as
  function f1
  ( blah in b1%type
    blah1 in b2%type)
  return tab1 pipelined
  is
  begin
    null;
  end;
end;
/
 
$
$

perl -lne 'if (/^create.*\b(table|view|package)\b(.*)$/i){print "prompt $1$2\n$_"} else {print}' f27.sql

awk '/^create/{print "prompt", $NF}1' infile

$ ruby -ane '$_="prompt #{$F[3..-1].join(" ")}\n#{$_}" if /^create/;puts $_' file