1 / 24

IOUW 2000 Managing Oracle and Other Cool Things You Can Do with PERL John D. Groenveld

IOUW 2000 Managing Oracle and Other Cool Things You Can Do with PERL John D. Groenveld Manufacturing Systems Division Applied Research Laboratory - Penn State University. Introduction 0001 #!/usr/bin/perl -w 0002 0003 use strict; 0004 0005 print &quot;Hello World<br>&quot;;

dara
Download Presentation

IOUW 2000 Managing Oracle and Other Cool Things You Can Do with PERL John D. Groenveld

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IOUW 2000 Managing Oracle and Other Cool Things You Can Do with PERL John D. Groenveld Manufacturing Systems Division Applied Research Laboratory - Penn State University

  2. Introduction • 0001 #!/usr/bin/perl -w • 0002 • 0003 use strict; • 0004 • 0005 print "Hello World\n"; • Using the Perl Programming Language to… • Manage your database • Develop applications • Client/Server • Web-based

  3. Perl • Larry Wall • Practical Extraction and Report Language • Not just for UNIX weenies • Modular, Object-Oriented styles • CPAN archive of reusable code • <URL:http://www.cpan.org/> • CGI, libwww, Net::*, • ActiveState PPM for Windows • <URL:http://www.activestate.com/> • Perl’s Plain Old Documentation, perldoc

  4. Perl (example) 0001 #!/usr/bin/perl -w 0002 0003 use strict; 0004 use File::Copy; 0005 use FileHandle; 0006 use Date::Format; 0007 use Net::SMTP; 0008 0009 my $oracle_home = "/opt/oracle8/product/8.0.6"; 0010 my $oracle_sid = "IOUW8"; 0011 my $smtp_server = "localhost"; 0012 0013 my $alert_log_dir = $oracle_home . "/rdbms/log"; 0014 my $alert_log = $alert_log_dir . "/alert_" . $oracle_sid . ".log"; 0015 my $date = time2str("%Y%m%d", time()); 0016 my $backup_log = $alert_log ."." . $date; 0017 die "can't find " . $alert_log unless -f $alert_log; 0018 0019 # move the alert log 0020 move($alert_log, $backup_log) or die $!; 0021

  5. Perl (example continued) • 0022 # parse the log file for Oracle errors • 0023 my $fh = new FileHandle $backup_log, "r"; • 0024 die $! unless defined $fh; • 0025 my @errors = grep /^ORA/, <$fh>; • 0026 $fh->close; • 0027 • 0028 # notify the DBA • 0029 if (@errors) { • 0030 my $smtp = new Net::SMTP($smtp_server); • 0031 $smtp->mail('oracle'); • 0032 $smtp->to('oracle'); • 0033 $smtp->data; • 0034 $smtp->datasend(qq{To: "Oracle DBA" <oracle>\n}); • 0035 $smtp->datasend(qq{From: "Oracle DBA rotate script" <oracle>\n}); • 0036 $smtp->datasend(qq{Subject: "errors in $backup_log\n}); • 0037 $smtp->datasend("\n"); • 0038 $smtp->datasend(@errors); • 0039 $smtp->dataend; • 0040 }

  6. DBI / DBD::Oracle • Perl4 extensions (oraperl, ingperl, sybperl, etc) • Tim Bunce • Database Independent Interface to DB Dependent Drivers • Pro*C / ODBC style interface • DBD::(ODBC,DB2,Informix, mysql, Xbase,CSV) • DBD::Ram • DBD::AnyDB • PerlDB <URL:http://perldb.sourceforge.net/> • <URL:http://www.symbolstone.org/technology/perl/DBI/> • O’Reilly Programming the Perl DBI

  7. DBI / DBD::Oracle (example) 0001 #!/usr/bin/perl -w 0002 0003 use strict; 0004 use DBI; 0005 0006 my $dbh = DBI->connect('dbi:Oracle:IOUW8', 0007 'scott', 0008 'tiger', 0009 { AutoCommit => 0, RaiseError => 1 } 0010 ); 0011 0012 my $sql = qq{ 0013 SELECT * 0014 FROM emp 0015 WHERE job = :p1 0016 }; 0017 my $sth = $dbh->prepare($sql); 0018 $sth->bind_param(':p1', 'CLERK'); 0019 $sth->execute();

  8. DBI / DBD::Oracle (example continued) 0020 my @row; 0021 my $col; 0022 while ( @row = $sth->fetchrow_array ) { 0023 foreach $col (@row) { 0024 print defined($col) ? $col : ""; 0025 print "\t"; 0026 } 0027 print "\n"; 0028 } 0029 $sth->finish; 0030 $dbh->disconnect;

  9. Perl / Tk • Tcl/Tk <URL:http://www.scriptics.com/> • Cross-platform GUI language • Nick Ing-Simmons • O’Reilly Learning Perl/Tk

  10. Perl / Tk (example) 0001 #!/usr/bin/perl -w 0002 0003 use strict; 0004 use DBI; 0005 use Tk; 0006 0007 my ($username, $password); 0008 0009 # Create a Tk window to Login (lw) 0010 my $lw = new MainWindow; 0011 $lw->title(" Login "); 0012 my $frame = $lw->Frame->pack; 0013 my $ul = $frame->Label( -text => "Username",); 0014 my $ue = $frame->Entry( -textvariable => \$username,); 0015 my $pl = $frame->Label( -text => "Password",); 0016 my $pe = $frame->Entry( -textvariable => \$password, -show => "*",); 0017 Tk::grid($ul, -row => 0, -col => 0); 0018 Tk::grid($ue, -row => 0, -col => 1); 0019 Tk::grid($pl, -row => 1, -col => 0); 0020 Tk::grid($pe, -row => 1, -col => 1);

  11. Perl / Tk (example continued .1) 0021 $lw->Button( 0022 -text => "Connect", 0023 -command => [\&fetch_login], 0024 )->pack(-side => 'bottom'); 0025 MainLoop; 0026 0027 sub fetch_login { 0028 # before I destroy, I should probably test the user/pass 0029 # As my CS prof used to say, that's left as an exercise 0030 $lw->destroy if ( defined $username && defined $password ); 0031 } 0032 0033 # fetch something 0034 my $dbh = DBI->connect('dbi:Oracle:IOUW8', 0035 $username, 0036 $password, 0037 { AutoCommit => 0, RaiseError => 1 } 0038 );

  12. Perl / Tk (example continued .2) 0039 my $sth = $dbh->prepare("SELECT table_name FROM user_tables"); 0040 $sth->execute; 0041 my $row_ref; 0042 my @list; 0043 while ( $row_ref = $sth->fetchrow_hashref ) { 0044 push @list, $row_ref->{TABLE_NAME}; 0045 } 0046 $sth->finish; 0047 $dbh->disconnect; 0048 0049 # Create a Tk window with a Listbox 0050 my $mw = MainWindow->new; 0051 $mw->title( "Hello World" ); 0052 my $lb = $mw->Scrolled("Listbox")->pack; 0053 $lb->insert("end", @list); 0054 $mw->Button( 0055 -text => "Done", 0056 -command => sub{ exit }, 0057 )->pack(-side => 'bottom'); 0058 MainLoop;

  13. Orac / PerlDBAdmin • Andy Duncan • Enterprise Manager • Extensible Framework • Other related projects • Karma • <URL:http://www.panix.com/~shull/karma/> • Web-based DB • OracleTool • Web-based DBA tool • <URL:http://www.oracletool.com/> • E/R Tool? • <URL:http://www.kkitts.com/orac-dba/>

  14. Orac / PerlDBAdmin (demo)

  15. Apache • open source web server • module API • Jakarta / Tomcat JSP, Java Servlets • XML • <URL:http://www.apache.org/>

  16. mod_perl • Doug MacEachern • Apache::DBI (persistant connections) • Apache::DBILogin (Oracle Authentication) • Apache::Session (Session Management) • Apache::OWA (OAS, PL/SQL cartridge) • <URL:http://perl.apache.org/> • O’Reilly Writing Apache Modules with Perl

  17. HTML::Mason • Jonathan Swartz • Reusable Components • Component Cache • Templates • Web Developer friendly • Content Management System • Staging area • Revision control • <URL:http://www.masonhq.com/>

  18. HTML::Mason (example Header) 0001 <!-- begin Header --> 0002 <HTML> 0003 <HEAD> 0004 <TITLE><% $title %></TITLE> 0005 </HEAD> 0006 <BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000EE" VLINK="#551A8B" ALINK="#FF0000"> 0007 <H1><% $title %></H1> 0008 <HR> 0009 <!-- end Header --> 0010 0011 <%ARGS> 0012 $title 0013 </%ARGS>

  19. HTML::Mason (example search_form.html) 0001 <& Header, title=>"IOUW2000 Example: Search Staff Expertise Database" &> 0002 0003 <FORM ACTION="search.html" METHOD="GET"> 0004 <TABLE> 0005 <TR> 0006 <TD> 0007 <SELECT NAME="Expertise" SIZE=5 MULTIPLE=1> 0008 %foreach my $row_ref (@{$expertise_table_ref}) { 0009 <OPTION VALUE="<% $row_ref->[0] %>"><% $row_ref->[1] %></OPTION> 0010 %} 0011 </SELECT> 0012 </TD> 0013 <TD> 0014 <INPUT TYPE="SUBMIT" VALUE="Submit"> 0015 </TD> 0016 </TR> 0017 </TABLE> 0018 </FORM> 0019 0020 <& Footer &> 0021

  20. HTML::Mason (example search_form.html continued .1) 0022 <%INIT> 0023 my $data_source = $r->dir_config('IOUW_data_source'); 0024 my $username = $r->dir_config('IOUW_username'); 0025 my $password = $r->dir_config('IOUW_password'); 0026 0027 my $dbh = DBI->connect($data_source, $username, $password, {RaiseError => 1}); 0028 0029 my $sql = qq{ 0030 SELECT id, description 0031 FROM iouw2000.expertise 0032 }; 0033 my $sth = $dbh->prepare($sql); 0034 $sth->execute; 0035 my $expertise_table_ref = $sth->fetchall_arrayref; 0036 </%INIT>

  21. HTML::Mason (example search.html) 0001 <& Header, title=>"Search Expertise" &> 0002 0003 <FONT SIZE="-3"> 0004 <CODE> 0005 <% $sql %> 0006 </CODE> 0007 </FONT> 0008 0009 <TABLE BORDER="1"> 0010 <TR> 0011 <TH>LastName</TH> 0012 <TH>FirstName</TH> 0013 <TH>Department</TH> 0014 <TH>Resume</TH> 0015 </TR> 0016 %foreach my $row ( @{$results} ) { 0017 <TR> 0018 <TD><% $row->{LASTNAME} %></TD> 0019 <TD><% $row->{FIRSTNAME} %></TD> 0020 <TD><% $row->{DEPARTMENT} %></TD> 0021 <TD></TD> 0022 </TR> 0023 %} 0024 </TABLE>

  22. HTML::Mason (example search.html continued .1) 0024 </TABLE> 0025 0026 <& Footer &> 0027 0028 0029 <%INIT> 0030 my $dbh = DBI->connect($r->dir_config("IOUW_data_source"), 0031 $r->dir_config("IOUW_username"), 0032 $r->dir_config("IOUW_password"), 0033 { RaiseError => 1, LongReadLen => 1024 ** 2 } 0034 ); 0035 0036 my @expertise = ( ref $ARGS{Expertise} ) 0037 ? @{$ARGS{Expertise}} 0038 : $ARGS{Expertise}; 0039 my $binding = join ',', map {sprintf "\?"} @expertise; 0040 0041

  23. HTML::Mason (example search.html continued .2) 0042 my $sql = qq{ 0043 SELECT DISTINCT lastname, firstname, department.name department 0044 FROM iouw2000.staff_users, iouw2000.staff_expertise, 0045 iouw2000.staff_info, iouw2000.department 0046 WHERE staff_users.empno = staff_expertise.empno 0047 AND staff_users.empno = staff_info.empno 0048 AND staff_info.deptno = department.deptno 0049 AND staff_expertise.exp_id IN ( $binding ) 0050 }; 0051 my $sth = $dbh->prepare($sql); 0052 $sth->execute(@expertise); 0053 my $results = $sth->fetchall_arrayref({}); 0054 $dbh->disconnect; 0055 </%INIT>

  24. Conclusions Use Perl! Questions? John D. Groenveld (814)863-9896 <groenveld@acm.org> http://www.cse.psu.edu/~groenvel/

More Related