By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,691 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,691 IT Pros & Developers. It's quick & easy.

Writing PERL script to query oracle data and write the output to Excel

P: 1
Hello Users,

I am new to PERL. There is a requirement to write a PERL script to query the Oracle data based on the user input date parameter and write the output to Excel.

I have the following query below

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT NODE, port, floor(mtr/86400) || 'd'||to_char(to_date(mode(mtr,86400,'sssss'),' hh24"h" mi"m" ss"s" ') DOWNTIME FROM INTERFACE_DOWNTIME WHERE begindate >= to_date(&param1,'DD/MM/YY HH:MI') + (8/24) and enddate <= to_date(&param2,'DD/MM/YY HH:MI') + (8/24)
  3.  
  4.  
The above query will fetch the records from the table that matches the date condition user input parameter from Begindate 8AM to Enddate 8AM.

The output of mtr in seconds will be displayed as days,hours,min,sec for example (2d 3H 40M 05S ).

From the forum I was able to get some inputs like connecting to Oracle Db. But I am not sure how to pass the user input parameter to my sql query.

Secondly how to write the query output to Excel.

Expand|Select|Wrap|Line Numbers
  1. use strict;
  2. use DBI;
  3. my $dbh = DBI->connect( 'dbi:Oracle:orcl',
  4.                         'reporter',
  5.                         'reporter',
  6.                       ) || die "Database connection not made: $DBI::errstr";
  7.  
Do I need to set any variables above.I don't know how to proceed.

Can someone please help me on this script.

Thanks.
Regards,
RaviShankar.
Aug 12 '10 #1
Share this Question
Share on Google+
1 Reply


numberwhun
Expert Mod 2.5K+
P: 3,503
First, DBI is the standard interface for connecting to databases in Perl. You should really check out a tutorial on it.
Second, to write to an excel spreadsheet, you may want to look into the Spreadsheet::WriteExcel module.

Please know that for both of these items you could easily do Google searches such as "write to excel perl" or "connect to mysql perl dbi" and the top results (which are what I provided here) gave you the answers that you need. Please be doubly sure that you have fully searched Google for the answers before asking here as that is usually one of our first courses of action.
Next, when you want to do something in Perl, there is usually a module to help. In case you were not pointed their before, you will want to search CPAN as well (which is the Perl module repository).

Regards,

Jeff
Aug 14 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.