Meaningless Drivel is fun!
The moose likes JDBC and Relational Databases and the fly likes help using table of number Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "help using table of number" Watch "help using table of number" New topic

help using table of number

Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 484

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
Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 484

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

Joined: Aug 22, 2010
Posts: 3733


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

Joined: Apr 04, 2009
Posts: 484

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
I agree. Here's the link:
subject: help using table of number
It's not a secret anymore!