Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Temporary table in package.

    I've written a script that creates a temporary table, loads data into it, and then drops it. The script works flawlessly. But when I try to fold the script into a package I get the following error when trying to create the temporary table:
    'CREATE' is not a valid identifier
    What gives? Can't I use temporary tables in packages and procedures? Is there a way around this?

    Thanks.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can NOT do DDL directly from within PL/SQL.
    You must use EXECUTE IMMEDIATE

    P.S.
    This is a VERY poor design.
    You should be using Global Temporary Table
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I am using a Global Temporary Table. The code chokes on trying to create it within my package procedure.
    Where and when should this temporary table be created? It is only needed by this one package procedure.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Just create it and never drop it. DDL are expensive in Oracle.

    Also, this *probably* doesn't need a temporary table at all..

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by JMartinez
    Also, this *probably* doesn't need a temporary table at all..
    Well...it runs 300 times faster using a temporary table than it did using a traditional oracle cursor-oriented technique. It completes in just 30 seconds now, where it took more than two and a half hours before.

    It would be simplest if I could just take my script and wrap it inside a package, but I'm coming to realize that this is seldom possible with oracle.

    Creating and the dropping the table as a permanent table is something I am loathe to do, as it is asking for collisions between processes. It seems to me that since the recordset is only used within the procedure, its scope should remain within the procedure and it should not be created outside the procedure...

    The alternative (which I am exploring) is to do all the processing inside the production table. But since I am only dealing with a subset of the data I am sure the queries against the full production table (100 times as large) will be significantly slower. This does not appear to me to be a good programming style, but I am becoming resigned to it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you are using a GTT, make it outside the package and NEVER drop it. Why do you feel the need to create and drop a GTT which by it's very nature can used by multiple people at the same time without confict. That is what it is designed for. Each session using the table will NEVER EVER see the other sessions data.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Because it is bad programming practice to create objects outside of the scope in which they will be used.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by blindman
    It would be simplest if I could just take my script and wrap it inside a package, but I'm coming to realize that this is seldom possible with oracle.
    Did you read anacedent's post?
    Quote Originally Posted by anacedent
    You must use EXECUTE IMMEDIATE
    This way you can easily create and drop the table if you really feel like you have to.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, I did try that, but then any statements that referenced the table I just created failed to compile as the table did not exist yet. So I guess I would have to run every statement in my code with "EXECUTE IMMEDIATE"? This solution also appears to be less than elegant...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    There is nothing stopping you from creating a simple procedure in your package that only does EXECUTE IMMEDIATE CREATE TABLE.... which gets invoked once manually.

    After this has been completed, then the rest of your code will run fat, dumb, & happy; plus it meets your "requirement" about being "self-contained".
    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.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Its not a matter of it being "self-contained", or where the code to create the global temporary tables is stored. Its an issue of scope. Even if I create a procedure to generate these tables and call it once, the tables it creates will still be hanging around after the procedure that requires them has completed.

    The scope of these temporary recordsets lies completely within the procedure, and so by any standard of good programming practice they should be created and destroyed within the procedure. Apparently, there is no way to do this in oracle. Frankly, even making them "Global" temporary tables accessable by other connections is overkill. They really should be "local" temporary tables but I don't see that oracle has such a construct.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    From
    http://download-west.oracle.com/docs....htm#sthref769

    Temporary Tables

    In addition to permanent tables, Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.

    The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table that can be transaction-specific or session-specific. For transaction-specific temporary tables, data exists for the duration of the transaction. For session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table, because each session has its own private data.

    A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session. It does not truncate the data of other sessions that are using the same table.

    DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination, either when the user logs off or when the session terminates abnormally such as during a session or instance failure.

    You can create indexes for temporary tables using the CREATE INDEX statement. Indexes created on temporary tables are also temporary, and the data in the index has the same session or transaction scope as the data in the temporary table.

    You can create views that access both temporary and permanent tables. You can also create triggers on temporary tables.

    Oracle utilities can export and import the definition of a temporary table. However, no data rows are exported even if you use the ROWS clause. Similarly, you can replicate the definition of a temporary table, but you cannot replicate its data.
    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.

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I don't have an Oracle system to test this on, so excuse the dumb question. You are saying that you can create a Global Temporary table, and each session will think that GTT contains only whatever data was put into it by that session, no matter how many sessions pump data into the thing?

  14. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by MCrowley
    I don't have an Oracle system to test this on, so excuse the dumb question. You are saying that you can create a Global Temporary table, and each session will think that GTT contains only whatever data was put into it by that session, no matter how many sessions pump data into the thing?
    Exactly right.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks for the link, though I think I came across that in my google searches.
    So an Oracle GTT is actually more similar to SQL Server's local temporary tables, though in a global object. It is not like SQL Server's global temporary tables, where the data itself is available across connections. Correct me if I misunderstand, please.

    I do appreciate everyone's input on this, though I am still left with the options of either abandoning the temporary table logic which is performing so well for me, or having these temporary structures exist outside of the scope of their dependencies...

    Right now I'm attempting to eliminate the use of the temporary tables, but this is raising its own complications as a result of the business logic required.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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