How to search for a line before a pattern using sed or grep

Question

I have a log file ouput.log and the contents on the log file is as below. The log file comprises of multiple .sql files output in a sequential manner. The log file will have which SQL file is being executed example --> @/opt/DBA/HIP/FILES/Environments/PRDO/347_WT_04NOV2020.sql It can have 'n' number of sql statements from where it can error out any where with the pattern ORA-. So whenever the command finds ORA-, I should get the file name for which has been errored out.

== Files executed in PRDO ==

== PRDO Execution spool ==
SQL> @/opt/DBA/HIP/FILES/Environments/PRDO/347_WT_04NOV2020.sql
SQL> ------UHIPOPS-36625---
SQL>
SQL> SET DEFINE OFF;
SQL>
SQL> ALTER SESSION SET CURRENT_SCHEMA = HIS_PD;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> INSERT INTO msg (MSG_ID,
  2                   LANG_CD,
  3                   SVR_CD,
  4                   MSG_DSC)
  5       VALUES (92012,
  6               'EN',
  7               1,
  8               'Please enter the EIN in the correct format: XX-XXXXXXX.');

1 row created.

Elapsed: 00:00:00.03
SQL>
SQL> INSERT INTO msg (MSG_ID,
  2                   LANG_CD,
  3                   SVR_CD,
  4                   MSG_DSC)
  5       VALUES (92012,
  6               'ES',
  7               1,
  8               'Ingrese el EIN en el formato correcto: XX-XXXXXXX.');

ORA-

Elapsed: 00:00:00.00
SQL>
SQL> INSERT INTO msg (MSG_ID,
  2                   LANG_CD,
  3                   SVR_CD,
  4                   MSG_DSC)
  5       VALUES (92012,
  6               'PT',
  7               1,
  8               'Introduza o EIN no formato correto: XX-XXXXXXX.');

1 row created.

Elapsed: 00:00:00.00
SQL>
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.00

SQL> @/opt/DBA/HIP/FILES/Environments/PRDO/348_WT_04NOV2020.sql
SQL> ------UHIPOPS-36625---
SQL>
SQL> SET DEFINE OFF;
SQL>
SQL> ALTER SESSION SET CURRENT_SCHEMA = HIS_PD;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> INSERT INTO msg (MSG_ID,
  2                   LANG_CD,
  3                   SVR_CD,
  4                   MSG_DSC)
  5       VALUES (92012,
  6               'EN',
  7               1,
  8               'Please enter the EIN in the correct format: XX-XXXXXXX.');

1 row created.

Elapsed: 00:00:00.03
SQL>
SQL> INSERT INTO msg (MSG_ID,
  2                   LANG_CD,
  3                   SVR_CD,
  4                   MSG_DSC)
  5       VALUES (92012,
  6               'ES',
  7               1,
  8               'Ingrese el EIN en el formato correcto: XX-XXXXXXX.');

1 row created.

Elapsed: 00:00:00.00
SQL>
SQL> INSERT INTO msg (MSG_ID,
  2                   LANG_CD,
  3                   SVR_CD,
  4                   MSG_DSC)
  5       VALUES (92012,
  6               'PT',
  7               1,
  8               'Introduza o EIN no formato correto: XX-XXXXXXX.');

ORA-

Elapsed: 00:00:00.00
SQL>
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.00

FOr example from the above log file I have executed two SQL files that has been captured and both has been errored with ORA-. Now is there a command or any way by which I can get the file names of the errored sqls? The output should be like below

347_WT_04NOV2020.sql
348_WT_04NOV2020.sql

All I know is basic grep "ORA-" output.log which will give me an output if ORA- is found in output.log

Answer 1

Using awk:

awk -F'/' '/^SQL> @/{ filename=$NF; next } /^ORA-/{ print filename; }' infile

Answer 2

Here's a sed version with xargs:

sed -n '/^SQL> @/{s/^SQL> @//;h};/^ORA-/{g;p;}' output.log | xargs -L1 basename

• -n suppresses default output, so only lines with an explicit print command will be displayed
• The first sed match takes just the path and filename and holds it (h) in a buffer
• The second sed match gets the contents of the buffer (g) and prints it (p)
• xargs sends each line of output to basename, which displays just the filename portion