Oracle 12c allows multiple functions, and multiple sub-queries, in a WITH clause:
COLUMN object_name FORMAT a30;
COLUMN fn_name FORMAT a30;
COLUMN fn_name2 FORMAT a30;
WITH
FUNCTION fn_with (n VARCHAR2)
RETURN VARCHAR2
IS
lv all_objects.object_name%TYPE;
BEGIN
lv := LOWER(n);
RETURN lv;
END fn_with;
FUNCTION fn_with2 (n VARCHAR2)
RETURN VARCHAR2
IS
lv2 all_objects.object_name%TYPE;
BEGIN
lv2 := initcap(n);
RETURN lv2;
END fn_with2;
get_names AS (
SELECT object_name
FROM all_objects
WHERE rownum <= 10
),
get_data AS (
SELECT object_name, fn_with(object_name) fn_name, fn_with2(object_name) fn_name2
FROM get_names
)
SELECT *
FROM get_data
/
OBJECT_NAME FN_NAME FN_NAME2
------------------------------ ------------------------------ ------------------------------
I_OBJ1 i_obj1 I_Obj1
CLU$ clu$ Clu$
I_COL3 i_col3 I_Col3
I_UNDO1 i_undo1 I_Undo1
I_CDEF4 i_cdef4 I_Cdef4
BOOTSTRAP$ bootstrap$ Bootstrap$
FILE$ file$ File$
I_CCOL2 i_ccol2 I_Ccol2
I_FILE#_BLOCK# i_file#_block# I_File#_Block#
C_USER# c_user# C_User#
10 rows selected.
|
but is not so happy with one function calling another
WITH
FUNCTION fn_with (n VARCHAR2)
RETURN VARCHAR2
IS
lv all_objects.object_name%TYPE;
BEGIN
lv := LOWER(n);
RETURN lv;
END fn_with;
FUNCTION fn_with2 (n VARCHAR2)
RETURN VARCHAR2
IS
lv2 all_objects.object_name%TYPE;
BEGIN
IF LENGTH(n) < 6
THEN
lv2 := fn_with(n);
ELSE
lv2 := initcap(n);
END IF:
RETURN lv2;
END fn_with2;
get_names AS (
SELECT object_name
FROM all_objects
WHERE rownum <= 10
),
get_data AS (
SELECT object_name, fn_with(object_name) fn_name, fn_with2(object_name) fn_name2
FROM get_names
)
SELECT *
FROM get_data
/
SP2-0552: Bind variable "RETURN" not declared.
|