Re: INDEXFILE and Line Breaks in DDL

From: Ethan Post <Blah_at_Blah.com>
Date: Sat, 15 Sep 2001 15:32:49 GMT
Message-ID: <BwKo7.6563$6c5.205027@news1.rdc1.sdca.home.com>

For anyone interested in a solution here you go. I used UTL_FILE. I am sure if I read the sed/awk docs for the next 2 hours I could make it a lot easier, perhaps someone will post this as an awk script. Here is what it does do fix lines with fragments.

Remove REM's, CONNECT and "..." lines.
Check to see if line ends in blank or semi-colen, if so line is fine write it out.
If line ends in something else find the last blank in line and write out everything before it and save the fragment for the next line.
If the line has no blank spaces then use the whole line as the fragment which is appended to the next line before it is processed.
End by writing out the final fragment as you exit the loop.

Seems to have worked.

declare

   hand_in     utl_file.file_type;
   hand_out    utl_file.file_type;
   file_in     varchar2(25) := 'test.sql';
   file_out    varchar2(25) := 'prodctl2.sql';
   utl_dir     varchar2(25) := '/u06/export/';
   more_lines  boolean := true;
   l1          varchar2(1000);
   l2          varchar2(1000);
   c           varchar2(1);
   i           number := 0;
   l_frag      varchar2(1000) := '';

begin

hand_in := utl_file.fopen(utl_dir,file_in,'r'); hand_out := utl_file.fopen(utl_dir,file_out,'w');

while more_lines loop

      begin
      i := i + 1;
      -- read in a line
      utl_file.get_line(hand_in, l1);
   if substr(l1,1,7) = 'CONNECT' then
      l1 := '';

end if;
if substr(l1,1,3) = 'REM' then

      l1 := substr(l1,6);
      if substr(l1,1,3) = '...' then
      l1 := '';
      end if;

end if;

l1 := l_frag || l1;

if length(l1)>0 then

      c := substr(l1,-1);
         if c = ' ' or c=';' then
     null;
     l_frag := '';
     utl_file.put_line(hand_out, l1);
      else
     -- cut current line back to last space and save fragment
     i := instr(l1,' ',-1); -- last space in line
     if i = 0 then
     l_frag := l1;
     l1 := '';
     else
        l_frag := substr(l1,i+1);
        l1 := substr(l1,i);
     utl_file.put_line(hand_out, l1);
           --utl_file.put_line(hand_out,'-- look');
     end if;
         end if;
      end if;
   exception
      when no_data_found then
      more_lines := false;

utl_file.put_line(hand_out, l_frag);
utl_file.put_line(hand_out, '-- EOF');

      when others then
      raise;

   end;
   end loop;
   utl_file.fclose(hand_in);
   utl_file.fclose(hand_out);
exception
  when others then

     dbms_output.put_line(dbms_utility.format_error_stack);
     if utl_file.is_open(hand_in) then
        utl_file.fclose(hand_in);
     end if;
     if utl_file.is_open(hand_out) then
        utl_file.fclose(hand_out);
     end if;

end;

"Ethan Post" <Blah_at_Blah.com> wrote in message news:sJIo7.6261$6c5.201625_at_news1.rdc1.sdca.home.com...
> Thanks for the ideas, the statements with the bad line breaks still cr*p
> out. I wrote my own program in VB that will fix this but an 18 mile drive
> into work is required to fetch it. Maybe I will write a stored procedure
> using UTL_FILE to fix it. Will let you know. - E
>
> "koert54" <koert54_at_nospam.com> wrote in message
> news:aHHo7.54870$6x5.11759267_at_afrodite.telenet-ops.be...
> > hmm - if you're using sqlplus in 8i you should issue 'set sqlblank on' -
> > otherwise run the indexfile in svrmgrl
> > Or you could use Pretoria - an indexfile pretty printer ... it can also
> > merge new storage parameters in the indexfile
> > http://sourceforge.net/projects/pretoria/
> >
> >
> > "Ethan Post" <Blah_at_Blah.com> wrote in message
> > news:iLEo7.6183$6c5.199800_at_news1.rdc1.sdca.home.com...
> > > Back to my original problem again.
> > >
> > > imp x/x file=file.dmp indexfile=abc.sql fromuser=a touser=b
> > >
> > > Then VI and remove REM's.
> > >
> > > When running numerous statements are invalid due to line-breaks in
> column
> > > names.
> > >
> > > What gives??? I swear I did this on some machines last week and never
> saw
> > > any invalid SQL. Both are AIX and Oracle 8i. Help will be greatly
> > > appreciated.
> > >
> > > Thanks,
> > > Ethan Post
> > >
> > >
> >
> >
>
>
Received on Sat Sep 15 2001 - 10:32:49 CDT