Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

I need to compare CVS files to Oracle code extracts from our Production environment. The issue is that some of the CVS files do not declare the function (package, procedure) in the same format as the Oracle extract, which later flags the files as different when using Text:Diff.

This difference:

CREATE OR REPLACE FUNCTION DC_F_COD_RANDOM_MMA RETURN VARCHAR2 IS

vs.

CREATE OR REPLACE FUNCTION "TRON2000"."DC_F_COD_RANDOM_MMA" RETURN VARCHAR2 IS

is one that I can safely ignore.

My approach is to open each CVS file, and check to see if it declares in the Oracle format. If not, replace the current declaration with a cleaned up Oracle one. (this is actually my first question - does this sound like a valid approach?)

So I look for the piece between 'CREATE OR REPLACE' and 'AS' or 'IS'. Inside there, I will find 'FUNCTION', 'PROCEDURE' or 'PACKAGE', and the actual name. So in the code I'm working on below, I have a function which is not in the Oracle format - so I have to change the file contents to

CREATE OR REPLACE FUNCTION "TRON2000"."DC_F_COD_RANDOM_MMA" RETURN VARCHAR2 IS

Is there a way to do this cleanly / easily, without all the IF/ELSE blocks that I'm building out - my current approach feels like I'm going to be sprawling quickly into something that is unmanageable.

Your thoughts / pointers / advice is GREATLY appreciated!

#!/usr/bin/perl -w use strict; use warnings; use 5.010; $/=undef; my $start_search_str = 'CREATE OR REPLACE '; while (<DATA>) { my $rc; # if the file contents are in the Oracle extract form if( $rc = /$start_search_str (.+) \"TRON2000\"\.\"/ .. /(AS|IS)/ +){ # file is the right format - get the next one last; } else { # nope - need to fix them - first - get the part of the string # between 'CREATE OR REPLACE ' and (AS|IS), then insert # "TRON2000"."<name>" around the name my $contents = $_; $contents =~ /(($start_search_str)(.*)(AS|IS))/si; my $function_name = $3; if ($3 =~ /FUNCTION/si) { $function_name =~ /( FUNCTION(.*) RETURN)/si; print "NOW: function_name: $function_name\n"; # now substitute and write it back to $rc }; }; } __DATA__ CREATE OR REPLACE FUNCTION DC_F_COD_RANDOM_MMA RETURN VARCHAR2 IS -- l_random_code1 VARCHAR2(1) ; RETURN l_random_code ; -- END dc_f_cod_random_mma ;

In reply to String replacement preparation for file comparison by TJRandall

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others avoiding work at the Monastery: (8)
    As of 2020-11-27 20:42 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      No recent polls found

      Notices?