slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
IOUW 2000 Managing Oracle and Other Cool Things You Can Do with PERL John D. Groenveld PowerPoint Presentation
Download Presentation
IOUW 2000 Managing Oracle and Other Cool Things You Can Do with PERL John D. Groenveld

Loading in 2 Seconds...

play fullscreen
1 / 24

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


  • 92 Views
  • Uploaded on

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 "Hello World\n";

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'IOUW 2000 Managing Oracle and Other Cool Things You Can Do with PERL John D. Groenveld' - dara


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

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

slide2

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
slide3

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
slide4

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

slide5

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 }
slide6

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
slide7

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();

slide8

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;

slide9

Perl / Tk

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

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);

slide11

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 );

slide12

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;

slide13

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/>
slide15

Apache

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

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
slide17

HTML::Mason

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

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>

slide19

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

slide20

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>

slide21

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>

slide22

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

slide23

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>

slide24

Conclusions

Use Perl!

Questions?

John D. Groenveld

(814)863-9896

<groenveld@acm.org>

http://www.cse.psu.edu/~groenvel/