SQL> create or replace
  2    procedure proc_test(
  3                        v_empno in number,
  4                        v_ref_cursor out sys_refcursor
  5                       )
  6      is
  7      begin
  8      open v_ref_cursor
  9        for
 10          with function get_dept_name(
 11                                      p_department_id number
 12                                     )
 13                 return varchar2
 14                 is
 15                     v_dept_name varchar2(100);
 16                 begin
 17                     select  department_name
 18                       into  v_dept_name
 19                       from  hr.departments
 20                       where department_id = p_department_id;
 21                     return v_dept_name;
 22                 end;
 23          select  e.last_name,
 24                  get_dept_name(e.department_id) department_name
 25            from  hr.employees e
 26          where e.employee_id = v_empno;
 27  end;
 28  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE PROC_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/9      PL/SQL: SQL Statement ignored
9/23     PL/SQL: ORA-00905: missing keyword
SQL>

SQL> create or replace
  2    procedure proc_test(
  3                        v_empno in number,
  4                        v_ref_cursor out sys_refcursor
  5                       )
  6      is
  7      begin
  8      open v_ref_cursor
  9        for
 10          'with function get_dept_name(
 11                                      p_department_id number
 12                                     )
 13                 return varchar2
 14                 is
 15                     v_dept_name varchar2(100);
 16                 begin
 17                     select  department_name
 18                       into  v_dept_name
 19                       from  hr.departments
 20                       where department_id = p_department_id;
 21                     return v_dept_name;
 22                 end;
 23          select  e.last_name,
 24                  get_dept_name(e.department_id) department_name
 25            from  hr.employees e
 26          where e.employee_id = :1' using v_empno;
 27  end;
 28  /

Procedure created.

SQL> variable rc refcursor
SQL> exec proc_test(100,:rc)

PL/SQL procedure successfully completed.

SQL> print rc

LAST_NAME                 DEPARTMENT_NAME
------------------------- --------------------
King                      Executive

SQL>

SQL> drop role r1
  2  /

Role dropped.

SQL> drop user u1 cascade
  2  /

User dropped.

SQL> create user u1 identified by u1 default tablespace users;

User created.

SQL> create role r1;

Role created.

SQL> grant select on emp to r1;

Grant succeeded.

SQL> grant select on dept to r1;

Grant succeeded.

SQL> grant r1 to u1;

Grant succeeded.

SQL> grant create session to u1;

Grant succeeded.

SQL> connect u1@pdborcl12/u1
Connected.
SQL> create or replace
  2    function get_dept_name(
  3                           p_deptno number
  4                          )
  5      return varchar2
  6      is
  7          v_dname varchar2(30);
  8      begin
  9          select  dname
 10            into  v_dname
 11            from  scott.dept
 12            where deptno = p_deptno;
 13          return v_dname;
 14  end;
 15  /
create or replace
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> with function get_dept_name(
  2                              p_deptno number
  3                             )
  4         return varchar2
  5         is
  6          v_dname varchar2(30);
  7         begin
  8             select  dname
  9               into  v_dname
 10               from  scott.dept
 11               where deptno = p_deptno;
 12             return v_dname;
 13       end;
 14  select  ename,
 15          get_dept_name(e.deptno) dname
 16    from  scott.emp e
 17  /

ENAME      DNAME
---------- ---------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH

ENAME      DNAME
---------- ---------------
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

14 rows selected.

SQL>

SQL> declare
  2      function get_dept_name(
  3                             p_deptno number
  4                          )
  5        return varchar2
  6        is
  7            v_dname varchar2(30);
  8        begin
  9            select  dname
 10              into  v_dname
 11              from  scott.dept
 12              where deptno = p_deptno;
 13            return v_dname;
 14      end;
 15  begin
 16      for v_rec in (select ename,get_dept_name(e.deptno) dname from scott.emp e) loop
 17        dbms_output.put_line(v_rec.ename || ', ' || v_rec.dname);
 18      end loop;
 19  end;
 20  /
    for v_rec in (select ename,get_dept_name(e.deptno) dname from scott.emp e) loop
                               *
ERROR at line 16:
ORA-06550: line 16, column 32:
PLS-00231: function 'GET_DEPT_NAME' may not be used in SQL
ORA-06550: line 16, column 32:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 16, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 17, column 28:
PLS-00364: loop index variable 'V_REC' use is invalid
ORA-06550: line 17, column 7:
PL/SQL: Statement ignored


SQL>

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 9 06:49:26 2016

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

Enter user-name: scott@pdborcl12
Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> WITH
  2  FUNCTION str_plus(p_str VARCHAR2) RETURN VARCHAR2
  3  IS
  4  BEGIN
  5     RETURN p_str || ' World';
FUNCTION str_plus(p_str VARCHAR2) RETURN VARCHAR2
*
ERROR at line 2:
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 str_plus('Hello') FROM dual;
SELECT str_plus('Hello') FROM dual
       *
ERROR at line 1:
ORA-00904: "STR_PLUS": invalid identifier


SQL> /

SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 9 06:50:09 2016

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

Enter user-name: scott@pdborcl12
Enter password:
Last Successful login time: Fri Sep 09 2016 06:49:40 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> WITH
  2  FUNCTION str_plus(p_str VARCHAR2) RETURN VARCHAR2
  3  IS
  4  BEGIN
  5     RETURN p_str || ' World';
  6  END;
  7  SELECT str_plus('Hello') FROM dual;
  8  /

STR_PLUS('HELLO')
--------------------------------------------------------------------------------
Hello World

SQL>

SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 9 06:55:21 2016

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

Enter user-name: scott@pdborcl12
Enter password:
Last Successful login time: Fri Sep 09 2016 06:52:27 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> DECLARE
  2      V_RES VARCHAR2(20);
  3  BEGIN
  4      WITH
  5      FUNCTION str_plus(p_str VARCHAR2) RETURN VARCHAR2
  6      IS
  7      BEGIN
  8         RETURN p_str || ' World';
  9      END;
 10      SELECT str_plus('Hello') INTO V_RES FROM dual;
 11      DBMS_OUTPUT.PUT_LINE(V_RES);
 12  END;
 13  /
    FUNCTION str_plus(p_str VARCHAR2) RETURN VARCHAR2
             *
ERROR at line 5:
ORA-06550: line 5, column 14:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 10, column 5:
PLS-00103: Encountered the symbol "SELECT"


SQL>

SQL> with function f(x in int) return int is begin return -x; end; select f(rownum) from dual
  2  /

 F(ROWNUM)
----------
        -1

SQL> with function f(x in int) return int
  2  is begin return -x; end; select f(rownum) from dual
  3  /

 F(ROWNUM)
----------
        -1

SQL> with function f(x in int) return int is begin return -x; end;
with function f(x in int) return int is begin return -x; end
     *
ERROR at line 1:
ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of
the following:
; <an identifier> <a double-quoted delimited-identifier>
current delete exists prior <a single-quoted SQL string>
The symbol ";" was substituted for "end-of-file" to continue.


SQL> with function f(x in int) return int is begin return -x;
with function f(x in int) return int is begin return -x
     *
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> with function f(x in int) return
  2  int is begin return -x;
with function f(x in int) return
     *
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>