Oracle DB question

Doc

Oracle (and DB in general) noob here.

I need a scratch table. I don't care about saving any data or metadata for it beyond the session. Using "CREATE GLOBAL TEMPORARY TABLE" is not what I want, since the table sticks around beyond the session.

How do I do this?

Pheezer

Can you catch the session ending event, and use that to issue a drop table command

I've never developed for Oracle, so I don't know how good it is for this. Like if a session is interrupted due to just hard disconnect rather than a proper logout procedure, if it'll be able to identify that in a timely manner and kill the table. Although I guess you could watch for inactive accounts beyond a specific time-out period (something I know IS in Oracle), and when disconnecting them based on that, also drop that table.

I know, not exactly what you're looking for, but maybe a handy work around? Not too much work to implement, I don't think.

Doc

DrDizaster wrote: »

Can you catch the session ending event, and use that to issue a drop table command

I've never developed for Oracle, so I don't know how good it is for this. Like if a session is interrupted due to just hard disconnect rather than a proper logout procedure, if it'll be able to identify that in a timely manner and kill the table. Although I guess you could watch for inactive accounts beyond a specific time-out period (something I know IS in Oracle), and when disconnecting them based on that, also drop that table.

I know, not exactly what you're looking for, but maybe a handy work around? Not too much work to implement, I don't think.

The issue is that not only would I like the table to disappear from the DB once the user disconnects, ideally it would only be visible to that user.

"CREATE GLOBAL TEMPORARY TABLE" gets me halfway there, where it creates an empty table that all sessions can see. If one session adds to it, the rows are only visible to that session and are deleted as soon as the session is ended. Basically, I want to do that except with the metadata of the table as well.

No, there is no such thing as "CREATE LOCAL TEMPORARY TABLE," as far as I can tell.

Pheezer

What if you create a table upon each session initialization, using the session id in the table name. Just a regular table. And as part of the script just set it to be readable only by the user with that session ID. Then just run a script on a very regular basis, say every hour or whenever, to scrape any tables whose session ID bit of their title doesn't correspond to an active session.

It's a lot of scripting though, and I don't know enough about Oracle to say if this is a completely retarded idea in terms of how much of a performance impact you're going to see. Basically I'm just tossing out ideas until someone who knows something about Oracle happens by.

blincoln

I'm pretty sure there's a way to do this. If no one else can answer your question before Monday, I can look at some old stored procedures I modified at work back in the olden days.

Jimmy King

Out of curiosity, have you tried creating a local temp table? From some googling it looks like Oracle 8i does not have them but it would appear newer versions might as they are mentioned Here

vonPoonBurGer

The "global" part of a global temporary table refers to the fact that its table definition is visible to all sessions. However, rows in the table are only visible to the session that inserted them. This is the way it's been since 8i, as far as I know. This is ideal, since it means that you only have to create the table once, and you never have to worry about session A seeing data from session B. Is there some reason why you don't want the table definition to persist beyond the session?

Doc

vonPoonBurGer wrote: »

The "global" part of a global temporary table refers to the fact that its table definition is visible to all sessions. However, rows in the table are only visible to the session that inserted them. This is the way it's been since 8i, as far as I know. This is ideal, since it means that you only have to create the table once, and you never have to worry about session A seeing data from session B. Is there some reason why you don't want the table definition to persist beyond the session?

It's being loaded from a file. The file defines the columns as well as the data, so if a user picks a different file, the columns could all be different.

blincoln

So here's what I found from looking at my old stored procedures. In MS SQL Server (and several other products with "SQL" in the name) if you issue a CREATE TABLE command and give the table name a pound sign before its name, it will be created as a local temporary table which is automatically dropped at the end of the transaction. Unfortunately, Oracle doesn't seem to support anything like that.
Not having worked with Oracle, I'm thinking DrDizaster's suggestion of creating a global temp table whose name is based on some sort of session ID or GUID type thing is probably the closest you can get.