Toad World® Forums

Unable create a view using Oracle 12c features

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.