am i correct in saying though, that if you are passing data from jdbc to oracle and you need to pass arrays or elements or records then a varray of objects is the only way to do it?
This would be a good use of varrays over other types.
plsql tables and nested tables do not work in this case.

Please correct me if this is not true though!

Dear Tom,
How would you do that?I mean passing rows of global temp table to procedure or function anything like that.Would you
use REF CURSOR for that?Please show the p-code.
Bye!

There is a paper going around our office which suggests that using varrays to store arrays of integer values in a DB (think along the lines of lots of days of meter readings - a reading every 5 minutes) would be more efficient than using NUMBERs.

The argument centralises around the disk space saving of using these types.

My opinion is that we would lose soo much more than we gain (developer time, concurrency, speed). Plus we could gain the space benefits by defining appropriate tables (with NUMBERS) and compressing these (gaining the compression on the primary key).

What are your thoughts?

Why do you not use VARRAYS for persistent storage?

I agree with you wholeheartedly, especially about the SQL friendly comment

An IOT (your suggestion) can even be nicely partitioned!

I have the book, but I still haven't read it cover to cover ...it gets used as more of a reference (by both me and the projects I work on).

Tom:

Does key compression in an IOT work the same as compression in an index i.e. all entries remain compressed even if not inserted via direct path. In a heap table rows are only compressed if inserted in direct path mode or CTAS.

Hello Tom,
Regarding varrays,What can be the fastest way to remove the
contents of a varray that is the part of the normal relational table?For example
SQL> create type skills_va as varray(5) of varchar(30);
SQL> create table empl(ename varchar(30),skills skills_va);

I would like to delete the contents of a varray that belongs to a single row?
Any help you can provide?

 

Dear Tom,
Could you please show some updates or deletes for Varray
enclosing an object type?
I have given some sample types used by me.
Please do reply.

SQL> create or replace type subj_ty as object(
  2* sub_name varchar2(30),Grade char(1))

Type created.


SQL> create or replace type subj_va as varray(5) of subj_ty
  2  /

Type created.


SQL> create table students(sname varchar2(30),sinfo subj_va);

Table created.

SQL> insert into students values('Joe',subj_va(subj_ty('English','A'),subj_ty('Economics','B'),
  2                               subj_ty('Maths','C'),subj_ty('Science','A'),
  3                               subj_ty('History','B')));

1 row created.

SQL> insert into students values('James',subj_va(subj_ty('English','B'),subj_ty('Economics','A'),
  2                               subj_ty('Maths','B'),subj_ty('Science','A'),
  3*                              subj_ty('History','D')))

1 row created.

SQL> commit;

Commit complete.

I would like to change the contents of Varray.Any methods 
you may provide?

 

Hi Tom

Please consider the example below.

Item Code: Item1
Item Desc: Item Name

Parameter Val1 Val2 Val3 Val4 Val5

Param1 1 2 3 4 5
Param2 1 2 3 4 5
Param3 1 2 3 4 5

For each item there is an array of parameters and for each parameter there is an array of values. The user should have an ability to insert and update records. I was planning to use VARRAY/NESTED TABLE to implement this design. I agree with you in that if I use these features it will be very difficult to manipulate data. If I choose the relational method I will have to store item details i.e Parameter and its values in a seperate table. So the records in the detail table will look like:

Item1 param1 1
Item1 param1 2
Item1 param1 3
Item1 param1 4
Item1 param1 5

and so on....

Can you think of a better way of doing this?

Please advise.

Hi Tom,
How to update the varray stored as a database table column?
I tried various ways but nothing seem to work.

SQL> create or replace type nums_va as varying array(10) of number
  2  /

Type created.


SQL> create table t(x varchar2(20),y nums_va)
  2  /

Table created.

SQL> insert into t values('First rec',nums_va(1,2,3,4))
  2  /

1 row created.

SQL> insert into t values('Second rec',nums_va(11,12,13,14))
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> update TABLE(select y from t)
  2  .
SQL> 
SQL> 
SQL> update TABLE(select y from t where x = 'First rec')
  2  set column_value = column_value+30
  3  /
set column_value = column_value+30
    *
ERROR at line 2:
ORA-25015: cannot perform DML on this nested table view column


SQL> ed
Wrote file afiedt.buf

  1  update TABLE(select y from t where x = 'First rec')
  2* set column_value = 31 where column_value = 1
SQL> /
set column_value = 31 where column_value = 1
    *
ERROR at line 2:
ORA-25015: cannot perform DML on this nested table view column



SQL> ed
Wrote file afiedt.buf

  1  update TABLE(select y from t where x = 'First rec')p
  2* set p.y = 51 where p.y = 1
SQL> /
set p.y = 51 where p.y = 1
                   *
ERROR at line 2:
ORA-00904: "P"."Y": invalid identifier


How to do the update?? 

SQL> select * from t
  2  /

X
--------------------
Y
--------------------------
First rec
NUMS_VA(1, 2, 3, 4)

Second rec
NUMS_VA(11, 12, 13, 14)



SQL> UPDATE t
  2     SET y = CAST (MULTISET (SELECT COLUMN_VALUE + 30
  3                               FROM TABLE (SELECT y FROM t WHERE x = 'First rec')
  4                            )
  5                   AS nums_va
  6                  )
  7   WHERE x = 'First rec'
  8  /

1 row updated.


SQL> 
SQL> select * from t
  2  /

X
--------------------
Y
-------------------------------------------------------------------------------------
First rec
NUMS_VA(31, 32, 33, 34)

Second rec
NUMS_VA(11, 12, 13, 14)


SQL> rollback
  2  /

Rollback complete.


SQL> UPDATE t
  2     SET y = CAST (MULTISET (SELECT DECODE (ROWNUM, 2, COLUMN_VALUE + 30, COLUMN_VALUE)
  3                               FROM TABLE (SELECT y FROM t WHERE x = 'First rec')
  4                            )
  5                   AS nums_va
  6                  )
  7   WHERE x = 'First rec'
  8  /

1 row updated.


SQL> select * from t
  2  /

X
--------------------
Y
-------------------------------------------------------------------------------------------
First rec
NUMS_VA(1, 32, 3, 4)

Second rec
NUMS_VA(11, 12, 13, 14)


SQL> rollback
  2  /

Rollback complete.



SQL> UPDATE t
  2     SET y = CAST (MULTISET (SELECT DECODE (COLUMN_VALUE, 3, COLUMN_VALUE + 30, COLUMN_VALUE)
  3                               FROM TABLE (SELECT y FROM t WHERE x = 'First rec')
  4                            )
  5                   AS nums_va
  6                  )
  7   WHERE x = 'First rec'
  8  /

1 row updated.

SQL> select * from t
  2  /

X
--------------------
Y
-------------------------------------------------------------------------------------------------
First rec
NUMS_VA(1, 2, 33, 4)

Second rec
NUMS_VA(11, 12, 13, 14)


SQL> rollback
  2  /

Rollback complete.



Best regards 

Hello Sir,

Could you please tell us why do we have to use the EXTEND function for Nested tables and Varrays.
Is it because the internal implementation of these is using Linked List. Thus when we say extend
it does a malloc or calloc (to allocate the memory). If it is so does it mean memory allocation
happens at runtime for them. The EXTEND is not applicable for Index by tables does that mean that
memory is allocated at compile time for it.

Regards
Sandeep