Hello. In my package spec I have a global variable declared like this:
NUMBERTABLE is a type I have declared as a TABLE OF NUMBER;
Then in the body I am trying to use that global variable in a FOR LOOP:
But the code is having problems and when I print out sqlerrm it is:
ORA-06531: Reference to uninitialized collection
Can anyone help me out with this?
Here is the main problem I am trying to solve:
I need something that is available globally within one sql package that can hold a set of values. That sql package will make calls to other sql packages, so this global thing needs to hold these values between calls to those packages too. Then once the values get used within the original package I need them to be erased.
I know I could just create a table with one column but I would rather not if possible.
True wisdom is in knowing you know nothing - Socrates
Sometimes you just gotta go back and re read basic tutorials :\
But there is a new problem - this is not holding my values, so whenever my original package that contains the type calls another package this must be getting reset? So is my only option to just create a table?
package variables are per-session; changes made by one session are not seen in another session. This might be an issue especially if you use connection pool. If you need to share data between sessions, you need to use either a table, or a global application context (I don't have any experience with the latter).
Of course, getting right the logic of data sharing across sessions might be quite tricky.
So turns out I could use the global variable type table of numbers...
I had two problems
1 - did not initialize it correctly as stated in a post above.
2 - I did not use the EXTEND method to give it room for values. I was adding my numbers to it in my for loop (and the for loop of course has a catch all exceptions block that ate all errors...). I just was not using .EXTEND first.
Two problems I have had in the past too, ha, hopefully this time I learn my lesson