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

my $dbconfig = Config::IniFiles->new(-file => "C:/Users/Me/Documents/dev/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;