How to create a view with a "WITH FUNCTION" clause ?

[Philippe Malera]

It seems that we could only create such view with SQL*Plus, but not with PLSQL Developer.

CREATE OR REPLACE VIEW test$v AS
    WITH
      FUNCTION with_function(p_id IN VARCHAR2) RETURN VARCHAR2 IS
      BEGIN
        RETURN p_id;
      END;
    SELECT with_function(dummy) AS dummy
    FROM   dual;
    /

This gives this error :

• ORA-06553: PLS-103: Encountered the symbol "end-of-file"

I've tried with SQL window, command window and program window.

Note: Post on Stack Overflow

 

[Marco Kalter]

You need to omit the last semi-colon and place the trailing slash at the start of the line:

CREATE OR REPLACE VIEW test$v AS
    WITH
      FUNCTION with_function(p_id IN VARCHAR2) RETURN VARCHAR2 IS
      BEGIN
        RETURN p_id;
      END;
    SELECT with_function(dummy) AS dummy
    FROM   dual
/

 

[Philippe Malera]

Thanks