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

1 Answer 1

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;
$
2
  • 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' Oct 25, 2018 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. Oct 25, 2018 at 2:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .