0

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

1

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;
$
  • perl $/ is the input record separator, not field. awk can do the same (portably) with -vRS=';' and bash can do read -d';' but that's nonstandard and less efficient. Note all such approaches fail if any of your statements has a char/string literal containing semicolon like SET recipe = 'mix this; stir that' – dave_thompson_085 Oct 25 '18 at 1:30
  • Good points, thanks dave_thompson. Embedded separators will be many grief, so issues - and handling them will require a lot more sophistication than can be encapsulated in a one-liner. Re field/separator, yeah, I had IFS on the brain from some other work, just typed it without thinking. Thanks for the catch, will edit. – GarethHumphriesAcc Oct 25 '18 at 2:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.