Here’a sample query I’ve created that uses WITH clause with a function and some sub-query (my real query is much more complex, but has similar structure).
–CREATE VIEW TMP_ABC AS
WITH
FUNCTION abc (p_val NUMBER) RETURN VARCHAR2 IS
vret VARCHAR2 (100);
BEGIN
SELECT LPAD (’ ', p_val, ‘z’) INTO vret FROM DUAL;
RETURN vret;
END;
gen AS (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL < 10)
SELECT gen.rn, abc (rn) abc
FROM gen;
With the first line commented out, the query runs fine from SQL Navigator 7.4, formatter is able to format it, but if I uncomment the first line and attempt to create a view based on this query I get an error message.
line 1: ORA-06553: PLS-103: Encountered the symbol “end-of-file” when expecting one of the following:
:= ; not null default character
or
line 12: ORA-00900: invalid SQL statement
Using SQL Nav 7.4 64-bit, Oracle 12c client 64 bit and 12c database.
Tried Oracle SQL Developer and the view creation script works just fine.