Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Usage of WITH FUNCTION in SQL Developer

Scott WesleyAug 5 2019 — edited Aug 8 2019

I noticed a behaviour in SQL Developer regarding the WITH clause that reminds me of script behaviour with PL/SQL packages.

If these two duplicated statements use the trailing slash, they both execute fine

pastedImage_1.png

However, if I use the semi-colon, like I do all my other SQL statements, SQL Developer doesn't recognise the statement is complete, and highlights both during execution, then ends with ORA-00933.

pastedImage_2.png

This was on SQL Developer 18.4, and the latest and greatest 19.2, though the statement highlight 'feature' is no longer apparent.

pastedImage_3.png

Is this expected behaviour?

Here is the relevant statement

with FUNCTION ver RETURN varchar2 IS

BEGIN

RETURN DBMS\_DB\_VERSION.VERSION || '.' || DBMS\_DB\_VERSION.RELEASE;

END;

select ver from dual

;

The single statement also fails on livesql.oracle.com

https://twitter.com/swesley_perth/status/1158518193696018432

Scott

Comments
Post Details
Added on Aug 5 2019
2 comments
833 views