Using 'RETURN_RESULT' WITH Clause in procedure/Function in Oracle 12c

Using 'RETURN_RESULT' WITH Clause in procedure/Function in Oracle 12c

Oracle 12c allows a procedure to return a list of rows by using RETURN_RESULT procedure of DBMS_SQL package. Rather than defining explicit reference cursor out parameters, the RETURN_RESULT procedure in the DBMS_SQL package allows you to pass them out implicitly. The following procedure returns list of rows from SALARY table as return value from a procedure.

CREATE OR REPLACE PROCEDURE Get_salary

AS

Salary_cursor SYS_REFCURSOR;

BEGIN

   OPEN salary_cursor FOR

   SELECT * from pay_payment_master;

   DBMS_SQL.RETURN_RESULT(salary_cursor);

END;

You can call the procedure and find the list of rows from the procedure as follows:

EXECUTE Get_salary

Functions in the WITH Clause:

The declaration section of the WITH clause can be used to define PL/SQL functions, as shown below.

WITH

  FUNCTION  expert(EMPLOYMENT_DATE date) RETURN NUMBER IS

  BEGIN

    RETURN  floor ((SYSDATE - EMPLOYMENT_DATE) / 365);

  END;

SELECT LATIN_NAME, expert(EMPLOYMENT_DATE) FROM pay_employee_personal_info;

From a name resolution perspective, functions defined in the PL/SQL declaration section of the WITH clause take precedence over objects with the same name defined at the schema level OFFSET and FETCH clauses

Other Miscellaneous Features:

–        The maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has been increases from 4,000 to 32,767 bytes.

–        Prior to Oracle 12c R1, undo generated by the temporary tables used to be stored in undo tablespace. However in 12c, the temporary undo records can now be stored in a temporary table instead of undo tablespace.