11 Replies Latest reply: Jul 3, 2013 6:02 PM by 523861 RSS

    12c - plsql function in with clause

    523861

      Hi all

       

      I'm mucking around with 12c:

       

       

      SQL> select *
        2    from v$version;
      
      
      BANNER                                                                               CON_ID
      -------------------------------------------------------------------------------- ----------
      Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
      PL/SQL Release 12.1.0.1.0 - Production                                                    0
      CORE    12.1.0.1.0      Production                                                                0
      TNS for Linux: Version 12.1.0.1.0 - Production                                            0
      NLSRTL Version 12.1.0.1.0 - Production                                                    0
      
      
      5 rows selected.
      
      
      

       

      specifically I'm trying to use the new functionality of putting a pl/sql function in the WITH clause of an SQL statement:

       

      docco here:

      http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_10002.htm#BABJFIDC

       

      the example they give is:

       

       

      WITH
      FUNCTION get_domain(url VARCHAR2) RETURN VARCHAR2 IS
        pos BINARY_INTEGER;
        len BINARY_INTEGER;
      BEGIN
        pos := INSTR(url, 'www.');
        len := INSTR(SUBSTR(url, pos + 4), '.') - 1;
        RETURN SUBSTR(url, pos + 4, len);
      END;
      SELECT DISTINCT get_domain(catalog_url)
        FROM product_information;
      /
      
      
      

       

      and so I produced my own hello world version:

       

      with function add_number(num1 number, num2 number) return number is
      begin
         return num1 + num2;
      end;
      select add_number(1,2) from dual;
      /
      
      
      

       

       

      but this just doesn't compile in SQL developer or SQLPlus.   do I need to get an updated version or something perhaps?

       

      output is:

       

       

      SQL> with function add_number(num1 number, num2 number) return number is
        2  begin
        3     return num1 + num2;
      with function add_number(num1 number, num2 number) return number is
           *
      ERROR at line 1:
      ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of the following:
      . ( * @ % & = - + ; < / > at in is mod remainder not rem
      <an exponent (**)> <> or != or ~= >= <= <> and or like like2
      like4 likec between || member submultiset
      
      
      
      
      SQL> end;
      SP2-0042: unknown command "end" - rest of line ignored.
      SQL> select add_number(1,2) from dual;
      select add_number from dual
             *
      ERROR at line 1:
      ORA-00904: "ADD_NUMBER": invalid identifier
      
      
      
      
      SQL> /
      select add_number from dual
             *
      ERROR at line 1:
      ORA-00904: "ADD_NUMBER": invalid identifier