RE: Oracle and global temporary tables

[Steve Sapovits]

Here's the code.  I've X'ed out our DB access parameters.
Any help appreciated.

----
#!/usr/local/bin/perl

use strict;

use DBI;
use DBD::Oracle;

my $dbh = DBI->connect('DBI:Oracle:XXXXXX', 'XXXXXX', 'XXXXXX',
                       {RaiseError => 0, AutoCommit => 0});

my $table_one_create_sql = << "END_SQL";
   CREATE GLOBAL TEMPORARY TABLE temp_table_one
   (
       col_one             VARCHAR2(10),
       col_two             VARCHAR2(10)
   )
END_SQL

eval
{
    $dbh->do($table_one_create_sql);
};

# Oracle temp. tables may already exist if we used them before.
# No problem ... just go on and fill the table (new session =
# new data).
die $@ if ( $@ && ($@ !~ /ORA-00955/) );

$dbh->do("INSERT INTO temp_table_one VALUES ('foo1', 'foo2')");
$dbh->do("INSERT INTO temp_table_one VALUES ('bar1', 'bar2')");
$dbh->do("INSERT INTO temp_table_one VALUES ('fubar1', 'fubar2')");

show_table("temp_table_one", $dbh);

my $table_two_create_sql = << "END_SQL";
   CREATE GLOBAL TEMPORARY TABLE temp_table_two AS
   (SELECT col_one, col_two FROM temp_table_one
    WHERE temp_table_one.col_one = 'foo1')
END_SQL

my $sth = $dbh->prepare($table_two_create_sql);

print "temp_table_one after temp_table_two is prepared.\n";
show_table("temp_table_one", $dbh);

$sth->execute();

print "temp_table_one after temp_table_two is executed.\n";
show_table("temp_table_one", $dbh);
show_table("temp_table_two", $dbh);

# Oracle temp. tables may already exist if we used them before.
# No problem ... just go on and fill the table (new session =
# new data).

$dbh->disconnect();

sub show_table
{
    my $table = shift;
    my $dbh   = shift;
    my $sth   = $dbh->prepare("SELECT * FROM $table");
    my $row;

    $sth->execute();

    print "Rows from $table:\n";
    while (defined($row = $sth->fetchrow_arrayref()))
    {
        print "  ", join('|', @$row), "\n";
    }

    $sth->finish();
}

----
Steve Sapovits
GSI Commerce
[EMAIL PROTECTED]

> -----Original Message-----
> From: Steve Sapovits 
> Sent: Friday, October 04, 2002 3:19 PM
> To:   'Dave K'; [EMAIL PROTECTED]
> Subject:      RE: Oracle and global temporary tables
> 
> 
> Working on that.  We have verified the following:
> 
> 1)  All rows are in temp. table 1 at after we create it.
> 
> 2)  All rows are in temp. table 1 at the point we start to 
>     create temp. table 2.  They remain there until we execute
>     the statement that prepares temp. table #2.
> 
> We tried caching statement handles so they're not reused.  No
> difference. 
> 
> We fully turned off AutomCommit, RaiseError, and PrintError with
> no effect.
> 
> It almost seems like DBD::Oracle is reusing/resetting something
> at the Oracle level that's causing Oracle to act as if the first
> table was committed.  When you commit an Oracle global temporary
> table, the rows are gone -- the idea is to only use the data 
> uncommitted in the session.
> 
> ----
> Steve Sapovits
> GSI Commerce
> [EMAIL PROTECTED]
> 
> -----Original Message-----
> From: Dave K [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, October 04, 2002 1:15 PM
> To:   [EMAIL PROTECTED]
> Subject:      Re: Oracle and global temporary tables
> 
> Post some code...
> 
> "Steve Sapovits" <[EMAIL PROTECTED]> wrote in message
> [EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> >
> > Has anyone used DBI to create and use Oracle's global temporary tables?
> > This mostly works for us, but we're having problems creating multiple
> > tables, where the ones after the first depend on the first via joins.
> >
> > ----
> > Steve Sapovits
> > GSI Commerce
> > [EMAIL PROTECTED]
> >
>