Perl Operations Oracle

One, Perl connect Oracle Database

[Plain]View Plaincopy

2. [Email protected] perl_script]$ more connect.pl
4. #!/usr/bin/perl
6. #perl script used to connect to Oracle
8. Use strict;
10. Use DBI;
14. My $tnsname = "ORA11GR2";
16. My $username = "Scott";
18. My $password = "Tiger";
22. My $dbh =dbi->connect ("Dbi:oracle: $tnsname", $username, $password) or die "cannot conenct DB: $DBI:: errstr\n";
24. Print "I has connected to the Oracle database!\n";
28. $DBH->disconnect or warn "DB disconnect failed: $DBI:: errstr\n";
30. Print "Disconnected from Oracle databae!\n";
34. [Email protected] perl_script]$./connect.pl
36. I have connected to the Oracle database!
38. Disconnected from Oracle databae!

Ii. inserting data into the database

[Plain]View Plaincopy

2. [Email protected] perl_script]$ more insert.pl
4. #!/usr/bin/perl
6. # This code was used to the insert data to Oracle Database
10. Use strict;
12. Use DBI;
16. my $id = 2;
18. My $name = "Denver";
22. My $dbh = Dbi->connect ("dbi:Oracle:ora11gR2", "Test", "test") or die "Cannot connect DB: $DBI:: errstr\n";
26. My $sql = Qq{insert into M VALUES (?,?)};
28. My $sth = $dbh->prepare ($sql);
30. $sth->execute ($id, $name);
34. Print "I have inserted the record!\n";
38. $DBH->disconnect or warn "DB disconnect failed: $DBI:: errstr\n";
42. [Email protected] perl_script]$./insert.pl
44. I have inserted the record!
46. [Email protected] perl_script]$

Third, delete data

[Plain]View Plaincopy

2. [Email protected] perl_script]$ more delete.pl
4. #!/usr/bin/perl
6. # Delete Data from Oracle Database
10. Use strict;
12. Use DBI;
16. my $id = 2;
20. My $dbh = Dbi->connect ("dbi:Oracle:ora11gR2", "Test", "test") or die "Cannot connect DB: $DBI:: errstr\n";
24. My $sql = Qq{delete from M WHERE id= $id};
26. My $sth = $dbh->prepare ($sql);
28. $sth->execute ();
30. Print "I have deleted the record!\n";
34. $DBH->disconnect or warn "DB disconnect failed: $DBI:: errstr\n";
38. [Email protected] perl_script]$./delete.pl
40. I have deleted the record!

Iv. Inquiries

[Plain]View Plaincopy

2. [Email protected] perl_script]$ more select.pl
4. #!/usr/bin/perl
6. # Here's an example code piece to select data from Oracle
10. Use strict;
12. Use DBI;
16. My $host = "localhost";
18. My $sid = "Denver";
22. My $dbh = Dbi->connect ("dbi:Oracle:ora11gR2", "Test", "test") or die "Cannot connect DB: $DBI:: errstr\n";
24. Print "I has connected to the Oracle 11g R2 database!\n";
28. My $sql = qq{select ID, name from m};
30. My $sth = $dbh->prepare ($sql);
32. $sth->execute ();
36. My ($pid, $pname); #declare columns
38. $sth->bind_columns (undef, \ $pid, \ $pname);
40. Print "The results are:\n\n";
42. while ($sth->fetch ()) {#fetch The rows from DataBase
44. Print "ID: $pid,---NAME: $pname \ n";
46. }
48. $sth->finish ();
52. $DBH->disconnect or warn "DB disconnect failed: $DBI:: errstr\n";
54. [Email protected] perl_script]$./select.pl
56. I have connected to the Oracle 11g R2 database!
58. The results are:
62. id:0,---name:**e
64. Id:1,---name:**e
66. [Email protected] perl_script]$