How to maintain data in temporary tables oracle 11g

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.
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
MikeOM_DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam_2012Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
>>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
magarityCommented:
Oracle's temporary tables can be a hassle. What exactly is the problem with making a normal table and truncating as needed?
0
slightwv (䄆 Netminder) Commented:
>>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
Mark GeerlingsDatabase AdministratorCommented:
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
magarityCommented:
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
Mark GeerlingsDatabase AdministratorCommented:
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
sam_2012Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.