Global Temp table question 2

[Wootoid]

So, I am a bit new to the Oracle world, and I want to know if I am approaching an issue I have properly by using a temp table, or if there might be a better approach. I am trying to improve a few processes that have recently been given to me that are just not terribly well done.

Currently:
We have a query that selects a large number of e-mails addresses of our customers, and sends a notice to them that they have information that has been recently posted to their account and is now viewable.

We then have an insert statement that uses the same criteria to update a table that records who we should have sent the notice to for auditing purposes.

The problem with this is if there are changes that occur between the to statements running, we may either e-mail one customer and not add them to the audit table, or add someone to the table that was not e-mailed.

What I would like to do:
I was thinking of creating a temporary table that an initial insert would populate and then send the emails from a query of the temp table. After the emails are sent then update the audit table with the info in the temp table. After the audit table is populated, the info in the temp table should be deleted. This should give me a static set of data between the email and update, but is this the best approach to this issue?

We do have a fair number of very similar processes that update the same audit table, and I figured, if I set it up properly, that they could all use the same temp table.

 

[LKBrwnDBA]

As long as you remember that the data in a GTT only is available to the particular session and lasts until the session ends, then it's ok.

This means that data inserted into a GTT is not available across sessions. If you need otherwise, you could create permanent table to perform the same function of holding temporary information.







----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Wootoid]

Thanks for the response.

I am fine with the data only being available to that particular session. Basically I am looking for a quick snapshot of the data, use it, record a portion of it to another table, then delete the snapshot. I just want to make sure that I am not doing something pointlessly complicated or something that will needlessly task the database's resources when there might be a better approach.

 

[SantaMufasa]

Frankly, Wootoid, your example is an excellent use for Global Temporary Tables.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Wootoid]

Thank you both for the help, now to put this into practice.

 

[karluk]

I don't see anything wrong with your approach, but it strikes me as being unnecessarily complicated. You are describing a situation that requires a so-called "serializable" isolation level. The simplest way to implement this would be to add a "set transaction isolation level serializable;" to your code immediately prior to beginning your send-email-update-audit-table process. As long as you don't have any commits in your code before updating the audit table, that would achieve your purpose. Instead you are looking at building a temporary table as a work-around. I would try setting the isolation level to serializable first, and only proceed to alternatives if that approach proves unsatisfactory.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:42560223798158

 

[Thargy]

Can anyone see anything wrong with a plain old transaction here?

Updating a table with an INSERT is new to me, but hey, I'm always willing to learn.

Do the update and audit in a single transaction. Errors ought to roll back, right?

Am I missing something here?

Regards

T

 

[Beilstwh]

Another way would be

for pnt in (select email,id_num from my_table for update) loop;
send_email(pnt.email);
update my_table a
set a.flag = 'Y'
where a.id_num = pnt.id_num;
end loop;
commit;

A cursor is a point of time thing and will not be effected by new rows being added.

Bill
Lead Application Developer
New York State, USA