As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options

Oracle DB question

DocDoc Registered User, ClubPA regular
edited November 2007 in Help / Advice Forum
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?

Doc on

Posts

  • Options
    PheezerPheezer Registered User, ClubPA regular
    edited November 2007
    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.

    Pheezer on
    IT'S GOT ME REACHING IN MY POCKET IT'S GOT ME FORKING OVER CASH
    CUZ THERE'S SOMETHING IN THE MIDDLE AND IT'S GIVING ME A RASH
  • Options
    DocDoc Registered User, ClubPA regular
    edited November 2007
    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.

    Doc on
  • Options
    PheezerPheezer Registered User, ClubPA regular
    edited November 2007
    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.

    Pheezer on
    IT'S GOT ME REACHING IN MY POCKET IT'S GOT ME FORKING OVER CASH
    CUZ THERE'S SOMETHING IN THE MIDDLE AND IT'S GIVING ME A RASH
  • Options
    blincolnblincoln Registered User regular
    edited November 2007
    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.

    blincoln on
    Legacy of Kain: The Lost Worlds
    http://www.thelostworlds.net/
  • Options
    Jimmy KingJimmy King Registered User regular
    edited November 2007
    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

    Jimmy King on
  • Options
    vonPoonBurGervonPoonBurGer Registered User regular
    edited November 2007
    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?

    vonPoonBurGer on
    Xbox Live:vonPoon | PSN: vonPoon | Steam: vonPoonBurGer
  • Options
    DocDoc Registered User, ClubPA regular
    edited November 2007
    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.

    Doc on
  • Options
    blincolnblincoln Registered User regular
    edited November 2007
    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.

    blincoln on
    Legacy of Kain: The Lost Worlds
    http://www.thelostworlds.net/
Sign In or Register to comment.