Advice: Global Temporary Tables, Collections ... ?

Posted on 2007-12-07
Last Modified: 2013-12-07

I need a little bit of advice.  Let me start by saying that I am fairly new to SQL and PL/SQL, so dont be too harsh ;)

I am going to provide a little bit of background here in case my approach could be improved on this.
The situation is that I have an app that allows users to create Processes as a sequence of steps from Start->Finish.  There may be dozens of steps with multiple paths between the start and finish steps of the process.  Each step has an estimated duration in # of days, so some Processes may take 10, 20 or 60 days to complete etc depending on the number of steps, their relative durations and the available paths between the first and last steps.

My objective has been to create a report to show the maximum delay from any step in the process to the finish step.

To do this, I have created a PL/SQL procedure that accepts three parameters  Process ID, Start Step ID and Finish Step ID.

The procedure is recursive  it starts with the Finish Step of the given process and progresses towards the Start step, calculating the maximum delay for all steps along the way.

Currently I use five DB tables (Oracle 10g R2).

Table 1:          End Result (three columns of Numbers)
Table2  5:      Temporary tables (all have between 3 and 6 columns of numbers)

This Procedure currently works fine for 1 process at a time.

However the report may be called concurrently by several users which would lead to inconsistent results.  Furthermore, each time I call this procedure I first truncate the temporary tables.

So this is where I need the advice&

I have been trying to read up on several possibilities including global temporary tables, collections, records and objects etc. and now that my mind feels saturated with this maze of information I would really appreciate a pointer in the right direction.

What makes it difficult for me is that the procedure is recursive, so any structure (GTT, array) needs to be accessible on each call.

Any (constructive) advice would be most welcome.

If possible, please give me some examples of the structure and syntax or point me to a URL.
Question by:cgosney42
  • 4
  • 2
  • 2
  • +1
LVL 34

Expert Comment

ID: 20427959
I think you are on the right track with the global temporary table.

As what you are doing is recursive, it is all within the same session.  Therefore, the global temporary tables should be visible to each call.  If your procedures do commits, they you will want to be sure to set on commint preserve rows on the tables.
LVL 34

Accepted Solution

Mark Geerlings earned 125 total points
ID: 20427960
I think global temporary tables will work very well for this.  (But I should admit, I am not a master of arrays or collections.)  GTTs will handle the multiple, concurrent users automatically with no problems.  Also the syntax for working with GTTs is exactly the same as the SQL syntax for standard tables.  You can: select, insert, update, delete, use cursors in PL\SQL, etc. with GTTs.  The main difference: most performance overhead is avoided.  You just have to remember that GTTs can never share data across multiple sessions (but I think you want that functionality, so multiple, concurrent users don't interfere with each other) and the contents of the GTTs will be cleared automatically either: when the session does a commit (this is the default) or when the session ends (if you add the clause "on committ preserve rows") when you create the global temporary tables.  One other note: when you create a global temporary table you *DO NOT* specify a tablespace or any storage parameters.
LVL 27

Expert Comment

ID: 20428318
You say any structure needs to be recursive; do you mean to say that every recursive call of your procedure has to have a "new" "structure". I.e. you do not want to share the same structure in the recursion?
If that is the case; GTT cannot be used here. Because the data within the GTT is valid until the end of the session.(Provided it is created with preserve rows on commit option).

You have to use a locally declared array to process the intermediate data. DONT use a global array declared in a package.

>> some examples of the structure and syntax
To provide you an example, we need at lease a pseudo code of what you are doing in the procedure.

Author Comment

ID: 20428727
Thanks for your help guys.
@sujith80 - I think you misread what I wrote: "What makes it difficult for me is that the procedure is recursive, so any structure (GTT, array) needs to be accessible on each call."

What I meant was that the since the data of all five current tables needs to be available to each call of the procedure, it can't be declared within the procedure (local).

So if GTTs are the correct approach I would like to clarify something.
My report is a SQL query of the current resultant table (Table 1).

If I implement GTTs, will each initial call to the procedure from my SQL Code have its own memory space?  i.e. each time my SQL code calls PROC(PROC_ID, START_ID, FINISH_ID) with new values, is this regarded as a new session and thus all 5 tables will be effectively empty?

Futhermore, since PROC is recursive, is each recursive call within PROC regarded as the same session?

I hope this makes sense.  If not, I can give some pseudo code.
LVL 27

Expert Comment

ID: 20429119
>> this regarded as a new session
No, a session starts once you connect to oracle. Throughout the execution of the code you are connected to the same session.
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails


Author Comment

ID: 20429292
">> this regarded as a new session
No, a session starts once you connect to oracle. Throughout the execution of the code you are connected to the same session."

Now I am confused.
My report is an SQL query which calls PROC.  PROC continues to call itself for all steps in the process, finally populates Table1 and control returns to the SQL query to complete the report.

User 1 and User 2 on my app may run this report at the same time for different processes.
or User 1 may run the report twice for different processes.

If I replace my current tables with GTT's and user 1 initiates a report, does start a session?  Does this finish when the SQL query is finished and the report is generated?
Or if User2 runs the report will the data be inconsistent?

If they share the same GTT, then this is not suitable for my needs.  Do you have a better suggestion?
LVL 34

Expert Comment

ID: 20429568
A temporary table is a distinct table for each session.  The name of the table is the same, however there is a separate copy of the table for each session.

In the case of user1 vs user2, then there will not be contention.  If user1 is connected twice and runs it from different sessions then again, there is not contention.

Within the process that populates the table, you will want to do a delete on the temporary table before doing anything.  This will clear the table if the report is run more than once in the same session.  If I recall correctly a truncate will not work if the table has not been used yet in the session, but the delete will.
LVL 34

Expert Comment

by:Mark Geerlings
ID: 20429658
Two different Oracle users will definitely *NOT* have any contention for data in global temporary tables.  Oracle will allow each session to see *ONLY* its own copy of the global temporary tables.

I agree with johnsone's recommendation that the first step of your procedure should be a delete of the temporary table(s) used in the procedure, especially if they were created with "on commit preserve rows".

Author Comment

ID: 20442856
I ended up using GTT's for the Temporary tables (2-5) explained in the question.
I needed to use static table for CP since the data in a GTT table is lost when the procedure finishes.  I added a column to CPP to mark the steps unique to each process per report, so that they could be deleted at the start of each call.  This also ensured that other data would not be corrupted.

Thanks again for all your help with this.

Author Closing Comment

ID: 31413379
Thanks :)

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
JDeveloper 12c for 32 bit 4 34
SQL query of Oracle 10g database. 8 57
T-SQL Convert to PL/SQL 23 61
Schema creation in Oracle12c 6 23
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now