Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2007
    Posts
    24

    Unanswered: Looking for temporary table advice

    Scenario:
    I'm developing an interface for our science lab to a small, very low traffic (2 or 3 user) Oracle 10g database. We work with a lot of delimited files. I would like the user to be able to load files into temporary tables in our database such that they may be queried against.

    Issues:
    - These files contain various numbers of fields. Rarely do two files contain the same fields.
    - These tables need to be destroyed at the end of each session.
    - A user may load several files during a session (several tables per session may be required).
    - A user may have multiple running sessions.
    - These tables must only be exposed to the session through which they were instantiated.

    If anyone has experience dealing with this kind of scenario I would really appreciate your advice.

    Some questions I've been asking myself:
    - Are there any high-level tools that might be employed?
    - Is using a global temporary table the right approach here?
    - Is it possible to create a trigger that will destroy any objects associated with a certain SID when a user logs off?

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Various numbers of fields? I'd try to see if you can make the extract definitions the same in terms of column count and datatypes, first off.

    Then, you can look into using External Tables so that users can query against their datafiles.

    - - -
    If the number of columns are still varying, and you need to delete the data after using it, why don't you just teach your users how to use MSAccess or something? Then you don't have to be involved at all. I don't see the utility in using Oracle if everything is temporary.

    --=cf

  3. #3
    Join Date
    Jan 2007
    Posts
    24
    Quote Originally Posted by chuck_forbes
    Various numbers of fields? I'd try to see if you can make the extract definitions the same in terms of column count and datatypes, first off.
    These are third party files that describe any number chemical properties.


    Quote Originally Posted by chuck_forbes
    Then, you can look into using External Tables so that users can query against their datafiles.
    Not possible.

    Quote Originally Posted by chuck_forbes
    If the number of columns are still varying, and you need to delete the data after using it, why don't you just teach your users how to use MSAccess or something? Then you don't have to be involved at all. I don't see the utility in using Oracle if everything is temporary.
    Everything else in the database isn't temporary.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    You can link the other tables into MSAccess. As for the "not possible" on the External Tables, you're using 10g, so they're available for use.

    - - - - -
    Since you're talking dynamic table def's, maybe there's someone with experience in an ETL tool which can conform to a flexible table definition. Seems like there should be one, I've just never heard of it.

    I think you're main problem is going to be combining those "temp" tables with your other permanent tables, in whatever application you have going there. The app is going to be tough to build if your "temp" table name constantly changes, and field names change, and their datatype changes. It sounds like you're going to have to train your users at some point how to set their own objects & queries up. MSAccess would be an easy environment for this, since you're not trying to save that "temp" data.

  5. #5
    Join Date
    Jan 2007
    Posts
    24
    Quote Originally Posted by chuck_forbes
    You can link the other tables into MSAccess. As for the "not possible" on the External Tables, you're using 10g, so they're available for use.
    External tables aren't possible because of the unpredictable nature of the file contents. I didn't mean to suggest they weren't available.

    Quote Originally Posted by chuck_forbes
    I think you're main problem is going to be combining those "temp" tables with your other permanent tables, in whatever application you have going there. The app is going to be tough to build if your "temp" table name constantly changes, and field names change, and their datatype changes. It sounds like you're going to have to train your users at some point how to set their own objects & queries up. MSAccess would be an easy environment for this, since you're not trying to save that "temp" data.
    Some of these files contain chemical structural information in various string and byte formats that MS Access is simply incapable of displaying.

    I think you're confusing my discussion of temporary tables with the data itself. I haven't said anything about what I would like to do with the data. I may want to merge it into my existing tables. I may want to run stored procedures or calculations on it. I may simply want to be able to query it against my existing data. I'm not writing this interface simply for the sake of viewing the data. The intention is to provide the capability to conveniently query this file data against my permanent tables and manipulate it in whatever fashion I choose.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    I still would say the same thing. You can include a local table and a linked table in the same query in MSAccess.

    By not being unable to come up with a standard file format, or even a standard set of file formats, it makes your implementation tough.

    But, it sounds like those comments aren't helping you, so ...
    ---

    If you want some users to be able to create a table that obscures it's data from other users, use a Global Temporary Table with "on commit preserve rows". Or, and I'd go this route, give each user acct rights to create tables in their own schema. Then, don't provide privileges to any other users so that they cannot see those tables.

    There is a database-level on logoff trigger where you could clean up the tables, but you'd need to use "EXECUTE IMMEDIATE" to perform the DML ('DROP TABLE ...') in PL/SQL.

    ---=cf

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    External tables aren't possible because of the unpredictable nature of the file contents.
    I'm confused. The only thing different between an external table & a regular table is the SQL syntax used to CREATE the table.
    Any SELECT statement does not know or care whether the underlying table is an external table or an internal table.

    So what exactly automagically make your solution easier by using internal tables as opposed to using external tables?

    If you can actually make this pig fly, I'll be very impressed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jan 2007
    Posts
    24
    Quote Originally Posted by anacedent
    I'm confused. The only thing different between an external table & a regular table is the SQL syntax used to CREATE the table.
    Any SELECT statement does not know or care whether the underlying table is an external table or an internal table.
    Good point. But, external tables would actually be less useful than regular tables in this context, for a few reasons, but primarily because I can't index them.

    If you can actually make this pig fly, I'll be very impressed.
    Actually, some additional user tracking in conjunction with a logoff trigger worked wonderfully and was very easy to implement. Thanks chuck.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It really "smells" like a global temporary table issue, but some points of the process say "no" (more than one session per user). As you can't / don't want to use external tables feature, how about this idea: I don't know how these "files" look like. Are "chemical" properties numerics? Characters? Is there anything in common among all of these files? For example, perhaps all of them have "chemical process ID" at the beginning of each row, timestamp follows, and then there's a varying number of numeric data.

    If so, perhaps you might create a staging table which would be able to "read" the whole record into a single column:
    Code:
    CREATE TABLE cp_stag (whole_row VARCHAR2(500));
    Using SQL*Loader (or even external tables feature), load a file into this table.

    The second table would look like this:
    Code:
    CREATE TABLE cp_data
    (process_id   NUMBER,
     process_date DATE,
     property_1   NUMBER,
     property_2   NUMBER,
     property_3   NUMBER,
     ...
     property_n   NUMBER
    );
    Parsing every record in the 'cp_stag' table (as you already know a delimiter), you might extract pieces of information and put them into appropriate columns of the 'cp_data' table.

    Afterwards, querying 'cp_data' might be a simple task.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •