CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

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

 



phamou
Novice

Jun 13, 2008, 2:09 PM

Post #1 of 13 (1547 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 (1527 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 (1415 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 (1407 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 (1406 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 (1405 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 (1403 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 (1400 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 (1394 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 (1385 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 (1322 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 (1319 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 (1316 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) Powered by Gossamer Forum v.1.2.0

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