Solved

How to maintain data in temporary tables oracle 11g

Posted on 2014-07-18
10
1,262 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Errors 11 87
Oracle 11gR2 Middleware: multiple domains with individual admin servers? 4 44
Oracle Mulit-site configuration 28 73
DB Shutdown Automatically 11 32
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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

734 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