SpaceCowboy:

I don't have a database server installed handy, so I can't really help directly with your question. But with the example code you listed, I just wanted to offer a couple suggestions:

You might consider using placeholders in your prepared statements, as that is well-tested and can help you avoid injection attacks. Something like this:

# Use ? as placeholders for the values

my $sth2 = $dbh->prepare(<<EOSQL);
INSERT INTO HR.Tempest ( ID, NAME, TITLE, DEPT )
VALUES ( ?, ?, ?, ? )
EOSQL

while ( @row = $sth->fetchrow_array() ) {
    my ($id, $name, $title, $dept) = @row;

    # Now when executing, just pass the values you want to the execute
    # statement, and DBI will pass the properly-quoted values to the
    # DBD backend in the order specified (i.e., $id will be used for
    # the first ?, $name for the second, etc.)
    $sth2->execute( $id, $name, $title, $dept );
}
[download]

Next, you ought to leverage the database and let it do more of the work. The database is optimized for large sets of data. The cost of serializing the result of the select and shipping it to your computer, then having your computer deserialize the data handle it and then turn it into a sequence of SQL statements, serializing each one and shipping it back to the database server (who would have to deserialize each of the statements and then process them and perform the inserts) costs *vastly* more time, CPU and network bandwidth than simply telling the database server to insert the rows into your database. Something like this:

$dbh->do("
    CREATE GLOBAL TEMPORARY TABLE HR.Tempest(
        ID INT,
        NAME VARCHAR2(25),
        TITLE VARCHAR2(50),
        DEPT VARCHAR2(100)
    ) ON COMMMIT PRESERVE ROWS
");
$dbh->commit();

# Tell the database to select the rows of interest and populate the
# temporary table with them
$dbh->do("
    INSERT INTO HR.Tempest
    SELECT e.employee_id, e.first_name, j.job_title, d.department_name
+ 
    FROM   HR.employees e, HR.jobs j, HR.departments d
    WHERE  e.job_id = j.job_id
    AND    e.department_id =  d.department_id
");
$dbh->commit();
[download]

Finally, when working with SQL, I suggest you structure your statements slightly differently. Using the longer join syntax lets you tell the database how to connect the tables together, freeing the WHERE clause to specify which records to select. That helps future programmers more easily see what the statement is doing:

INSERT INTO HR.Tempest
    INSERT INTO HR.Tempest
    SELECT e.employee_id, e.first_name, j.job_title, d.department_name
+ 
    FROM   HR.employees e
    JOIN   HR.jobs j        ON j.job_id = e.job_id
    JOIN   HR.departments d ON d.department_id = e.department_id
    WHERE  <criteria to select a subset of records>
[download]

The more complex the statement, the easier it is to express the structure and make the SQL easier to comprehend. Here's a purely artificial example with multiple joins against tables in a single statement:

---- We want to find employees making over $150K/yr in marketing or sa
+les.
---- However, the department_id of marketing and sales teams are split
+ into
---- dozens of regions, so we'll have to use the boss' department to
---- narrow the list of employees.  That's fine, though, as we need th
+e
---- bosses information, too.

SELECT boss.employee_id as boss_id,    boss.first_name        as boss_
+name, 
       boss_j.job_title as boss_title, boss_d.department_name as boss_
+dept,
       emp.employee_id  as emp_id,     emp.first_name         as emp_n
+ame, 
       emp_j.job_title  as emp_title,  emp_d.department_name  as emp_d
+ept,
       emp.salary       as emp_salary

---- Standard table hookup for the boss

FROM HR.employees   boss
JOIN HR.jobs        boss_j ON boss_j.job_id        = boss.job_id
JOIN HR.departments boss_d ON boss_d.department_id = boss_e.department
+_id

---- Standard table hookup for the employee

     -- tie the employee to their boss
JOIN HR.employees   emp   ON emp.boss_id         = boss.employee_id
JOIN HR.jobs        emp_j ON emp_j.job_id        = emp.job_id
JOIN HR.departments emp_d ON emp_d.department_id = emp_e.department_id

---- Now select the employees we're looking for.

WHERE boss.department_name IN ('MARKETING', 'SALES')
AND   emp.salary > 150000;
[download]

Yes, it's an overly-contrived example, but it easily lets you separate "standard" table joining from the important selection criteria. When you stuff it all in a WHERE clause, it's difficult to sort it all out in your head. There's *no* difference at all to the SQL server, but it's a world of difference to the people who have to figure out what the statement says with a minimum of effort.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

First relegate error handling to the DBI instead of using "or die" messages within your code.
I would also turn AutoCommit on, if it is not on by default (it is in SQLite, but I don't know about Oracle).
An anon hash can be specified in the connect, like this:

my $dbh = DBI->connect($dsn,$username,$password,{RaiseError=>1, AutoCo
+mmit=>1}) or 
    die $DBI::errstr;
[download]

When you want an explict transaction, override the auto commit by starting a transaction explictly with $dbh->begin_work; After the work is done, then $dbh->commit; This way it is very clear within your code exactly what work is within that particular transaction.

Use placeholders in your insert instead of continually "preparing" the statement again and again within a "do". The DB can wind up doing quite a bit of work in order to "prepare". Note that "prepares" are specific to a particular table meaning that the table name cannot be a "placeholder variable" within a prepared statement.

my $insert1 = $dbh->prepare("
    INSERT INTO HR.Tempest (
        ID,
        NAME,
        TITLE,
        DEPT
    ) VALUES (?,?,?,?)
    ");

for (...whatever...)
{
    $insert1->execute($id,$name,$title,$dept);
}
[download]

When you say "program continues to throw "table or view does not exist" in runtime", that sounds like something is wrong with your temporary table.

I would start by massively dumbing this thing down. Make a very simple temp table with perhaps just a single column. Put a few rows in it. Then see if you can read and print that table out. If that doesn't work and you can't make it work, then you have a simple and direct question to pose to the Monks.

If your temp table is pretty big, then making its creation a single transaction will speed things up a lot. A main limit in DB's is the number of transactions per second. A single transaction can have millions of operations. Don't worry about this just to get your code working. After it is working, then slap a begin_work before and commit after the creation work.

Update: Well now that I look more closely:

my $resultset = $dbh->prepare("Select * from Tempest");

while(@row = $resultset->fetchrow_array()){
   print"@row\n\n";
}
[download]

my $resultset = $dbh->prepare("Select * from Hr.Tempest");
$resultset->execute();

while(@row = $resultset->fetchrow_array()){
   print"@row\n\n";
}
[download]

foreach my $year (sort keys %yr2dir)
{
   create_table($dbh,$year);
   
   $dbh->begin_work;
   populate_table($dbh,$year,$yr2dir{$year});
   $dbh->commit;
}
[download]

If your turn auto commit off, you wind up having to put in explicit commit statements for relatively quick operations like create_table. My preference is leave auto commit on (and I do think that is usually the default) and explicitly override that for "important operations".

Update3:
This is off topic, but folks who work with large tables may find this interesting. With SQLite, I have found that it is faster when importing data to create an entire table, to create the table first without indices and then create the indices (if any) later. SQLite "likes" a lot of memory for index creation. You cannot vary the memory footprint of a Perl process. But you can and should vary the memory footprint of SQlite when appropriate. I have tested this and it works. Giving SQLite say an extra 500 MB of memory during index creation will speed things up a lot. When that is over, you can give that memory back to the O/S.

What oracle version?

Does the same SQL work when given directly to the server (via SQLplus, for example)?

Catch perl errors, DB errors; what do they say? (your code checks some but not all)

(after the connect line, perhaps insert:

$dbh->{RaiseError} = 1;
[download]

I also am unable to see the temp table in my database.

Cheers
Chris