With function ORA-00933: SQL command not properly ended

[developeris]

When I run this in SQL window:

with function foo return number is
begin
return 1;
end;
select foo from dual;

I get "ORA-00933: SQL command not properly ended"

If I remove semicolon after dual, it works.

Tested in sqlplus, it works fine with the semicolon.

 

[Marco Kalter]

This is indeed a known issue. You have to terminate the command with a slash (or nothing if it is the only statement in the script) due to the PL/SQL section that it contains.

 

[fctb]

Creating a view with a function in a with clause does not work even if "AutoSelect statement" is disabled. Not even in the command window.

We have to use SqlPlus to compile such view.

CREATE OR REPLACE VIEW TMP_FUNCV AS
WITH
  FUNCTION FOO RETURN NUMBER IS
  BEGIN
    RETURN 1;
  END;
  FUNCTION FOO2 RETURN NUMBER IS
  BEGIN
    RETURN 2;
  END;
SELECT FOO,FOO2 FROM DUAL
/

 

[Marco Kalter]

As a workaround you can also execute it in a Test Window, which does not try interpret separate statements in a script, but sends the whole script to the server.