3 Replies Latest reply on Jun 22, 2020 2:35 PM by thatJeffSmith-Oracle

    Reporting a BUG in handling plsql in sql

    Erik van Roon

      Since Oracle 12c we can define plsql functions in the with-clause of a query.

      SQLcl handles it correctly if the function is in the with clause of the top-level query.

      But if it's in the with clause of a subquery, it errors out.

       

      @version
      ##############################################################
      Productversions:
      ##############################################################
      Product                                 Version     Status
      ======================================  ==========  ==========
      Oracle Database 19c Enterprise Edition  19.0.0.0.0  Production
      SQLcl                                   19.4.0.0
      ##############################################################
      

       

      create a test table:

       

      create table ero_tst       
      as                                  
      select level                    one 
      ,      cast(null as integer)    two 
      from   dual                         
      connect by level <= 4               
      ;                                   
                                               
      Table created.                           
                                               
      select *                   
      from   ero_tst                      
      order by one                        
      ;                                   
      
         ONE    TWO                            
      ______ ______                            
           1                                   
           2                                   
           3                                   
           4                                   
                                               
      4 rows selected.                         
      

       

      Execute a 'normal' query with a function in the with-clause:

       

      with
        function value_for_two
        (p_one  in  integer
        )
        return integer
        is
        begin
          return (p_one * 10);
        end;
        --
      select value_for_two (t1.one)
      from   ero_tst  t1
      /
      
         VALUE_FOR_TWO(T1.ONE)
      ________________________
                            10
                            20
                            30
                            40
      
      4 rows selected.
      

       

      So success!

      Now lets make this query a subquery in een update statement

      The statement looks like this:

       

      update --+ with_plsql
             ero_tst   t2
      set    two = (
                    with
                      function value_for_two
                      (p_one  in  integer
                      )
                      return integer
                      is
                      begin
                        return (p_one * 10);
                      end;
                      --
                    select value_for_two (t1.one)
                    from   ero_tst  t1
                    where  t1.one    = t2.one
                   )
      /
      

       

      But when I try to execute it in SQLcl:

       

      update --+ with_plsql
             ero_tst   t2
      set    two = (
                    with
                      function value_for_two
                      (p_one  in  integer
                      )
                      return integer
                      is
                      begin
                        return (p_one * 10);
      
      update --+ with_plsql
      ERROR at line 1:
      ORA-00921: unexpected end of SQL command
      
                      end;
      SP2-0734: unknown command beginning "          ..." - rest of line ignored.
                      --
                    select value_for_two (t1.one)
                    from   ero_tst  t1
                    where  t1.one    = t2.one
                   )
      /
      
                   )
                   *
      ERROR at line 4:
      ORA-00933: SQL command not properly ended
      

       

      It thinks the statement ends at the first semicolon.

      Running the same statement in SQL*Plus is no problem at all:

       

      @version
      ##############################################################
      Productversions:
      ##############################################################
      Product                                 Version     Status
      ======================================  ==========  ==========
      Oracle Database 19c Enterprise Edition  19.0.0.0.0  Production
      SqlPlus                                 1903000000
      ##############################################################
      
      update --+ with_plsql
             ero_tst   t2
      set    two = (
                    with
                      function value_for_two
                      (p_one  in  integer
                      )
                      return integer
                      is
                      begin
                        return (p_one * 10);
                      end;
                      --
                    select value_for_two (t1.one)
                    from   ero_tst  t1
                    where  t1.one    = t2.one
                   )
      /
      
      4 rows updated.
      
      select *
      from   ero_tst
      order by one
      ;
      
                  ONE             TWO
      --------------- ---------------
                    1              10
                    2              20
                    3              30
                    4              40
      
      4 rows selected.
      
        • 1. Re: Reporting a BUG in handling plsql in sql
          thatJeffSmith-Oracle

          weird, if i try the same thing

           

          SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 15 15:21:40 2020

          Version 19.3.0.0.0

           

          Copyright (c) 1982, 2019, Oracle.  All rights reserved.

           

          Last Successful login time: Mon Jun 15 2020 15:17:08 -04:00

           

          Connected to:

          Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

          Version 19.3.0.0.0

           

          SQL> update ero_tst tw

            2  set two=(with function value_for_two (p_one in integer) return integer is

            3  begin

            4  return (p_one*10);

            5  end;

            6  select value_for_two (t1.one)

            7  from ero_tst t1

            8  where t1.one = tw.one)

            9  /

          set two=(with function value_for_two (p_one in integer) return integer is

                   *

          ERROR at line 2:

          ORA-32034: unsupported use of WITH clause

           

          SQL>

          • 2. Re: Reporting a BUG in handling plsql in sql
            Erik van Roon

            Jeff,

             

            That's because you don't do the same thing

            If the plsql is in the with clause that is not the top level sql (like in the select in the update statement, or a subquery, or the using clause of a merge, or the select of an insert-as-select) the query needs the /*+ with_plsql */ hint to 'warn' the compiler that it will run into plsql.

             

            Apparently it's something to do with security, according to Brynn, but he wasn't able to make me understand  why that security issue is only there if it's not in the toplevel.

             

            Anyway, your statement lacks the hint, resulting in that error.

            • 3. Re: Reporting a BUG in handling plsql in sql
              thatJeffSmith-Oracle

              ah, i didn't recognize that HINT!

               

              I've logged a bug for your scenario, Erik. Unfortunately we're too late in the game to address this for version 20.2.

              1 person found this helpful