CODEWITHSUNDEEP
oracle-databaseplsql
Thomas Carlton
Thomas Carlton

Reputation: 5968

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

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?

Upvotes: 0

Views: 325

Answers (1)

MT0
MT0

Reputation: 168041

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.

Upvotes: 1

Related Questions