Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Finding The Complete SQL statement Using PDFGREP Or Grep

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Finding The Complete SQL statement Using PDFGREP Or Grep
# 1  
Old 02-02-2020
Finding The Complete SQL statement Using PDFGREP Or Grep
Linux Gods,

I am simply attempting to parse SQL statements from a PDF doc in creating a base SQL script at a later time but for the life of me, am having a tough time extracting this data.This exact string worked perfectly a couple of months ago and now it doesnt. Below is an example of the data structure.

Code:
show parameter os_authent_prefix

SHOW PARAMETER log_archive_dest;

Audit:

  SELECT AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE

  FROM AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
  WHERE AUD.POLICY_NAME = ENABLED.POLICY_NAME
  AND AUD.AUDIT_OPTION = 'CREATE TRIGGER'
  AND AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
  AND ENABLED.SUCCESS = 'YES'
  AND ENABLED.FAILURE = 'YES'
  AND ENABLED.ENABLED_OPT = 'BY'
  AND ENABLED.USER_NAME = 'ALL USERS';

Other variations I have tried:
Code:
pdfgrep -i -PB 20 -A 20 "audit\:" ./Oracle-12.pdf | gawk '{IGNORECASE=1;} /show.*\;/ || /select.*\;/ {print "Here is the data \n\n",$0, "\n"}'

gawk: cmd. line:1: warning: regexp escape sequence `\;' is not a known regexp operator
Here is the data 

   REVOKE SELECT_ANY_DICTIONARY FROM <grantee>; 

Here is the data 

    REVOKE SELECT ANY TABLE FROM <grantee>; 

Here is the data 

   REVOKE SELECT_CATALOG_ROLE FROM <grantee>; 

Here is the data 

   AUDIT SELECT ANY DICTIONARY;

I suspect something changed in a binary or two. In attempting to get past this, I have attempted various regex variations:
Code:
pdfgrep -i -PB 20 -A 20 "audit\:" ./Oracle-12.pdf | gawk '{IGNORECASE=1;} /show.*|;/ || /select.*|;/ {print "Here is the data \n\n",$0, "\n"}'

pdftotext ./Oracle-12.pdf - | grep -i "select.*\; | show.*\;"

gawk '{IGNORECASE=1;} /show.*|;/ || /select.*|;/ {print "The Goodies \n\n",$0, "\n"}' ./Oracle-12.pdf.txt

Can someone shed some light? I am using distro Kali 2020.1 which I upgrade from 2019.4 and now the original string doesnt work. Thanks
Last edited by rbatte1; 02-07-2020 at 07:43 AM..
# 2  
Old 02-02-2020
Can you specify what does not work? The message presented is just a warning caused by the unnecessary backslash before the semicolon, but it should not change the outcome.
# 3  
Old 02-02-2020
Thank you for your response. When I run the one-liner, its doesnt apply the regex and produce the entire SQL statement like:
Code:
SELECT AUD.POLICY_NAME, AUD.AUDIT_OPTION, AUD.AUDIT_OPTION_TYPE
  FROM AUDIT_UNIFIED_POLICIES AUD, AUDIT_UNIFIED_ENABLED_POLICIES ENABLED
  WHERE AUD.POLICY_NAME = ENABLED.POLICY_NAME
  AND AUD.AUDIT_OPTION = 'CREATE TRIGGER'
  AND AUD.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
  AND ENABLED.SUCCESS = 'YES'
  AND ENABLED.FAILURE = 'YES'
  AND ENABLED.ENABLED_OPT = 'BY'
  AND ENABLED.USER_NAME = 'ALL USERS';

Its not honoring the regex:
Code:
select.*\;

So when it was working, it would simply find each line that had the SQL statement as in the above sample,inspect and include each newline until it reached the semicolon. The SQL statements in the PDF doc are not in a single line but broken down into multiple newlines, ending with a semicolon.

Moderator's Comments:
Mod Comment Please do wrap your samples/codes in CODE TAGS as per forum rules.
Last edited by RavinderSingh13; 02-02-2020 at 01:01 PM..
# 4  
Old 02-03-2020
I tried all the tricks under the sun. No matter what I do, I cant get any of these combinations to work:
Code:
pdfgrep -i -PB 20 -A 20 "audit\:" ./Oracle-12.pdf | gawk '{IGNORECASE=1;} /select/,/\;/ {print $0}'
gawk '{IGNORECASE=1;} /select.*/,/\;/ {print $0}' ./Oracle-12.txt
gawk '{IGNORECASE=1} BEGIN { RS = ";" } /FROM/ { gsub("\n+|  +", " ");  print }' ./Oracle-12.txt
gawk '{IGNORECASE=1;} /select.*|;/ {f=1} f; !/\\$/{f=0}' ./Oracle-12.txt
gawk '{IGNORECASE=1;} BEGIN {p=0}; /select.*/ { p = ! p ; next } ; p  {print $0}' ./Oracle-12.txt

Could it be whitespace in the conversion from pdfgrep to a text?
# 5  
Old 02-03-2020
Code:
gawk -v IGNORECASE=1 '....'

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Using df -g command with awk to get SQL statement

Hi Gurus... good day; currently I trying to run the df -g command with awk to get to convert in SQL statement, but I have some errors; df -g | awk '{print "This is the FileSystem: " $NF, " This is LV: "$1, "This is SIZE: "$2, "This is FREE: " $3, "This is the USED% "$4}' This on AIX... (3 Replies)
Discussion started by: wcastibl
3 Replies

2. Shell Programming and Scripting

Grep command is not search the complete pattern

I am facing a problem while using the grep command in shell script. Actually I have one file (PCF_STARHUB_20130625_1) which contain below records. SH_5.55916.00.00.100029_20130601_0001_NUC.csv.gz|438|3556691115 SH_5.55916.00.00.100029_20130601_0001_Summary.csv.gz|275|3919504621 ... (2 Replies)
Discussion started by: sumit.vedi1988
2 Replies

3. Shell Programming and Scripting

Need help for finding and killing sql process

hi, iam completely new to scripting. this may sound naive but i have spend lot of time figuring this out. i want to make a script to find number of sql processes running. If the number of processes are more then 200, then pick out process IDs along with query it is executing, which are running... (0 Replies)
Discussion started by: Prateek Suhag
0 Replies

4. Shell Programming and Scripting

UNIX variable to SQL statement

The following is my script : #!/bin/bash echo "please give app_instance_id" read app_instance_id echo "id is $app_instance_id" export app_id=app_instance_id sqlplus -s nnviewer/lookup@//nasolora008.enterprisenet.org:1521/LOAD3 @test.sql<<EOF SPOOL /home/tibco/MCH/Data/qa/raak/name.xls... (4 Replies)
Discussion started by: raakeshr
4 Replies

5. Shell Programming and Scripting

issues with sql inside if statement

Hi, I have problem with the following code. My IF block is not executed. And I see "syntax error near unexpected token `)'" error for line "EOF" in the stats_function(). but when I comment the IF block I don't see this error. Kindly help me with this issue. clean_function() {... (10 Replies)
Discussion started by: babom
10 Replies

6. Shell Programming and Scripting

Script to batch pdfjoin based on pdfgrep output

I have a situation in which I'm given a bunch of pdf files which are all single pages with employee ID's on an independent line. I need to collate all of the pages by employee ID. Piecemeal, I can find a particular employee ID by just using pdfgrep. I could also do something like this: find .... (3 Replies)
Discussion started by: nopposan
3 Replies

7. Shell Programming and Scripting

Read SQL statement in Script

Hi Guys.. need some urgent help... I am stuck in something badly I need to write a script which would read a sql statement (which might be a join/inner join/select/sub select etc. ) I need to read that sql statement ... and in the output I want all the table names and columns (doesn't... (4 Replies)
Discussion started by: freakygs
4 Replies

8. UNIX for Dummies Questions & Answers

Can grep command return word instead of complete line

Hi Is there any way GREP command can return word and not complete line. My file has following data: Hello Everyone I am NitinrajSrivastava Hi Friends Welcome VrajSrivastava I am using grep 'raj' which is returning me complete line.However I want only the word having keyword 'raj'. Required... (11 Replies)
Discussion started by: dashing201
11 Replies

9. Shell Programming and Scripting

Executing sql statement from .sh file

Hi, How to execute sql statements from the .sh file ?? Means, when we run .sh file then the sql statements within it should be get executed one by one from the sqlplus With Regards (3 Replies)
Discussion started by: milink
3 Replies

10. Shell Programming and Scripting

Rerun sql statement

Hi, Script that I wrote only run sql query once then exit. But my requirement, I want the query can be execute a couple of time without exiting the script. How could I do it? Thanks. (1 Reply)
Discussion started by: killboy
1 Replies
Login or Register to Ask a Question