Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Temp Tables

Temp Tables

Temp Tables

We have a visual basic application on MTS connecting to a Oracle database through DB Link on sun solaris using ADO'S.The Oracle database has global temporary tables.When ever there is an insert or update on the tables a trigger fires to populate the temp tables.Occassionally we are getting an oracle error a saying "trying to access the transactional temp tables which are already in use". If we do the updates through oracle sql we are not getting that error, but when we are going through the VB Application we are getting that error and that too not all the times. Can some one help me find what might be the problem.

RE: Temp Tables

Not sure if this is your error but here goes a little explanation.

Temp tables are normally destroyed by a database when a connection is released.  Under MTS/Component Services Connection pooling actually doesn't release connections when the application calling the component in MTS releases the component  ie

now without MTS you would do this and when ComObjectA is done with the connection the connection is broken and oracle cleans up/drops the temp tables
Application ---> ComObjectA ----> ADO Connection ----> Oracle

but with MTS what happens is
App ---> MTS ---> ComObjA ---> ADO Conn ---> Oracle
         |  ^       |   ^
         |  |       |   |
         |  ---------   |

Those loops underneath is showing that ComObjA requests a connection from MTS via the connection pool.  When the object is down with the connection and sets it to nothing or it goes out of scope it is returned to the connection pool but this does NOT break the connection to Oracle.  Oracle still sees the connection as open, becuase it is from MTS and the ComObjA only borrowed it for a bit.  If a connection isn't requested for awhile MTS will destroy the connection and Oracle will clean up.

What does this mean?  Well your objects are faster because they don't have to wait to establish a connection every time....BUT temp objects don't get destroyed automatically.
So to fix this just make sure that you destroy the temp objects explicitly instead of relying on them going out of scope.

Hope this helps

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close