Oracle SQL : Oracle 12 C features Part 4

Feature 1:
Booleans in dynamic PL/SQL :

In Oracle 12c we can pass Boolean variable as parameter in functions,procedures and packages
Boolean in dynamic PL/SQL :
You can use booleans values in dynamic PL/SQL.
Boolean can be passed to the PL/SQL routines as bind variable.

CREATE OR REPLACE PROCEDURE are_you_happy (p_bool IN BOOLEAN)
IS
BEGIN
   IF p_bool
   THEN
      DBMS_OUTPUT.put_line ('Happy!');
   ELSE
      DBMS_OUTPUT.put_line ('Not yet!');
   END IF;
END are_you_happy;
/

BEGIN
   EXECUTE IMMEDIATE 'begin are_you_happy(:a); end;' USING TRUE;

   EXECUTE IMMEDIATE 'begin are_you_happy(:a); end;' USING FALSE;
END;

Feature 2:

Implicit Result Sets:

Oracle 12c now offers PLSQL Developers to display the result of sql query directly to the
screen. This is made possible the Oracle procedure DBMS_SQL.RETURN_RESULT.
For Instance,


DECLARE
   REF_CURSOR   SYS_REFCURSOR;
BEGIN
   OPEN REF_CURSOR FOR SELECT   EMPNO, ENAME, DEPTNO FROM EMP;

   DBMS_SQL.RETURN_RESULT (REF_CURSOR);

END;/

Feature 3:

PL/SQL Functions Defined in the SQL WITH Clause: In 12c, WITH clause is enhanced to now include PL/SQL units.

WITH

FUNCTION Funtion_Name(arg1 IN datatype)

RETURN datatype

IS

BEGIN

RETURN;

END GET_TEXT;