Updating a varray column in table

User_DUYMH

Hi Guys

I have a table with one of the column as varray and need to update that column particular index with a value.

*** Test Data***

CREATE OR REPLACE TYPE "VARR_50" AS VARRAY (1000) OF VARCHAR2 (50);

CREATE TABLE test_data

(

  user_id  RAW (16),

  attr_ids  VARR_50

);

insert into test_data values (sys_guid(), VARR_50('abc','pqr',null,null,null,null,null,null,null,null,null,null));

insert into test_data values (sys_guid(), VARR_50(null,null,null,null,null,null,null,null));

insert into test_data values (sys_guid(), VARR_50('xyz',null,'FOO',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null));

commit;

Now how to update column attr_ids, to have a value of 'FOO' at 7th index of varray for each record.

Expected O/P

Record 1- 'abc','pqr',null,null,null,null,null,'FOO',null,null,null,null

Thanks

mathguy

In Oracle SQL, you can't directly update individual elements of varray values in a column; and you can't COLLECT old and new values together into a new varray value (as you could, if you had used nested tables instead of varrays).

For ease of development and maintenance, it is probably easiest to create an "update varray" function, and then simply invoke it as needed. Something like this:

create or replace function updt_varr_50(varr varr_50, idx pls_integer, new_val varchar2)
  return varr_50
as
  new_varr varr_50 := varr;
begin
  new_varr(idx) := new_val;
  return new_varr;
/* exception section here */
end;
/

In particular, this will allow to handle exceptions as needed - what to do, for example, if an input varray has fewer than 7 elements? What to do if you pass in a string longer than 50 characters? Etc. You can test for these "exceptions" and handle them conveniently in the function, so that they don't upset an otherwise valid UPDATE.

Then your update can be as simple as

update test_data
  set  attr_ids = updt_varr_50(attr_ids, 7, 'FOO')
;

Note that this would work for more complicated updates - for example, both the index to update and the new value may depend on values in other columns in the same row of the base table. You would just have to write the corresponding expressions as arguments to the function, instead of hard-coding 7 and 'FOO'.

Then you can do fancier things if needed; for example, you could put the type definition and the function in a package, or you could encapsulate the function (create an object type, with the varray as the only member, and convert the function into a method).

mathguy

In Oracle SQL, you can't directly update individual elements of varray values in a column; and you can't COLLECT old and new values together into a new varray value (as you could, if you had used nested tables instead of varrays).

For ease of development and maintenance, it is probably easiest to create an "update varray" function, and then simply invoke it as needed. Something like this:

create or replace function updt_varr_50(varr varr_50, idx pls_integer, new_val varchar2)
  return varr_50
as
  new_varr varr_50 := varr;
begin
  new_varr(idx) := new_val;
  return new_varr;
/* exception section here */
end;
/

In particular, this will allow to handle exceptions as needed - what to do, for example, if an input varray has fewer than 7 elements? What to do if you pass in a string longer than 50 characters? Etc. You can test for these "exceptions" and handle them conveniently in the function, so that they don't upset an otherwise valid UPDATE.

Then your update can be as simple as

update test_data
  set  attr_ids = updt_varr_50(attr_ids, 7, 'FOO')
;

Note that this would work for more complicated updates - for example, both the index to update and the new value may depend on values in other columns in the same row of the base table. You would just have to write the corresponding expressions as arguments to the function, instead of hard-coding 7 and 'FOO'.

Then you can do fancier things if needed; for example, you could put the type definition and the function in a package, or you could encapsulate the function (create an object type, with the varray as the only member, and convert the function into a method).

Zlatko Sirotic

An ugly attempt from ancient times (when these programming languages were not yet born: Scala, Groovy, Clojure, Go, Rust, Dart, Kotlin, TypeScript, Swift ...):

https://community.oracle.com/tech/developers/discussion/comment/564433#Comment_564433

Solomon Yakobson

update test_data t
   set attr_ids = cast(
                       multiset(
                                select  case rownum
                                          when 7 then 'FOO'
                                          else column_value
                                        end
                                  from  table(attr_ids)
                               )
                       as varr_50
                      )
/

3 rows updated.

SQL> select  *
  2    from  test_data
  3  /

USER_ID                          ATTR_IDS
-------------------------------- ----------------------------------------------------------------------------------
1FF2FAEB6C2144149C719A312DA3AE70 VARR_50('abc', 'pqr', NULL, NULL, NULL, NULL, 'FOO', NULL, NULL, NULL, NULL, NULL)
41F9E0305F41489BA76CF3F9E8BFA956 VARR_50(NULL, NULL, NULL, NULL, NULL, NULL, 'FOO', NULL)
FD7ED64C5BEC478EBA534A4F9A8CF857 VARR_50('xyz', NULL, 'FOO', NULL, NULL, NULL, 'FOO', NULL, NULL, NULL, NULL, NULL,
                                  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

SQL>

SY.

Paulzip

@Zlatko Sirotic and @Solomon Yakobson I'm not sure you can rely on ROWNUM to always return the same value as the Varray index in a deterministic manner. ROWNUM will depend on how Oracle retrieves the data from storage, which might not be accessed sequentially matching the indices of your varray.

Solomon Yakobson

Yes, oracle never confirmed nested table rows are fetched in index order (although I never ran into a case where it wouldn't). I filed enhancement ages ago asking Oracle to add ordinality pseudocolumn, but ... It could be because OO relational database is (at least IMHO) a dead end. So yes, only PL/SQL solution would guarantee element order.

SY.

Paulzip

@User_DUYMH I'd probably make a wrapper class and use encapsulation to give the functionality you want.

create or replace type "VARR_50" as varray (1000) of varchar2 (50);


create or replace type varr_50_wrapper is object (
  Items VARR_50
, member function SetIndex(pIndex pls_integer, pValue varchar2) return varr_50_wrapper
)
/


create or replace type body varr_50_wrapper is
  member function SetIndex(pIndex pls_integer, pValue varchar2) return varr_50_wrapper is
    vSelf varr_50_wrapper := Self;
  begin
    vSelf.Items(pIndex) := pValue;
    return vSelf;
  end;
end;
/


create table test_data
(
  user_id  RAW (16) primary key,
  attr_ids varr_50_wrapper
);


--nested table attr_ids store as attr_ids_tab ;

insert into test_data values (sys_guid(), varr_50_wrapper(VARR_50('abc','pqr',null,null,null,null,null,null,null,null,null,null)));
insert into test_data values (sys_guid(), varr_50_wrapper(VARR_50(null,null,null,null,null,null,null,null)));
insert into test_data values (sys_guid(), varr_50_wrapper(VARR_50('xyz',null,'FOO',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null)));


commit;


update test_data t
set t.attr_ids =  t.attr_ids.SetIndex(7, 'FOO');

3 rows updated.

select *
from   test_data
/

USER_ID                           ATTR_IDS
--------------------------------  ------------------------------------------------------------
FEF801056FA345F8A4FF47F2BF0925CD  VARR_50_WRAPPER(VARR_50('abc','pqr',NULL,NULL,NULL,NULL,'FOO',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
5F428851785241059A36F6969E667B57  VARR_50_WRAPPER(VARR_50(NULL,NULL,NULL,NULL,NULL,NULL,'FOO',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NU
C55BFF6F29764372B261B091D4052054  VARR_50_WRAPPER(VARR_50('xyz',NULL,'FOO',NULL,NULL,NULL,'FOO',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,


3 rows selected.

Solomon Yakobson

Question is what to do if there is no element with such index? Do we extend varray and add it or simply let it fail with subscript beyond count.

SY.

mathguy

https://community.oracle.com/tech/developers/discussion/comment/16804809#Comment_16804809

This is what I meant when I wrote "Then you can do fancier things if needed"

There are at least three ways to handle that condition: the two you mentioned, as well as "leave varray as is" (that is, no action; the varray is not modified if we try to update an out-of-bounds element, but no error is thrown).

I don't know what the common and "best" practices are for such things in the industry (as you surely recall, I never worked in it). To my mind, since we can control everything, I would write the method with a third parameter, to indicate the desired handling of such conditions; then let the invoker decide in their code which handling is best for their specific task. (Perhaps with a default action of throwing an error, but allowing the invoker to pass an argument to either extend the varray or to ignore the update request, if that's what they need.)