Perl Guru Forums: Perl Programming Help: Intermediate: Special Extraction requirement...

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: Intermediate:
Special Extraction requirement...

phamou
Novice

Jun 13, 2008, 2:09 PM

Post #1 of 13 (1241 views)

Special Extraction requirement...	Can't Post

Hi,

I have a very special text extraction requirement, see the sample text below:

======================================================================
[15:39:36.873] dal.oracle.connection
Bindings:
:1 char: Config (6)
:2 char: Vend (4)

[15:39:36.877] dal.connection_impl
SQL: {00:00:00.004236 us} {SELECT SW_Key_Nm, Config_Value FROM SW_Config
c, SW_Key k, SW_Section s, SW_Object o WHERE c.SW_Object_Id=o.SW_Object_Id
AND c.SW_Section_Id=s.SW_Section_Id AND c.SW_Key_Id=k.SW_Key_Id AND
k.SW_Object_Id=o.SW_Object_Id AND k.SW_Section_Id=s.SW_Section_Id AND
s.SW_Object_Id=o.SW_Object_Id AND s.SW_Section_Nm LIKE :1 AND
o.SW_Object_Nm LIKE :2}

[15:39:36.885] dal.oracle.connection
Bindings:
:1 char: ConfigDC (8)
:2 char: Vend (4)

[15:39:36.887] dal.connection_impl
SQL: {00:00:00.002197 us} {SELECT SW_Key_Nm, Config_Value FROM SW_Config
c, SW_Key k, SW_Section s, SW_Object o WHERE c.SW_Object_Id=o.SW_Object_Id
AND c.SW_Section_Id=s.SW_Section_Id AND c.SW_Key_Id=k.SW_Key_Id AND
k.SW_Object_Id=o.SW_Object_Id AND k.SW_Section_Id=s.SW_Section_Id AND
s.SW_Object_Id=o.SW_Object_Id AND s.SW_Section_Nm LIKE :1 AND
o.SW_Object_Nm LIKE :2}

[15:39:36.942] dal.oracle.connection
Bindings:
:1 char: HT (2)

[15:39:36.945] dal.connection_impl
SQL: {00:00:00.054701 us} {SELECT Device_Id, Device_Nm FROM Device WHERE
Device_Type_Cd=:1}

[15:39:36.947] dal.oracle.connection
Bindings:
:1 long: 2 (4)
:2 long: 1 (4)

[15:39:36.949] dal.connection_impl
SQL: {00:00:00.001932 us} {SELECT TL_Comm_IP, TL_Comm_Port,
TL_Comm_Timeout, Connectivity_Status_Cd FROM Connectivity WHERE
Host_Id=:1 AND Route_Seq_No=:2}

[15:39:36.952] dal.connection_impl
SQL: {00:00:00.001662 us} {SELECT COUNT(*) AS Total FROM
NCR_Data_Collection}

[15:39:36.977] dal.oracle.connection
Bindings:
:1 char: Encryption (10)
:2 char: Vend (4)
======================================================================

I need to extract all of the SQL statements, they all start with the following regular expression:

SQL: \{[0-9]*\:[0-9]*\:[0-9]*\.[0-9]* [a-zA-Z0-9]*\} \{

but can spawn for a different number of lines until the next "}"

I would like to extract those SQL statements and push them into a different file.

any help would be greatly appreciated !!

best regards,

Patrick Hamou
www.renaps.com

(This post was edited by phamou on Jun 13, 2008, 2:10 PM)

meloyelo
User

Jun 13, 2008, 11:30 PM

Post #2 of 13 (1221 views)

Re: [phamou] Special Extraction requirement... [In reply to]	Can't Post

Slurp the entire file into a variable. Then you can write a regular expression that spans more than one line:

Code

my $log = ... # contents of the SQL log file 
 
while ($log =~ m/\{(.*?)\}\s*\{(.*)\}/sg) { 
  print "execution time: $1\n"; 
  print "sql statement: $2\n"; 
}

Note the /s on the regular expression.

(This post was edited by meloyelo on Jun 13, 2008, 11:31 PM)

phamou
Novice

Jun 17, 2008, 7:04 AM

Post #3 of 13 (1109 views)

Re: [meloyelo] Special Extraction requirement... [In reply to]	Can't Post

my $log = ... # contents of the SQL log file

How can you load the content of the entire file into one perl variable ?

Thanks,

Patrick

phamou
Novice

Jun 17, 2008, 8:07 AM

Post #4 of 13 (1101 views)

Re: [meloyelo] Special Extraction requirement... [In reply to]	Can't Post

I figured how to slurp the content of the file in one perl variable, Here is my code:

#!/usr/local/bin/perl

my $FilePath='file1.txt';

open(FileHandle, $FilePath) or die "Can't open $FilePath: $!";
##########@lines = <FileHandle>;
select((select(FileHandle), $/ = undef) [0]);
my $content = <FileHandle>;
close (FileHandle);

while ($content =~ m/\{(.*?)\}\s*\{(.*)\}/sg)
{
print "execution time: $1\n";
print "sql statement: $2\n";
}

#################print $content;
#################print @lines;

exit;

Although, the WHILE loop doesn't work as expected.. only the first SQL Statement is returned, the rest of the file is returned as-is, here is a portion of the output

========================================================================================================================================
execution time: 00:00:00.004236 us
sql statement: SELECT SW_Key_Nm, Config_Value FROM SW_Config
c, SW_Key k, SW_Section s, SW_Object o WHERE c.SW_Object_Id=o.SW_Object_Id
AND c.SW_Section_Id=s.SW_Section_Id AND c.SW_Key_Id=k.SW_Key_Id AND
k.SW_Object_Id=o.SW_Object_Id AND k.SW_Section_Id=s.SW_Section_Id AND
s.SW_Object_Id=o.SW_Object_Id AND s.SW_Section_Nm LIKE :1 AND
o.SW_Object_Nm LIKE :2}

[15:39:36.885] dal.oracle.connection
Bindings:
:1 char: ConfigDC (8)
:2 char: Vend (4)

[15:39:36.887] dal.connection_impl
SQL: {00:00:00.002197 us} {SELECT SW_Key_Nm, Config_Value FROM SW_Config
c, SW_Key k, SW_Section s, SW_Object o WHERE c.SW_Object_Id=o.SW_Object_Id
AND c.SW_Section_Id=s.SW_Section_Id AND c.SW_Key_Id=k.SW_Key_Id AND
k.SW_Object_Id=o.SW_Object_Id AND k.SW_Section_Id=s.SW_Section_Id AND
s.SW_Object_Id=o.SW_Object_Id AND s.SW_Section_Nm LIKE :1 AND
o.SW_Object_Nm LIKE :2}

[15:39:36.942] dal.oracle.connection
Bindings:
:1 char: HT (2)

[15:39:36.945] dal.connection_impl
SQL: {00:00:00.054701 us} {SELECT Device_Id, Device_Nm FROM Device WHERE
Device_Type_Cd=:1}

[15:39:36.947] dal.oracle.connection
Bindings:
:1 long: 2 (4)
:2 long: 1 (4)

[15:39:36.949] dal.connection_impl
SQL: {00:00:00.001932 us} {SELECT TL_Comm_IP, TL_Comm_Port,
TL_Comm_Timeout, Connectivity_Status_Cd FROM Connectivity WHERE
Host_Id=:1 AND Route_Seq_No=:2}

[15:39:36.952] dal.connection_impl
SQL: {00:00:00.001662 us} {SELECT COUNT(*) AS Total FROM
NCR_Data_Collection}

[15:39:36.977] dal.oracle.connection
Bindings:
:1 char: Encryption (10)
:2 char: Vend (4)

[15:39:36.979] dal.connection_impl
SQL: {00:00:00.001861 us} {SELECT SW_Key_Nm, Config_Value FROM SW_Config
c, SW_Key k, SW_Section s, SW_Object o WHERE c.SW_Object_Id=o.SW_Object_Id
AND c.SW_Section_Id=s.SW_Section_Id AND c.SW_Key_Id=k.SW_Key_Id AND
k.SW_Object_Id=o.SW_Object_Id AND k.SW_Section_Id=s.SW_Section_Id AND
s.SW_Object_Id=o.SW_Object_Id AND s.SW_Section_Nm LIKE :1 AND
o.SW_Object_Nm LIKE :2}

========================================================================================================================================

Please note that in the ORIGINAL text, there are spaces between the beggining of the line and "SQL:".. (if that helps..)

Thanks alot for your help,

Patrick

meloyelo
User

Jun 17, 2008, 8:08 AM

Post #5 of 13 (1100 views)

Re: [phamou] Special Extraction requirement... [In reply to]	Can't Post

Several ways:

1. The module way -- see File::Slurp
2. By setting $/ to undef:

open(my $fh, "<", "path-to-file") or die "open error: $!"
my $contents = do { local $/; <$fh> };
close($fh);

phamou
Novice

Jun 17, 2008, 8:20 AM

Post #6 of 13 (1099 views)

Re: [meloyelo] Special Extraction requirement... [In reply to]	Can't Post

Thanks meloyelo,

I think our post have just crossed !

but please read my last post, I have a different question now...

Patrick

meloyelo
User

Jun 17, 2008, 8:32 AM

Post #7 of 13 (1097 views)

Re: [phamou] Special Extraction requirement... [In reply to]	Can't Post

Use:

Code

while ($content =~ m/\{(.*?)\}\s*\{(.*?)\}/sg) 
 ...

Note the second question mark in .*?

phamou
Novice

Jun 17, 2008, 8:48 AM

Post #8 of 13 (1094 views)

Re: [meloyelo] Special Extraction requirement... [In reply to]	Can't Post

Thank you, it worked perfectly this time.

Patrick

phamou
Novice

Jun 17, 2008, 10:34 AM

Post #9 of 13 (1088 views)

Re: [phamou] Special Extraction requirement... [In reply to]	Can't Post

I added the red line of code, and I get the following error:

[root@REPL3x-RH4-PS1 patrick]# perl test.pl
Modification of a read-only value attempted at test.pl line 12.

================================================

[root@REPL3x-RH4-PS1 patrick]# cat test.pl
#!/usr/local/bin/perl

my $FilePath='file1.txt';

open(FileHandle, $FilePath) or die "Can't open $FilePath: $!";
select((select(FileHandle), $/ = undef) [0]);
my $content = <FileHandle>;
close (FileHandle);

while ($content =~ m/\{(.*?)\}\s*\{(.*?)\}/sg)
{
$2 =~ s/select/explain plan for select/i;
print "\n$2\n\n\n";
}

exit;

phamou
Novice

Jun 17, 2008, 11:25 AM

Post #10 of 13 (1079 views)

Re: [phamou] Special Extraction requirement... [In reply to]	Can't Post

Please disregard,

I fixed the error by working with a new variable...

Patrick

[root@REPL3x-RH4-PS1 patrick]# cat test.pl
#!/usr/local/bin/perl

my $FilePath='file1.txt';
my $printedvalue='';

open(FileHandle, $FilePath) or die "Can't open $FilePath: $!";
select((select(FileHandle), $/ = undef) [0]);
my $content = <FileHandle>;
close (FileHandle);

while ($content =~ m/\{(.*?)\}\s*\{(.*?)\}/sg)
{
print "\nDELETE plan_table;\nCOMMIT;\n\n";

$printedvalue=$2;
$printedvalue =~ s/SELECT/EXPLAIN PLAN FOR SELECT/i;
$printedvalue =~ s/UPDATE/EXPLAIN PLAN FOR UPDATE/i;
$printedvalue =~ s/INSERT/EXPLAIN PLAN FOR INSERT/i;
$printedvalue =~ s/DELETE/EXPLAIN PLAN FOR DELETE/i;
$printedvalue = "$printedvalue\n\/";
print "\n$printedvalue\n\n\n";

print "\@\@select_plan.sql\n\n\n";

}

exit;

phamou
Novice

Jun 20, 2008, 9:09 AM

Post #11 of 13 (1016 views)

Re: [phamou] Special Extraction requirement... [In reply to]	Can't Post

I have a small problem to resolve.. (maybe not that easy though)

Some of the SELECT statements are ending with the keywords "FOR UPDATE".

my procedure is replacing all of the UPDATE keywords with "EXPLAIN PLAN FOR UPDATE",

BUT I need to ignore the "FOR UPDATE" statements in my search and replace command...

is this possible ? - how would you do it ?

Thanks alot,

Patrick

here is an example statement

SELECT 1 FROM DUAL FOR UPDATE;

Here is the code used for the search and replace :

$printedvalue =~ s/SELECT/EXPLAIN PLAN FOR SELECT/i;
$printedvalue =~ s/UPDATE/EXPLAIN PLAN FOR UPDATE/i;

phamou
Novice

Jun 20, 2008, 9:32 AM

Post #12 of 13 (1013 views)

Re: [phamou] Special Extraction requirement... [In reply to]	Can't Post

I have another idea, but don't know how to code it...

if we search and replace only lines BEGGINING with the word SELECT or UPDATE -- the problem would be resolved..

please advise..

thanks,

Patrick

phamou
Novice

Jun 20, 2008, 9:51 AM

Post #13 of 13 (1010 views)

Re: [phamou] Special Extraction requirement... [In reply to]	Can't Post

I found my answer:

$printedvalue =~ s/^SELECT/EXPLAIN PLAN FOR SELECT/i;
$printedvalue =~ s/^UPDATE/EXPLAIN PLAN FOR UPDATE/i;

Thanks,

Patrick

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives