| creating temperory table in oracle [message #373357] |
Thu, 12 April 2001 08:53  |
Farhan
Messages: 5
Registered: April 2001
|
Junior Member |
|
|
Hello everyone...
i m facing a problem regarding creating a temperory table in oracle..
Let me explain the scenario..
What i need to do is create a temperory table in a stored proceedure fill it up at run time and had it returned in to the FrontEnd which in my case is JAVA.
via a resultset.which is been performed perfectly..now the problem comes up with the temperory table..if i use the temperory table fill it up and runtime and return the data selected from it (which is stored in the cursor at the first place)..as soon as it gets to the front end the resultset(cursor in database language) it gets emptied...but if i do the same thing with a proper base table (not a temperory table) then it runs perfectly fine...the problem i think is that before the resultset gets to the frontend the data is being emptied from the temperory table...
Can anyone comment on this.
and reply me at "sarwarfarhan@hotmail.com"
Take Care
Farhan.
|
|
|
|
| Re: creating temperory table in oracle [message #373373 is a reply to message #373357] |
Fri, 13 April 2001 08:51  |
Madhav Kasojjala
Messages: 42
Registered: November 2000
|
Member |
|
|
Hi Farhan,
I think the simple reason that is happening is you are not commiting the data.
I will dig further a little.
First what's a session?
Any client (SQLPLUS, Web Browser/Server, Any frontend like Form/Report/VB or any command Line call) making a successful connection with Database Server is called a Session and every time you connect from Client to Server a new session is made.
By default the fate of data which is inserted/deleted/modified in a session is decided by 2 ways either programatical control or the settings of calling tool.
For example SQLPLUS has settings as
SET AUTOCOMMIT ON, which if u run soon after u log in will commit all the data when u logout of session without executing commit/rollback.
Now coming to ur situation, I think somewhere
along the line between a) creating temp. table
b) loading data c) calling through procedure
d) loading in Java you haven't commited the data into temp table or session is getting reinitialized.
If u have unique id w.r.t. each user that accesses it, why don't u commit the data and delete it when u leave the session externally?
HTH
Madhav
|
|
|
|
| Re: creating temperory table in oracle [message #373407 is a reply to message #373357] |
Mon, 16 April 2001 00:02 |
Farhan
Messages: 5
Registered: April 2001
|
Junior Member |
|
|
Madhav:
Again thanks a lot for replying me up...
First of all..let me tell u that i have solved the problem..but not by creating temperory table...but by a base table and getting the data wipped out everytime for each session(using the session id from the front end)....i have used the session id..and added a column in the table too for that id...this is being done programmitically by us and i don't think so there is any role of temperory table...
do u agree..and the solution u provided was the same i have done..but in that case there is no role of temperory table right???its just a base table which we are making it behave like a temperory table (though its not)
Do u agree..??? or is there still anything i m missing...and one more thing in this case i m not even committing the data when returning the resultset and it works fine.
Will wait for ur reply.
Farhan.
P.S: Madhav do u have yahoo massenger or MSN..so its gonna be easy for me to communicate with and that would be a big help and thanks again.
|
|
|
|
| Re: creating temperory table in oracle [message #373410 is a reply to message #373357] |
Mon, 16 April 2001 08:23 |
Madhav Kasojjala
Messages: 42
Registered: November 2000
|
Member |
|
|
Hi Farhan,
Well! temperory table is a psuedo keyword used by us Oracle Mortals refering to any table that is purely used for temperory purposes and which is not part of physical database design of a normalized db structure.
Like I said, if u have same session throughout the chain of events from client to server then
all u need is filtering the data w.r.t. ur session id.
But yes, temperory table is actually a table that exists.
( U can use some Oracle Built ins to create, load and destroy the table on the fly too...)
HTH
Madhav
|
|
|
|
| Re: creating temperory table in oracle [message #373431 is a reply to message #373357] |
Mon, 16 April 2001 23:45 |
Farhan
Messages: 5
Registered: April 2001
|
Junior Member |
|
|
Well this discussion with u have really cleared my idea regarding temperory
tables...well what i further need to know that there is an option of
temperory table in ORACLE 8i..in 3 ways..and they say that the table is
though is base table..but the data filled in it is temperory...it means that
after a session ends the data is wiped out of it...i m not using this
functionality in my application as i have tried this and this is what i told
u about that the data is no more there when i return the cursor to the
resultset...though the cursor has the data in the Database but the resultset
does'nt when retrieved in JAVA interface...although if i used the above
feature the session id thingee would'nt be need anymore as the data is been
taken care of by the Backend itself..as it wipes it out for every
session...(it means every unique call to the database)...but i m afraid this
is'nt working...thats why i have to use the way u said..anyway my problem
has solved and thanks to u...so Madhav in my last mail to i asked about
communicating on any of the messaging software like Yahoo massenger or
MSN...if u could kindly give me ur id..if that is'nt a problem..this way i
would be able to clear most of my concepts..
Farhan.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
