How to create two variables refering the same nested table?

Question

I expected that in the following code variables x and y refer the same nested table object but expression y = x seems to create empty table in y. So output is surprisingly 'No: a c' ( however I expected 'Yes: a b c'). What should I do in PL/SQL to have y referring the same nested table object as x.

declare
  type str_t is table of varchar(100);
  x str_t := str_t ();
  y str_t ;
begin 

  x.extend;  x(x.last) := 'a';

  y := x;

  y.extend;  y(y.last) := 'b';

  x.extend;  x(x.last) := 'c';

  dbms_output.put_line(case when x=y then 'Yes: ' else 'No: ' end);

  for i in x.first .. x.last
  loop       
     dbms_output.put_line(x(i));      
  end loop;

Сan it (passing nested table by reference - not by value) be written in PL/SQL or it's fundamentally impossible? Does any alternatives exist in the language to aviod the problem?

Answer 1

PL/SQL doesn't have references (but in one place we'll see later) so essentially what you're asking is impossible in PL/SQL. I don't see how that can be a problem, though.

In your example x and y are two different variables that are having the same type (a nested table collection), x := y is a variable assigment (deep copy), x = y is a comparison. See also e.g. Assigning Values to Variables.

I guess you've programming background with some other languages and you're trying to apply some other language paradigm here. You also might have an XY-problem.

One alternative is to use two different index variables to access the collection. That might or might not suit for your case:

declare
  type str_list_t is table of varchar2(32767);
  v_foos constant str_list_t := str_list_t('a', 'b', 'c');
  -- have some business logic to get the right indices
  i pls_integer := 1;
  j pls_integer := 3;
begin
  dbms_output.put_line(v_foos(i));
  dbms_output.put_line(v_foos(j));
end;
/

But please note that changes to collection will invalidate the index variables (that's why I declared a constant collection).

When working with nested tables check also SQL multiset conditions that provide a powerful manipulations.

The only place with reference semantics is subprogram parameters where different parameter modes (IN, OUT or IN OUT) have different semantics (pass-by-value or pass-by-reference). But even there the decision is made by PL/SQL compiler/runtime - programmer has no much control.