Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

help using table of number

 
Matt Kohanek
Village Idiot
Ranch Hand
Posts: 484
Java jQuery Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?


edit:

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.
 
Matt Kohanek
Village Idiot
Ranch Hand
Posts: 484
Java jQuery Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Matt,

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.
 
Matt Kohanek
Village Idiot
Ranch Hand
Posts: 484
Java jQuery Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic