Cfrtjryk

I want to add a statement just after the text matching the conditions mentioned in a shell script,

Below is my sample File(SQL File) :

begin

AFFECTED_ROWS := 0;



UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;



DELETE FROM table_name

WHERE condition;



INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);



MERGE INTO employees e

    USING hr_records h

    ON (e.id = h.emp_id)

  WHEN MATCHED THEN

    UPDATE SET e.address = h.address

  WHEN NOT MATCHED THEN

    INSERT (id, address)

    VALUES (h.emp_id, h.address);

end;

I will take this file and perform below transformation,
Once I see any of below Text in sequence

1: "UPDATE ... SET ...;"

2: "DELETE ... FROM ...;"

3: "INSERT ... INTO ...;"

4: "MERGE ... INTO ... [WHEN MATCHED THEN | WHEN NOT MATCHED] ... [UPDATE|INSERT|DELETE] ... ;"

I need to add 1 extra row just after the semicolon

AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;

So My new file will look like something below

begin

AFFECTED_ROWS := 0;



UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;



DELETE FROM table_name

WHERE condition;

AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;



INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;



MERGE INTO employees e

    USING hr_records h

    ON (e.id = h.emp_id)

  WHEN MATCHED THEN

    UPDATE SET e.address = h.address

  WHEN NOT MATCHED THEN

    INSERT (id, address)

    VALUES (h.emp_id, h.address);

AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;



end;

I tried finding and implementing the approach in the scope of PLSQL but didn't find any generic way possible to get no of rows affected, so I thought
of text parsing but I don't know much about awk or sed.

For now what I was trying to do something like below:

sed '/Patterns Go Here/a AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;' temp.sql

So pattern may have conditions mentioned above

I have solved it by using perl instead of sed and using the IRS (Input Record Separator - $/) special variable to process lines by semi-colon, rather than by newline.

Any language that supports changing the IRS should work (probably possible with pure bash using IFS=';', but I haven't explored this).

In this example, perl will load it's buffer until the input record separator, then process all that content as a line - by changing what that separator is, you can process the file a statement at a time, rather than a line at a time. I can then match the pattern of the statement(s) I'm interested in, and append the affected rows logic to the line before it gets printed.

$ cat test.sql

SELECT non-matching-statement FROM table;



MERGE INTO employees e

    USING hr_records h

    ON (e.id = h.emp_id)

  WHEN MATCHED THEN

    UPDATE SET e.address = h.address

  WHEN NOT MATCHED THEN

    INSERT (id, address)

    VALUES (h.emp_id, h.address);



SELECT another-non-matching-statement FROM table;



end;



$ cat test.sql | perl -pe 'BEGIN{$/=";"} m/MERGE INTO .*/ && ($_ .=  "nAFFECTED_ROWS := AFFECTED_ROWS + SQL %ROWCOUNT;")'

SELECT non-matching-statement FROM table;



MERGE INTO employees e

    USING hr_records h

    ON (e.id = h.emp_id)

  WHEN MATCHED THEN

    UPDATE SET e.address = h.address

  WHEN NOT MATCHED THEN

    INSERT (id, address)

    VALUES (h.emp_id, h.address);

AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;



SELECT another-non-matching-statement FROM table;



end;



$

Using less perl shorthand, you might do this:

perl -e '$/=";"; foreach $statement (<>){ print $statement; $statement =~ m/(MERGE INTO .*)|(DELETE FROM)/i && print "nAFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;" }'

e.g.:

cat test.sql | perl -e '$/=";"; foreach $statement (<>){ print $statement; $statement =~ m/(MERGE INTO .*)|(DELETE FROM)/i && print "nAFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;" }'

SELECT non-matching-statement FROM table;



MERGE INTO employees e

    USING hr_records h

    ON (e.id = h.emp_id)

  WHEN MATCHED THEN

    UPDATE SET e.address = h.address

  WHEN NOT MATCHED THEN

    INSERT (id, address)

    VALUES (h.emp_id, h.address);

AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;



SELECT another-non-matching-statement FROM table;



DELETE FROM truncate_me;

AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;



end;

$