Re: Where is the temporary table I created?

"gamaz" <gamaz_at_earthlink.net> wrote in message news:Ylsi9.2893$XE1.253085_at_newsread2.prod.itd.earthlink.net...
> Hi ,
> I created a temporary table using insert into, select into syntax.

Whoa! Step back a bit. Did you do something similar to this:

create global temporary table blah
as select * from real_table;

??

Or did you create a 'proper' table which you personally happen to think is a temporary staging area by using something like this:

create table temporary_one
as select * from real_table;

??

The difference is important.

>The
> Oracle server is in the unix machine. Now my question is: 1) Is there any
> way to download the Oracle table to my machine via ftp and ported to
access?

You'll have to define that a bit better. When you create a global temporary table (assuming that's what you did), then whilst the table structure is a permanent feature of the data dictionary, the contents of the table are retained either (a) only for the duration of a transaction or (b) only for the duration of a session. It depends on whether you declared 'on commit preserve rows' or not (the default being 'on commit delete rows').

It's also the case that a global temporary table's rows are visible only to the session that put them into the table.

So what that all means is that a global temporary table cannot be transported anywhere in any meaningful sense, because you'll need another session to either export them or ftp them, and that session won't be able to see the rows within the table.

If, however, you actually created a 'real' table which you just happen to think of as 'temporary', then normal export will be able to extract that table, and its rows, and you can ftp the dump file to anywhere you choose. Trouble is, the only thing that can read an export dump file is import: Access hasn't a hope in hell of pulling off that particular trick.

So if you have a table whose contents you wish to be visible in Access, you're going to have to forget all about global temporary tables and use real ones. And you're going to have to create an ODBC connection from Access to the Oracle database. And then you're going to have to use the 'link tables' functionality within Access to attach to the Oracle table. And once you've done all of that, then you can use Access functionality to copy the structure and contents of the linked Oracle table into a nativve Access table.

> 2)Which path in the Unix system can the new temporary table I created be
> found? I would appreciate any help. Thanks in advance. Regards.
>

Tables don't have paths. They live inside tablespaces. Tablespaces are made up of datafiles. And datafiles *do* have paths. But they're going to be of no use to you on this occasion. If you created a genuine global temporary table, then the table is currently living within the database's temporary tablespace. (And if the database has more than one temporary tablespace, it will be living within whichever of them has been set to be your user's temporary tablespace, as shown in the TEMPORARY_TABLESPACE column of dba_users).

Regards
HJR
>
Received on Thu Sep 19 2002 - 18:24:11 CDT