How to prevent Oracle from calling a function for every attribute of the function result

I’ve created a package, containing a function that returns an object.
When retrieving the object details through sql, the function is called multiple times – once for every detail retrieved.
I believe it should be possible for it to just be called once instead.

Following is an example that demonstrates the issue:

CREATE OR REPLACE TYPE t_test AS OBJECT (
    v1              VARCHAR2(10),
    v2              VARCHAR2(10),
    v3              VARCHAR2(10),
    times_called    NUMBER
);
/

CREATE OR REPLACE PACKAGE test_pkg AS 
    times_called    NUMBER :=0;
    FUNCTION test(something IN VARCHAR2) RETURN t_test;
    PROCEDURE reset;

END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg IS
    PROCEDURE reset IS
    BEGIN
        times_called := 0;
    END;
    
    FUNCTION test(something IN VARCHAR2) RETURN t_test IS
        
    BEGIN
        times_called := times_called + 1;
        RETURN t_test('first', 'second', 'third', times_called);
    END;

END test_pkg;
/

Here we can see that the function is invoked four times:

SQL> SELECT t.r.v1, t.r.v2, t.r.v3, t.r.times_called FROM (
  2      SELECT test_pkg.test('x') r FROM DUAL
  3  ) t;

R.V1       R.V2       R.V3       R.TIMES_CALLED
---------- ---------- ---------- --------------
first      second     third                   4

SQL>

If we reset the counter, and only select two attributes, we can see it’s called twice:

SQL> exec test_pkg.reset();

PL/SQL procedure successfully completed.

SQL> SELECT t.r.v1, t.r.times_called FROM (
  2      SELECT test_pkg.test('x') r FROM DUAL
  3  ) t;

R.V1       R.TIMES_CALLED
---------- --------------
first                   2

SQL>

The actual stored procedure is more expensive, so I’d like to avoid re-calling it for every attribute listed.

The solution has to work on Oracle 10gr2

>Solution :

Oracle is not materializing the sub-query and is pushing the function calls to the outer query. You need to force the SQL engine to materialize the inner query either by:

Using a seemingly unnecessary ROWNUM > 0 filter:

SELECT t.r.v1, t.r.v2, t.r.v3, t.r.times_called
FROM (
  SELECT test_pkg.test('x') r
  FROM   DUAL
  WHERE  ROWNUM > 0
) t;

or, you should be able to use the (undocumented) /*+ materialize */ hint but, for an unknown reason, it doesn’t seem to want to materialize this particular query (although it does work for similar problems).

db<>fiddle here