Insert Log file into oracle table

[mattjones]

hi,

Im having trouble loading a .log file into oracle. I can connect to the
database and i can insert data (that i define in an INSERT statement).
I eventually need to filter specific info from the .log files (there
are alot of them - and this script provides an automated process) but
for now i just want to read the whole script into a table!

CURRENT SCRIPT:
#!/usr/central/bin/perl -w

use strict;
use DBI;

my $dbh = DBI ->connect("dbi:Oracle:SERVER", "DATABASE", "PASSWORD")
or die "Couldn't connect to database: $DBI::errstr\n";


my $rows = $dbh->do("
INSERT INTO LOGS VALUES ('160820061-fst.log', '5', '0', 'FASTSEARCH',
'0', 'inversions', '0', 'conflicting') ");

$dbh->disconnect;

exit;
##### I have also tried the following and putting $LOG into one of the
values but I just get the value '1' returned!
my $LOG = open (LOG, "/home/USRNAME/PERL/hello.log");

my @data = <LOG>;
close LOG;

Any ideas?

Thanks

 

[addinall]

hi,

Im having trouble loading a .log file into oracle. I can connect to the
database and i can insert data (that i define in an INSERT statement).
I eventually need to filter specific info from the .log files (there
are alot of them - and this script provides an automated process) but
for now i just want to read the whole script into a table!

CURRENT SCRIPT:
#!/usr/central/bin/perl -w

use strict;
use DBI;

my $dbh = DBI ->connect("dbi:Oracle:SERVER", "DATABASE", "PASSWORD")
or die "Couldn't connect to database: $DBI::errstr\n";


my $rows = $dbh->do("
INSERT INTO LOGS VALUES ('160820061-fst.log', '5', '0', 'FASTSEARCH',
'0', 'inversions', '0', 'conflicting') ");

$dbh->disconnect;

exit;
##### I have also tried the following and putting $LOG into one of the
values but I just get the value '1' returned!
my $LOG = open (LOG, "/home/USRNAME/PERL/hello.log");

my @data = <LOG>;
close LOG;

Any ideas?

I'm assuming you have a CLOB field to put the data into?
If so, your second attempt is the one that is correct.
If ORACLE is returning a '1', that generally reads
"One row updated".

Fire up SQL+, or a PL/SQL session, or TOAD and do a

DESCRIBE TABLE_NAME ;

and let us know what is in the table. Then perhaps do a

SELECT COUNT(*) FROM TABLE_NAME ;

to see if any of your attempts have made it into the
database.

I assume that some of your entries are just going to contain strings
like:
'160820061-fst.log'

ORACLE isn't hard, be persitant, it will become clear!

Cheers,
Mark Addinall.

 

[mattjones]

I'm assuming you have a CLOB field to put the data into?
If so, your second attempt is the one that is correct.
If ORACLE is returning a '1', that generally reads
"One row updated".

Fire up SQL+, or a PL/SQL session, or TOAD and do a

DESCRIBE TABLE_NAME ;

and let us know what is in the table. Then perhaps do a

SELECT COUNT(*) FROM TABLE_NAME ;

to see if any of your attempts have made it into the
database.

I assume that some of your entries are just going to contain strings
like:
'160820061-fst.log'

ORACLE isn't hard, be persitant, it will become clear!

Cheers,
Mark Addinall.

Right, so I've created a CLOB field (didn't realise!) and my code is now:

#!/usr/central/bin/perl -w

use strict;
use DBI;

#!/usr/central/bin/perl -w

use strict;
use DBI;

my $dbh = DBI ->connect("dbi:Oracle:SERVER", "DATABASE", "PASSWORD")
or die "Couldn't connect to database: $DBI::errstr\n";

my $LOG = open (LOG, "/home/USERNAME/PERL/hello.log");

my @data = <LOG>;
close LOG;

my $rows = $dbh->do("
INSERT INTO LOGS VALUES ( 'wesdf', 'sdfg', 'dfg', 'fdg', 'dfg', 'dfg',
'dfg', 'dfg', $LOG) ");

$dbh->disconnect;

exit;
I want to get this working before I think about what i have to enter
into the fields. But for your info: i have to search through 1 log file
at a time and pull out specific information (which are not delimited!).
Some of the lines are repetitious so i can't just define a value to
pull out. I have written some code on this and it basically pulls out
the lines i want - I was hoping that TOAD would let me then, filter out
the info on display!

Matt

 

[Tad McClellan]

my $LOG = open (LOG, "/home/USRNAME/PERL/hello.log");

Any ideas?

You should always, yes _always_, check the return value from open():

open (LOG, "/home/USRNAME/PERL/hello.log") or
die "could not open '/home/USRNAME/PERL/hello.log' $!";

 

[Bob Walton]

Im having trouble loading a .log file into oracle. I can connect to the
database and i can insert data (that i define in an INSERT statement).
I eventually need to filter specific info from the .log files (there
are alot of them - and this script provides an automated process) but
for now i just want to read the whole script into a table!

CURRENT SCRIPT: ....
##### I have also tried the following and putting $LOG into one of the
values but I just get the value '1' returned!
my $LOG = open (LOG, "/home/USRNAME/PERL/hello.log");

my @data = <LOG>;
close LOG;

....

From perldoc -f open:

"Open returns nonzero upon success, the undefined value otherwise. If
the open involved a pipe, the return value happens to be the pid of the
subprocess."

Note that the value returned by open() is not something you want to
store in your database. The "1" you are complaining about is the
nonzero value open() returns saying it was successful in the operation
of opening the file. Yes, you DO want to check it to be sure your file
opened successfully. Maybe something like:

die "Oops, couldn't open log file" unless $LOG;

The data from your file will be in array @data -- but you probably will
want to parse it in some fashion before storing it in your database.

 

[Mumia W.]

#!/usr/central/bin/perl -w

use strict;
use DBI;

#!/usr/central/bin/perl -w

use strict;
use DBI;

my $dbh = DBI ->connect("dbi:Oracle:SERVER", "DATABASE", "PASSWORD")
or die "Couldn't connect to database: $DBI::errstr\n";

my $LOG = open (LOG, "/home/USERNAME/PERL/hello.log");

When open succeeds, it returns '1,' so $LOG contains '1.'

my @data = <LOG>;

If you want to put the entire log file into the database, you
should slurp it into a scalar like so:

close LOG;

my $rows = $dbh->do("
INSERT INTO LOGS VALUES ( 'wesdf', 'sdfg', 'dfg', 'fdg', 'dfg', 'dfg',
'dfg', 'dfg', $LOG) ");

Since $LOG contains the useless value '1,' you probably want
to put the actual log data in there:

'dfg', 'dfg', '$data') ");

There also might be a need for escaping the data in $data.

$dbh->disconnect;

exit;

I want to get this working before I think about what i have to enter
into the fields. But for your info: i have to search through 1 log file
at a time and pull out specific information (which are not delimited!).
Some of the lines are repetitious so i can't just define a value to
pull out. I have written some code on this and it basically pulls out
the lines i want - I was hoping that TOAD would let me then, filter out
the info on display!


Matt

I'm neither a Perl nor DB expert, but this might help you some:

use strict;
use warnings;
use DBI;

open (LOG, 'hello.log') or die("Open failure: $!\n");
my $data = do { local $/; <LOG> };
close (LOG);

# I don't have $Oracle$.
my $dsn = "DBI:mysql:database=test;host=localhost";
my $dbh = DBI->connect($dsn,$ENV{USER},'')
or die("Connection failure: $!\n");

my $qdata = quotemeta($data);
$dbh->do("insert into logfiles values (0, '2006-09-04',
'$qdata')")
or die("insertion failure: $!\n");

$dbh->disconnect;

__END__

Notice how I placed quotes around $qdata in the insertion. I
also quoted metacharacters to be sure they wouldn't mess up
the insertion.

 

[addinall]

#!/usr/central/bin/perl -w

use strict;
use DBI;

#!/usr/central/bin/perl -w

use strict;
use DBI;

my $dbh = DBI ->connect("dbi:Oracle:SERVER", "DATABASE", "PASSWORD")
or die "Couldn't connect to database: $DBI::errstr\n";

my $LOG = open (LOG, "/home/USERNAME/PERL/hello.log");

my @data = <LOG>;
close LOG;

At this stage $LOG SHOULD contain '1' if the OPEN opened.
You should test this.

my $rows = $dbh->do("
INSERT INTO LOGS VALUES ( 'wesdf', 'sdfg', 'dfg', 'fdg', 'dfg', 'dfg',
'dfg', 'dfg', $LOG) ");

You are sticking $LOG into the database and ignoring your data!
Don't worry, I've done that a few times ;-)

Cheers,
Mark Addinall.

 

[mattjones]

Right, I've managed to input the data so i can move on to the next bit
of the script.

I was thinking someone would have a go at me for posting such a
question! I've got the programming perl and the perl DBI books but
sometimes its good to have someone explain it for you...

Thanks for everyones help anyway.

Matt

 

[addinall]

Right, I've managed to input the data so i can move on to the next bit
of the script.

Goodo! Have fun!

I was thinking someone would have a go at me for posting such a
question! I've got the programming perl and the perl DBI books but
sometimes its good to have someone explain it for you...

Thanks for everyones help anyway.

No worries on my part.
Cheers,
Mark Addinall.