How to pass by reference object instance in PL/SQL?

Question

I have the following object :

-- A dummy type with one attribute      
CREATE OR REPLACE TYPE example_type AS OBJECT
(
  value VARCHAR2(50),
  CONSTRUCTOR FUNCTION example_type (value_in VARCHAR2) RETURN SELF AS RESULT
);
/

CREATE OR REPLACE TYPE BODY example_type AS
  CONSTRUCTOR FUNCTION example_type (value_in VARCHAR2) RETURN SELF AS RESULT IS
  BEGIN
    SELF.value := value_in;
    RETURN;
  END;
END;
/

And another table type of the same object :

-- A table of example_type
CREATE OR REPLACE TYPE example_table_type AS TABLE OF example_type;
/

I use these two objects as follows :

declare   
  Item example_type;
  ItemsList example_table_type;
          
  Value1 varchar2(15);
  Value2 varchar2(15);
begin
  Item := example_type(value_in => 'a');
          
  ItemsList := example_table_type();
  ItemsList.extend(1);
  ItemsList(ItemsList.count) := Item;
          
  ItemsList(1).value := 'b';    -- Replace the object value with 'b'
          
  Value1 := Item.value;         -- This returns 'a'. Why hasn't this value changed?
  Value2 := ItemsList(1).value; -- This returns 'b'
end;

When running this code the value of the original object doesn't change (It's still a) While I'm expecting it to change to b.

If I run this code in C# or .Net it will change.

I was wondering why the value remains the same as I expect Oracle to "pass the object by reference".

What am I doing wrong?

MT0 · Accepted Answer · 2023-02-11 19:53:32Z

1

I was wondering why the value remains the same as I expect Oracle to "pass the object by reference".

Does anyone know what am I doing wrong ?

Oracle does not "pass by reference".

declare   
  item1 example_type := example_type(value_in => 'a');
  item2 example_type := item1;
begin
  item1.value := 'b';
      
  DBMS_OUTPUT.PUT_LINE(
    item1.value || ' - ' || item2.value
  );
end;
/

Outputs b - a as item2 is a copy of item1 and not a reference to item1.

If you want to create references to objects then store them in a table and use a REF pointer.

answered Feb 11, 2023 at 19:53

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

William Robertson Over a year ago

To be slightly more exact, PL/SQL does pass by reference in some cases, just not this one.

Collectives™ on Stack Overflow

How to pass by reference object instance in PL/SQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related