Solved

How to maintain data in temporary tables oracle 11g

Posted on 2014-07-18
10
1,210 Views
Last Modified: 2014-08-01
Hi ,

We have an application , in that we have the reports generation option . We have an report for generating products brought by the customers and their price , product groups info etc. As part of the process , we insert the customers to whom the data needs to be generated in an temporary table , from this table within the procedure , we process the data and calculate the data for the report send it as an ref cursor back to the java program.
Now we have an additional requirement , we need to send the customer branch and fee details as an separate report  and send to the same java program.For this new report , we need the customer data stored it the temporary table to be re-used for the second report.


Proc 1 : Inserts data into the Temp1 temporary table
Proc2 : Fetch the customer details from temp1 table , and then get the branch and fee details

Can any one help me , is there any means to store the data inserted in temp1 table from proc1 and get the data from proc2.

First proc1 is called , it inserts the data into temp1 table , processes and send the data to java app.  After getting data from proc1 , proc2 is invoked , which needs data inserted from proc1.

Any help in this regard is really appreciated.
0
Comment
Question by:sam_2012
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40204460
Need more information:
Is this a global temporary table?
If so:  
    Can the procs run in the same session?
    Was the global temp table created to preserve on commit?

If not:
Is there some unique ID that can be passed to proc2 by the app that calls proc1?
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 200 total points
ID: 40204553
As slightwv states: if the table is a global temporary table and the procs run in the same session -- then the table needs to be created with the option "preserve on commit".

Otherwise, you may need to convert the table to "permanent" and use some unique id to identify the transaction(s).
;)
0
 

Author Comment

by:sam_2012
ID: 40204640
I think that is better approach to make the temporary table to preserve rows on commit .  
My doubt is ,

for each session if i make temporary table to preserve rows on commit, then I need to truncate the data in the table for each request , insert the data , call proc2.

Will there be any impact if i follow this approach.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40204647
>>then I need to truncate the data in the table for each request

ONLY if the next request is in the same session (database connection).

For example:
connect to database
call proc1
call proc2
...
call proc1
call proc2
disconnect

If the app does the following then there is no need to truncate the table:
connect to database
call proc1
call proc2
disconnect
...
connect to database
call proc1
call proc2
disconnect
0
 
LVL 13

Expert Comment

by:magarity
ID: 40204925
Oracle's temporary tables can be a hassle. What exactly is the problem with making a normal table and truncating as needed?
0
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.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40204932
>>What exactly is the problem with making a normal table and truncating as needed?

You cannot truncate a normal table since it is likely more than one person can run this at the same time.

If you move to regular tables, you will need some report_unique_id and delete after the report.
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 300 total points
ID: 40225477
Global temporary tables in Oracle are a good way to hold information that is used only briefly.  And, you don't need to worry about multiple users or programs using the "same" global temporary table at the same time.  Oracle will make sure that each separate database session sees only its own copy of the global temporary table.

If you do run multiple jobs in the same session, then yes you will have to clear out the global temporary table in between.  So, your program should do these steps in this order:
1. clear the global temporary table (use "delete" not "truncate").
2. run your Proc1
3. run your Proc2
0
 
LVL 13

Expert Comment

by:magarity
ID: 40232210
For a behind-the-scenes process like loading up a report table, users shouldn't be looking at this table so I would just make it a normal table and truncate when reprocessing.  Then you don't have to worry about the peculiarities of temporary type tables such as process 1 and process 2 coming from different sessions and no being able to see each others' records, etc, etc.  All you have to do is only grant permissions to the process account and not any users.  This also helps with debugging any problems as the data will sit in the table where an administrator can look at it if there is some suspect values in the final report.
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 300 total points
ID: 40232635
The disadvantage of using a "normal" table to hold this data is the fact that then more complexity is needed to handle the possibility of two or more users trying to run this same report/process at about the same time.  You then need an additional column added to this table that can hold a unique value for each run, and queries from this table can't simply select everything - they need to select only the data for the current run.  Also, that means you can't use "truncate" to clear this table.  You have to use a "delete" command at the end of each run to delete just the records for that particular run, but leave other records alone.

This approach adds a lot more I/O (since individual "deletes" cause a lot more disk I/O than a single "truncate") and it will cause the table to grow over time, even though it usually contains no rows.
0
 

Author Closing Comment

by:sam_2012
ID: 40233900
Thanks , I went with the global temporary with on comment delete rows and ensured from app , that all transactions i.e both the procs are called in the same session.
0

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

747 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

12 Experts available now in Live!

Get 1:1 Help Now