Print Thread
With function ORA-00933: SQL command not properly ended
#57284 11/23/17 11:53 AM
Joined: Jun 2013
Posts: 28
D
Member
OP Offline
Member
D
Joined: Jun 2013
Posts: 28
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.

Re: With function ORA-00933: SQL command not properly ended
developeris #57288 11/24/17 10:08 AM
Joined: Aug 1999
Posts: 22,208
Member
Offline
Member
Joined: Aug 1999
Posts: 22,208
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.


Marco Kalter
Allround Automations
Re: With function ORA-00933: SQL command not properly ended
Marco Kalter #57832 04/23/18 08:50 AM
Joined: Nov 2008
Posts: 14
F
Member
Offline
Member
F
Joined: Nov 2008
Posts: 14
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.

Code
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
/


Re: With function ORA-00933: SQL command not properly ended
fctb #57836 04/24/18 08:41 AM
Joined: Aug 1999
Posts: 22,208
Member
Offline
Member
Joined: Aug 1999
Posts: 22,208
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.


Marco Kalter
Allround Automations

Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.028s Queries: 15 (0.007s) Memory: 2.5045 MB (Peak: 3.0380 MB) Data Comp: Off Server Time: 2024-05-04 12:35:36 UTC
Valid HTML 5 and Valid CSS