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

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 resolutions 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 tablepace. However in 12c, the temporary undo records can now be stored in a temporary table instead of undo tablespace.

 Posted SRY IT Solutions

Contact us for more details and Session Schedules at :

  IND: +91- 9948030675, 

USA: +1- 563-823-8515

Email: info@sryitsolutions.com, 

Web: http://www.sryitsolutions.com/