Solved

How to maintain data in temporary tables oracle 11g

Posted on 2014-07-18
10
1,268 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 77

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 77

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
 
LVL 77

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 35

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 35

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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.

705 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