Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 13 March 2014

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.

0 comments:

Post a Comment