I have some n No. of volatile tables in a SP. is there any scenario where a Volatile table will be dropped automatically? like wen i run out of spool space that n - (n-1)th table will be dropped. Because i am creating so many volatile tablesbut wen i try to use it throws error saying that this volatile table does not exist and that too randomly (First time it might be 1st table next time some other table). Any thoughts.
When you run out of spool during the CREATE VOLATILE TABLE this specific table will not be created, but otherwise VTs will not be dropped automatically, only when the session logs off.
If you have lots of VTs and a complex SP, maybe creation/access is not always in the correct order?
As you said you have not mentioned the number of volatile tables you are trying to create in that SP.
As far as i know, 64 volatile tables can be created at one time.
@Dieter When you run out of spool during the CREATE VOLATILE TABLE this specific table will not be created -- It wont even throw error, it will just skip??
If you have lots of VTs and a complex SP, maybe creation/access is not always in the correct order? -- How can we make sure that it runs as we intended?
@Asad I am well within the limits of 64...
Of course it will throw an error, maybe you catch the error with a CONTINUE handler?
It's up to you to implement the correct order, it's your source code :-)
The maximum number of VTs is way more than 64, it's 1000 plus 2000 materialized Global Temporary Tables per session.
Thanks Dieter for your timely responses :-) How can we make sure that it runs as we intended? The reason for this question is - I have 7 VTs, i am first creating all the VT's and then inserting into that VT's from a permanent table; But during some insert statements its throwing an error as some VTs are not present.
Still i am confused why its throwing that error while inserting eventhough that VTs DDL was just above it! Any thoughts?