Perl is quite good for DB interaction, you may want to have a look at a few tutorials here in the monastry (Tutorials): Reading from a database and The fine art of database programming.

Hope this helps, -gjb-

Makeshifts last the longest.

Here is my code:

my $dbh= DBI->connect("DBI:Oracle:", "scott", "tiger") or  die $DBI::e
+rrstr;
my $sth1= $dbh->prepare(q{truncate table directory_data}) or die $DBI:
+:errstr;
$sth1->execute or die $sth1->errstr;

my $sth = $dbh->prepare(q{
    INSERT INTO directory_data(dn, cn, surname, given_name, initials, 
+generational_qualifier, building,
    room_number, mailstop, telephone, fax, mail, tfs_id, uid) 
    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)}) or die $dbh->errstr;

foreach $emp (keys %employee){ 
$sth->execute($employee{$emp},.,....)
[download]

Currently I have created the Oracle table using SQL Plus. I would like to,however,create the table using a different perl script that I call within this script. Also each time I call the original script I want it to drop the current table and plot the new information. I would like to perform these sql functions in a different script as well. I was told that creating these different scripts will clean up my code. I hope this clarifies my question and again thanks for any help.

Spreading your code out among several different files won't really clean up your code ... it will just spread it out among several different files. :)

What you need to do is search the CPAN for some modules that will make your task easier ... modules like DBIx::Connect which will allow you to abstract the DB connect info out of your scripts (and into a config file). However, before i start ripping my one script into a modularized, maintainable package ... i'd clean up what you have. Here is how i would code your snippet:

my $dbh = DBI->connect(
   qw(DBI:Oracle: user pass),
   {RaiseError => 1},
);

# with RaiseError turned on, no need for die and errstr

$dbh->do("truncate table directory_data");

my $sth = $dbh->prepare(q{
    INSERT INTO directory_data(
       dn, cn, surname, given_name, initials, 
       generational_qualifier, building, room_number,
       mailstop, telephone, fax, mail, tfs_id, uid
    ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)
});
 
$sth->execute($employee{$_},.,....) for keys %employee;
[download]

Sometimes i like to use code to generate an INSERT statement:

my @field = qw(
   dn cn surname given_name initials
   generational_qualifier building room_number
   mailstop telephone fax mail tfs_id uid
);

my $table = 'directory_data';

my $sql = "insert into $table (" 
   . join(',', @field)
   . ') values (' 
   . join(',', map '?', @field) 
   . ')'
;
[download]

You could turn that snippet into a subroutine and pass it the name of the table, an reference to an array of field (column) names, and a reference to another array that contains the values (just make sure the two arrays line up!).

Hope this helps and gives you some ideas. :)

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

If you want to further 'abstract' your code DBIx::Recordset is worth having a look at.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Why would you want to create the table using a different perl script???

Your truncate will delete all the data... do you need to run the script somewhere where the table does not exist?

I think that having one script will make your code cleaner... since this seems like a small script... no need to spread all the code in different files... How about splitting it in different subroutines?

you would probably want to replace the truncate by a drop and a create, but I don't think it's a good idea to do so in Oracle... (Will the table be in a separate Tablespace? How will extents allocation go, How frequently will you do this... it depends on so many factors)