Declaring arrays .... 3

[mythologyrocks]

Hi all,

I am kinda new to Oracle. I'd like to create a table with one of the fields (say a numeric field) as an array. Any help is appreciated. Thanks in advance.

Regards,
A Beginner.

 

[SantaMufasa]

Myth (Sorry I can't tell if you are a "myth" or a "mythter" ).

As a beginner, probably the first rule you must learn in the "Relational" (read Oracle) World is, "You don't build a table (array) inside another table". This is strictly a two-dimensional world. To build a third dimension disobeys the first commandment (First Normal Form) of our "Relational God" (E.F. Codd).

The way we handle "an array inside and array" is to build another table with a link to the original table. (Since you did not divulge the contents you want in your array within a table, I'll contrive and example). Let's say that the numeric array you wanted to build is PULSE_RATE for each PATIENT. Instead of creating a PULSE_RATE array inside of the PATIENT table, We'd CREATE the following two tables:

CREATE TABLE PATIENT
(patient_ID number PRIMARY KEY,
 last_name  varchar2(40),
 et cetera)

CREATE PULSE_RATE
(ID number PRIMARY KEY,
 READING_DATE date,
 READING_VALUE number,
 PATIENT_ID references PATIENT(PATIENT_ID));

(To expand the usefulness of the second table, I'd change it to "VITALS", having the same columns plus READING_TYPE that would also let me store whether the reading was for pulse, respirations, blood pressure, weight, et cetera.)

So don't get trapped into multi-dimensional (beyond 2 dimensions) thinking if you want to be an Oracle aficionado/aficionada.

Cheers,

Dave
Sandy, Utah, USA @ 18:48 (19Nov03) GMT, 11:48 (19Nov03) Mountain Time

 

[mythologyrocks]

Hi,

Now does that mean the patiene_Id in table patient is an array? i.e. I can access it as patient_id(number)?

Regards,
Beginner....

 

[SantaMufasa]

Pictures are often better than words. Here is a partial "picture" of the PATIENT table:

PATIENT_ID LAST_NAME            FIRST_NAME
---------- -------------------- ----------------
    277304 Bridges              Dave
    277305 Rocks                Mythology
    277306 Hunt                 Dave

Here is a picture of the PULSE_RATE table:
        ID READING_D READING_VALUE PATIENT_ID
---------- --------- ------------- ----------
         1 15-OCT-03            74     277304
         2 20-OCT-03            79     277305
         3 26-OCT-03            75     277306
         4 19-OCT-03            80     277304
         5 31-OCT-03            79     277306

To shuffle them together, you can use this code:
select p.first_name||' '||p.last_name patient, reading_date, reading_value pulse_rate
from patient p, pulse_rate pr
where p.patient_id = pr.patient_id
order by last_name, first_name, reading_date;

PATIENT                                   READING_D PULSE_RATE
----------------------------------------- --------- ----------
Dave Bridges                              15-OCT-03         74
Dave Bridges                              19-OCT-03         80
Dave Hunt                                 26-OCT-03         75
Dave Hunt                                 31-OCT-03         79
Mythology Rocks                           20-OCT-03         79

5 rows selected.

Notice that in all these examples (tables and queries) that we see two dimensions: Columns and Rows. Notice also that PULSE_RATE is a third dimension of PATIENT, but we treat it as a linked two-dimensional table rather than an "array-in-an-array".


I hope this helps,

Dave
Sandy, Utah, USA @ 19:29 (19Nov03) GMT, 12:29 (19Nov03) Mountain Time

 

[mythologyrocks]

Thx a lot dave...I got it.

Regards,
A new-comer...

 

[CRoberts]

It is possible to create an array within a table. Never say never is rather strict, I would say that in some rare cases a VARRAY makes sense and can be quite convenient. If you anticipate referencing the elements of the array, then go with a relational table. But, if the list of numbers is rather small (say always from 0 to 10) and you always want a query result such that:

COL1 COL2 COL3 COL4 List of Integers

then VARRAY does made sense and get rid of a lot of messy outer joins needed to produce a single row query. I believe that as a rule, ralational tables should be normalized, and only in very rare cases should a VARRAY be used.

 

[ddrillich]

As a proponent of the ANSI/ISO SQL 1999 standard, which Oracle 9i supports, I would use the natural join syntax to rewrite Dave's select statement as:

select first_name ||' '|| last_name patient, reading_date, reading_value pulse_rate
from patient natural join pulse_rate
order by last_name, first_name, reading_date

Please note that the where clause is no longer needed for the join and table aliases are neither needed nor allowed.

Regards,
Dan

 

[SantaMufasa]

Dan, Cool !!! Purple Star, dude !

 

[SantaMufasa]

Dan,

As a followup to your previous code, here is the "old" way:

select name, last_name
from s_dept, s_emp
where s_emp.dept_id = s_dept.id;

NAME                      LAST_NAME
------------------------- -------------
Administration            Garcia
Operations                Ngao
Sales                     Nagayama
Finance                   Quick-To-See
Administration            Ropeburn
Operations                Urguhart
Operations                Menchu
Operations                Biri
Operations                Catchpole
Operations                Havel
Sales                     Magee
Sales                     Giljum
Sales                     Sedeghi
Sales                     Nguyen
Sales                     Dumas
Operations                Maduro
Operations                Smith
Operations                Nozaki
Operations                Patel
Operations                Newman
Operations                Markarian
Operations                Chang
Sales                     Patel
Operations                Dancs
Operations                Schwartz

Here is the "new" way:

select name, last_name
from s_emp natural join s_dept;

NAME                      LAST_NAME
------------------------- ---------------
Finance                   Havel

Whycome it displays only one row?

Dave
Sandy, Utah, USA @ 04:15 (20Nov03) GMT, 21:15 (19Nov03) Mountain Time

 

[ddrillich]

Dave,

The new way to write:

select name, last_name
from s_dept, s_emp
where s_emp.dept_id = s_dept.id

would be:

select name, last_name
from s_dept d
join s_emp e on e.dept_id = d.id

It is so, because unlike the previous case, the join here is not done on common column names.

Dan

 

[SantaMufasa]

Dan,

Okay, I can accept the alternate code. A couple of issues, however:

* Actually, the adjusted code is more verbose (less succinct) than the "old way".
* Certainly not backward compatible if you are writing generalised code for multi-platform use.
* Why, In my previous attempt, did my "unadjusted code" display one row from the "middle" row of the table?

Dave
Sandy, Utah, USA @ 05:26 (20Nov03) GMT, 22:26 (19Nov03) Mountain Time

 

[sem]

Since Oracle 8 this database os no longer pure relational, but rather object-relational. So you may

SQL> create type ListOfNumbers as table of number
2 /

Type created.

SQL> create table listsOfNumbers(entry ListOfNumbers)
2 nested table entry store as list_det
3 /

Regards, Dima

 

[dbtoo2001]

I interpreted "myth" or a "mythter"'s original post as a question about nested tables (or varrays.) I don't like nested tables myself, but based on Santa's example (and Dima's)...

REM Nested Tables Example
REM Create the object type with the fields that will be nested within the parent table
CREATE OR REPLACE TYPE "PULSE_RATE" AS OBJECT
( READING_DATE date,
READING_VALUE number
);

Rem Create a table object for those fields
CREATE OR REPLACE TYPE "VITALS_NT" AS
TABLE OF PULSE_RATE;

Rem Create the parent table with the nested table object, make sure you tell oracle
REM how to store the nested table object.
CREATE TABLE PATIENT
(patient_ID number NOT NULL,
last_name varchar2(40) NULL,
first_name varchar2(20) NULL,
vitals SYSTEM.VITALS_NT NULL)
NESTED TABLE VITALS STORE AS PATIENT_VITALS;

REM Insert some values
INSERT INTO PATIENT VALUES(2004,'Mufasa','Santa',VITALS_NT(PULSE_RATE(sysdate,85)));
INSERT INTO PATIENT VALUES(2001,'Too','DB',VITALS_NT(PULSE_RATE(sysdate,81)));
INSERT INTO PATIENT VALUES(2001,'Too','DB',VITALS_NT(PULSE_RATE(sysdate,0)));

REM don't forget to commit them
commit;

REM Select them
select patient_id,last_name,first_name,b.reading_date,b.reading_value
from patient a, table(a.vitals) b
order by patient_id, last_name,first_name,b.reading_date
;

====
Output Example:
PATIENT_ID LAST_NAME FIRST_NAME READING_D READING_VALUE
---------- ---------------------------------------- -------------------- --------- -------------
2001 Too DB 20-NOV-03 81
2001 Too DB 20-NOV-03 0
2001 Too DB 20-NOV-03 81
2001 Too DB 20-NOV-03 0
2002 TOO DB 20-NOV-03 85
2004 Mufasa Santa 20-NOV-03 85
2004 TOO DB 20-NOV-03 85

Personnally, I prefer SantaMufas's method of Normalizing the data.
-david

 

[Velena]

Hi all,

I would like to have master and detail blocks in Oracle Forms 9i and do query by some fields in master and detail blocks. I have control block which store search variables. What is the best way for such filtering?

Regards,
Velena

 

[sem]

You may copy appropriate values to queryable items or even rebuild DEFAULT_WHERE completely in PRE-QUERY trigger.

BTW, your question is probably an off-topic, because it neither relates to this thread nor to this forum at all. I'd recommend you to ask Forms-related questiuons in Oracle Developer forum.

Regards, Dima

 

[Velena]

Thanks, Dima. I am sorry about putting my question in wrong place. Your answer gave my some guidence in Forms triggers.
Velena