Results 1 to 7 of 7
  1. #1
    Join Date
    May 2007
    Posts
    3

    Angry Unanswered: Global Temporary Table

    Hi all!...I am working on Crystal Reports and Oracle.When a report is run it references a prodedure(s) which in turn calls for a Function.The functions returns value from a Global Temporary Table.So this returned Data is displayed in the Report.When the Rpt runs for the first time ,say it displays 10 rows...I then try rerunning the report without logingoff from the same DB Source ,now the report doesnot seem to delete the old data..it displays two sets of data..one old set and another new set so totally 20 rows.When I issue a DELETE * from GTT an error ORA-06503 is returned, But however when I use TRUNCATE command instead of DELETE, in my function there are no such problems ,infact the report is refreshed between sessions and transactions.Though I have a solution to the problem,I dont understand the Logic behind.
    Help is appreciated.
    Thanks in Advance.

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Oracle temporary tables isolate the data between sessions. If you use a new session (ie. log off and log on again) then you will see new data. The other method is that you issue a commit or rollback to clear the data. This method assumes that the temporary table does not have "Preserve on Commit" option set. If it does, then the data will remain even after the commit is run. If not, then running a commit will clear the data from the temporary table.

    I often wonder why people want to use temp tables instead of just using SQL for a report... Can you shed some light on this, just for discussion sake?

  3. #3
    Join Date
    May 2007
    Posts
    3
    Quote Originally Posted by dayneo
    Oracle temporary tables isolate the data between sessions. If you use a new session (ie. log off and log on again) then you will see new data. The other method is that you issue a commit or rollback to clear the data. This method assumes that the temporary table does not have "Preserve on Commit" option set. If it does, then the data will remain even after the commit is run. If not, then running a commit will clear the data from the temporary table.

    I often wonder why people want to use temp tables instead of just using SQL for a report... Can you shed some light on this, just for discussion sake?
    I have issued a 'DELETE on COMMIT' during table creation.The data in the Rpts not only get refreshed between sessions but also transactions (i.e)irrespective of whether I logoff or not Data gets deleted.This works for TRUNCATE.My question is why is it not working for DELETE?
    And to answer your question of why one uses the temp table and not just SQL...have a look at the foll url:
    http://www.dba-oracle.com/t_tuning_g...ary_tables.htm

  4. #4
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    sorry. I missed the real point of your question before. Never had this myself, but trying to get the same error.
    Thanks for the temp table lesson (the link).

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Do you have an "exception when others then" in your function. Check that the function is not failing to your exceptions block that is "hiding" the error and then not returning anything from the function. eg:
    Code:
    create or replace function testfnc return number as
      l_n number;
    begin
      l_n := 1/0;
      return l_n;
    exception
      when others then
         null; -- Very BAD! Hiding error and will give ORA-06503 since function no longer returns a value
    end;
    There is probably something else preventing the delete, it is giving an error, which is being caught by your exception handler and hidden, then exiting the funciton and giving ORA-06503 because no value was returnd (well at least I suspect this is the case).

    See Tom's response to ORA-06503 (I can't really claim the answer above )
    http://asktom.oracle.com/pls/asktom/...72179182554989

    And again, according to Tom, you shouldn't need to delete/truncate from the temp table, but rather issue commit/rollback:
    http://asktom.oracle.com/pls/asktom/...:5243764001269

    Hope this is the problem... good luck

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Are you issuing a commit anywhere in your code? Maybe your function should look something like:

    Code:
    create or replace function as
    begin
      commit;
      -- do stuff
    end;
    Since the first step is to commit, it will clear the table. It's seems like a hack for some reason, but I guess it's really no different than

    Code:
    create or replace function as
    begin
      delete from my_global_temp_table;
      -- do stuff
    end;

    ---=Chuck

  7. #7
    Join Date
    May 2007
    Posts
    3

    Smile

    Thankyou Dayneo and Chuck! Using 'Commit' before the query starts, solves the problem.

    Regards,
    Savitha

Posting Permissions

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