0

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

3
  • Please edit your question and show us the output you want to see from this input. Also explain how we can recognize a file name in your output, how we can recognize an error, and whether all files will contain at least one error. – terdon Nov 11 '20 at 17:49
  • Edited in detail – sabarish jackson Nov 11 '20 at 18:38
  • But how can we know the file name? Is a @ followed by a / enough? Can there be cases where you have @/foo and foo is not a file name? And what defines an error? Is it a line with only ORA-? Or can ORA- appear as part of a line that also has other data? – terdon Nov 11 '20 at 18:42
2

Using awk:

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

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

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.