Dear Monks,

Perl newbie here.

I am looking to write some queries/stored procedures to return table, insert them into temp table and export that to flat file. While running, the perl program continues to throw "table or view does not exist" in runtime. I also am unable to see the temp table in my database.

Please can you help me understand what is wrong here...

# load all the libraries
use DBI;
use Config::IniFiles;
use warnings;
use strict;

my $dbconfig = Config::IniFiles->new(-file => "C:/Users/Me/Documents/d
+ev/perl/project1/dbcredentials.ini");

my $dbname = $dbconfig->val('myoracle','dbname');
my $driver = $dbconfig->val('myoracle','driver');
my $host = $dbconfig->val('myoracle','host');
my $sid = $dbconfig->val('myoracle','sid');
my $username = $dbconfig->val('myoracle','username');
my $password = $dbconfig->val('myoracle','password');
my $port = $dbconfig->val('myoracle','port');

my $dsn = "dbi:$driver:host=$host;sid=$sid;port=$port";
my $dbh = DBI->connect($dsn,$username,$password) or 
    die $DBI::errstr;

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

my $sth = $dbh->prepare("
    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
");

$sth->execute or die "cant execute SQL statement";

my @row;
while (@row = $sth->fetchrow_array()){

    my $id = @row[0];
    my $name = @row[1];
    my $title = @row[2];
    my $dept = @row[3];

    my $insert1 = $dbh->do("
    INSERT INTO HR.Tempest (
        ID,
        NAME,
        TITLE,
        DEPT
    ) VALUES (
         $id,
        '$name',
        '$title',
        '$dept'
    )
    ");
    ###$insert1->execute() or die "{ERROR} - ". $insert1->errstr;

    ###print"@row\n\n";
}

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

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

$dbh->disconnect() or warn "error disconnecting";

exit;
[download]