formating sql file using awk or sed

Shell Programming and Scripting
1

Hi,
I have a file where I would like to add a prompt type object_name
statement before every create commnad

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
2 
$
$
$ 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;
/
 
$
$

tyler_durden

3

Perfect ! works like charm. you are the man ! Thank-you so much.

---------- Post updated at 12:48 PM ---------- Previous update was at 12:38 PM ----------

one more question. how do I ignore case. For e.g in the above text for create or objects names?

4

Use the "i" modifier -

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

tyler_durden

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