clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36307 in the last week).

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


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


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; /
 hidden batch(es)


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;
R.V1 R.V2 R.V3 R.TIMES_CALLED
first second third 1
 hidden batch(es)


WITH cte AS ( SELECT /*+ materialize */ test_pkg.test('x') r FROM DUAL ) SELECT t.r.v1, t.r.v2, t.r.v3, t.r.times_called FROM cte t;
ORA-00604: error occurred at recursive SQL level 1 ORA-00902: invalid datatype
 hidden batch(es)


SELECT t.r.v1, t.r.v2, t.r.v3, t.r.times_called FROM ( SELECT /*+ materialize */ test_pkg.test('x') r FROM DUAL ) t;
R.V1 R.V2 R.V3 R.TIMES_CALLED
first second third 5
 hidden batch(es)