3 Replies Latest reply on Mar 12, 2019 12:34 PM by thatJeffSmith-Oracle

    explain plan for statement not working with "with function" clause

    Mustafa KALAYCI

      Hello everyone,

       

      SQL Developer:  Version 18.4.0.376

      OS: Windows 7 and Windows 10

      DB: 12.2.0.1

       

      EXPLAIN PLAN FOR
      with function my_func(p number) return number as
      begin
        return p*p;
      end;
      select my_func(10)
      from dual;
      /
      

      this code raise an error: SQL Error: ORA-00904: "MY_FUNC": invalid identifier

       

      sql is running standalone also sqlplus is able to run the code with explain plan command:

      SQL> EXPLAIN PLAN FOR
        2  with function my_func(p number) return number as
        3  begin
        4    return p*p;
        5  end;
        6  select my_func(10)
        7  from dual;
        8  /
      
      
      Explained.
      
      
      SQL> select * from table(dbms_xplan.display);
      
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------
      Plan hash value: 1388734953
      
      
      -----------------------------------------------------------------
      | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
      -----------------------------------------------------------------
      |   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
      |   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
      -----------------------------------------------------------------
      
      
      8 rows selected.
      
      
      SQL>
      

       

      any idea or work around?

      thanks.