0

Example:

$cat data.txt

SCN           THREAD  OPERATION ID                                       OBJECT ID   OBJECT NAME                     REPLICATE  OPERATION       SQL
------------- ------- -------------------------------------------------- ----------- ------------------------------- ---------- --------------- -------------------------------------------------
959936598838  1       0x00df.80ae1336.0000:0001.001087a6.00074e62.0010   0           N/A                             NO         START           set transaction read write;
959936598838  1       0x00df.80ae1336.0000:0001.001087a6.00074e62.0010   8144610     HEARTBEAT                       YES        INSERT          insert into "SRS"."HEARTBEAT"("INST_NAME","CR_DATE") values ('LIVE LIVE-22.',TO_DATE('03/15/2019 23:00:05', 'MM/DD/YYYY HH24:MI:SS'));
959936598839  1       0x00df.80ae1337.0000:0001.001087a6.00074e63.0174   0           N/A                             NO         COMMIT          commit;

From the above file, is it possible to extract the insert query including values using shell scripting?

i need desired output like below.

insert into "SRS"."HEARTBEAT"("INST_NAME","CR_DATE") values ('LIVE LIVE-22.',TO_DATE('03/15/2019 23:00:05', 'MM/DD/YYYY HH24:MI:SS'));

  • Did you add the ** around the insert query, or does your data show up like that? – Jeff Schaller Jun 6 at 10:16
  • Please also edit your question to show your desired output. – Jeff Schaller Jun 6 at 10:17
  • No. data not contain any ** before and afterthe query. – Raj s Jun 6 at 10:44
0

Assuming no space in the first seven columns.

$ while read a b c d e f g query; do echo "${query}"; done < data.txt | grep -Ei "insert|update"
insert into "SRS"."HEARTBEAT"("INST_NAME","CR_DATE") values ('LIVE LIVE-22.',TO_DATE('03/15/2019 23:00:05', 'MM/DD/YYYY HH24:MI:SS'));


$ awk '/insert|update/{for(i=1;i<=7;i++){$i=""}print}' data.txt
       insert into "SRS"."HEARTBEAT"("INST_NAME","CR_DATE") values ('LIVE LIVE-22.',TO_DATE('03/15/2019 23:00:05', 'MM/DD/YYYY HH24:MI:SS'));
  • Hi,above is just an example.the file contains hundreds of such insert and update queries.i want to extract all of them – Raj s Jun 6 at 12:46
  • did you execute the given two commands ? whats the problem with given commands ? – Kamaraj Jun 7 at 9:49
  • Hi Sir,The query is working fine and im getting desired output.thank you.. – Raj s Jun 7 at 10:31

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.