Can a table contain data arranged in two dimensions, and accessible
through a pair of integer subscripts?

I can live with 1-dimensional arrays, too.

TIA,

-RFH

On Aug 22, 4:07*pm, Ramon F Herrera <ra... (AT) conexus (DOT) net> wrote:
Bonus question:

I believe it is possible to save an arbitrary file (let's say an MS
Word or PDF document) in Oracle. I'd appreciate the details on that.

TIA,

-Ramon

On Aug 22, 4:09*pm, Ramon F Herrera <ra... (AT) conexus (DOT) net> wrote:

snip

asktom.oracle.com ... which also applies to your first question.

On Aug 22, 4:46*pm, John Hurley <johnbhur... (AT) sbcglobal (DOT) net> wrote:

Thanks, John. That is indeed a helpful resource.

In reference to arrays (or VARRAYs) I have a question for you folks
out there. According to the examples that I read, a VARRAY can be used
when there is a small number of items. The example mentions a person/
organization with a variable number of phone numbers, of to 6.

What about a larger number of items? In that case, is it:

- inefficient
- inelegant
- undesirable
- not recommended

to use VARRAYs perhaps?

The characteristic that I value the most about arrays is the ability
to iterate with an integer(s). Also important is the fact that if an
item is in position 52, I can rely on it being there next time.

TIA,

-Ramon

On Aug 23, 2:39*am, Ramon F Herrera <ra... (AT) conexus (DOT) net> wrote:
I meant "up to 6"

-RFH

On Aug 23, 10:39*am, Ramon F Herrera <ra... (AT) conexus (DOT) net> wrote:
Read up on nested tables and associative arrays in the docs for your
Oracle release. Note that true arrays (associative arrays aka INDEX BY
tables) are only available in PL/SQL. VARRAYs are somewhat limited
because they have finite predeclared capacity (you must declare
maximum VARRAY size,) and nested tables are unordered unless you
include an index column, always ORDER BY that index and maintain it
yourself. VARRAYs are stored in LOBs, so they become a bit less
efficient as they grow, but not dramatically.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Ramon,

Generally you should use side tables with referential integrity to store
such data.
Let's use your phone number example.
It is trivial for the DBMS to reverse lookup a name from a phone number
if the data is stored in relational format: (empid, number)
If you store your phone-numbers in an array there will be no indexing,
no statistics, no detection of duplicate numbers.

ARRAYs belong into the world of procedural logic, not relational, IMHO

Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

On Aug 22, 4:07*pm, Ramon F Herrera <ra... (AT) conexus (DOT) net> wrote:
Thanks to everyone for their help...

I would like to provide a specific example of what I am trying to
accomplish. Let's say I run a circus and every day I get the totals
sold in a given week.

Categories are: ticket sales, cotton candy, souvenirs, etc.

My application needs to display the sales like this:

Ticket Sales
8/22/2009: $8492.10
8/15/2009: $9024.31
8/8/2009: $7893.03
8/1/2009: $8945.67

People keep on recommending the SQL paradigm, but the above looks to
me like something that must be handled by arrays.

I need to display the 6 most recent weeks and I keep on pushing the
most recent sales figures at the top, while I roll down and eventually
drop the oldest data. This kind of think MUST be handled with an
index, such as week-1 or week-2.

If I followed the relational model, I would need to hardcode records
such as:

CURRENT_WEEK
WEEK_MINUS_1
WEEK_MINUS_2
etc.

That would be a nightmare to code.

So, the question is how to implement the records (with arrays?) for a
rolling scenario like the above described.

Thanks!

-Ramon

On Aug 31, 9:02*pm, Ramon F Herrera <ra... (AT) conexus (DOT) net> wrote:
This sounds like a job for analytics. They're built into the engine,
optimizer understands them. Look at the docs, orafaq, asktom for
many, many examples.

See here: http://www.oracle.com/technology/obe.../outerjoin.htm

You might remember to state what you are trying to accomplish at the
start of a thread, rather than ask about how a more primitive
technology would do it.

jg
--
@home.com is bogus.
Is it possible to not be able to shift into neutral while the
accelerator is jammed wide open?
http://www3.signonsandiego.com/stori...ug-/?uniontrib

On Sep 1, 12:27*am, joel garry <joel-ga... (AT) home (DOT) com> wrote:
Thanks Joel.

However, I am not sure that your suggestion is what I need:

- My data is not sparse
- I am not comparing anything

Assume that instead of dollar amounts I have the "Plate of the week".
No software can compare dishes. My interest is in shifting a large
number of weeks, several years.

The intrinsic nature of the data is tabular, let's say an array of 156
entries (3 years). It would be really easy to shift with indices.

-Ramon