The discussion and investigation continues, but it made me think to test if there's any similar strange behaviour with Oracle via DBI.
The temporary table model is somewhat different in Oracle, and centers around the "CREATE GLOBAL TEMPORARY TABLE.." statement. Temp table definitions are always global, but data is always private to the session, and whether data persists over a commit depends on whether the qualification "on commit preserve rows" or "on commit delete rows" is specified.
testOraTempTables.pl is a simple test script to check out the behaviour. Good news is that all seems to be a-ok. The temporary table definition is persistent across sessions, but data is not, and importantly (..the point of Andon's investigation..) data is preserved across DBI calls within the same session as expected.
Sample output from the test program:
C:\MyDocs\Testers2\perl\dbi>perl testOraTempTables.pl orcl scott tiger
[1st connection] connect to orcl {AutoCommit => 1}:
[1st connection] create global temp table:
create global temporary table t1 (x varchar2(10)) on commit preserve rows
[1st connection] insert 3 rows of data into it: insert into t1 values (?)
[1st connection] should be 3 rows because we have "on commit preserve rows" set:
select count(*) from t1 = 3
[2nd connection] connect to orcl:
[2nd connection] should be 0 rows because while the table definition is shared, the data is not:
select count(*) from t1 = 0
[2nd connection] disconnect:
[1st connection] disconnect:
[1st connection] reconnect {AutoCommit => 0}:
[1st connection] should be 0 rows because this is a new session:
select count(*) from t1 = 0
[1st connection] drop the temp table: drop table t1
[1st connection] create global temp table:
create global temporary table t1 (x varchar2(10)) on commit delete rows
[1st connection] insert 3 rows of data into it: insert into t1 values (?)
[1st connection] should be 3 rows because we have autocommit off and not committed yet:
select count(*) from t1 = 3
[1st connection] should be 0 rows because now we have committed:
select count(*) from t1 = 0
[1st connection] disconnect:
[1st connection] reconnect {AutoCommit => 1}:
[1st connection] insert 3 rows of data into it: insert into t1 values (?)
[1st connection] should be 0 rows because we have autocommit on and "on commit delete rows" defined:
select count(*) from t1 = 0
[1st connection] disconnect:
[1st connection] reconnect {AutoCommit => 0}:
[1st connection] drop the temp table: drop table t1
[1st connection] disconnect: