2 Replies Latest reply on Aug 8, 2019 10:38 AM by Matthias_H

    Usage of WITH FUNCTION in SQL Developer

    Scott Wesley

      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

      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.

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

      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

        • 1. Re: Usage of WITH FUNCTION in SQL Developer
          mNem

          FWIW,

           

          When I ran the below statement on sqldeveloper:  Version 4.0.1.14/ DB: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

           

          ---------------------------------------------------------------------------------------------------

           

          with FUNCTION ver RETURN varchar2 IS 
            BEGIN 
              RETURN DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE; 
          END; 
          select ver from dual 
          ;

          -- (a comment)

          --

           

          ---------------------------------------------------------------------------------------------------

           

          I got the result whereas the same statement fails as you mentioned on sqldeveloper 18.*.

          • 2. Re: Usage of WITH FUNCTION in SQL Developer
            Matthias_H

            I believe a valid PL/SQL block always needs to end with "/"?

             

            For example this works in SQL Developer 18.4 (both when I press "Run Statement" and "Run Script":

             

            with FUNCTION ver RETURN varchar2 IS 

              BEGIN 

                RETURN DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE; 

              END; 

            select ver from dual;

            /

             

            with FUNCTION ver RETURN varchar2 IS 

              BEGIN 

                RETURN DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE; 

              END; 

            select ver from dual;

            /

             

            HTH

            Matthias