New keywords OFFSET and FETCH provides ANSI SQL standard to limit the number of rows returned and to specify a starting row for the return set.

Note

• ROW and ROWS has got the same meaning and can be used interchangeably
• FIRST and NEXT has got the same meaning and can be used interchangeably

[ OFFSET offset ROWS]
FETCH  NEXT [  row_count | percent PERCENT  ] ROWS  [ ONLY | WITH TIES ]

Continue reading →

It’s new option to return many ref cursors in PL/SQL implicit.

When code is migrated to Oracle Database from other vendors’ environments (Microsoft etc.), the capability will remove the need to rewrite code that takes advantage of implicit result set communication.

This feature is delivered by DBMS_SQL.RETURN_RESULT procedure which is responsible for creation of implicit result sets. Following procedure hasn’t got any explicit declaration of out variables. I love it :).

create or replace procedure return_many_cursors
authid current_user
is
  v_c1 sys_refcursor;
  v_c2 sys_refcursor;
  v_c3 sys_refcursor;
begin
  open v_c1 for 'select object_name from user_objects';

  dbms_sql.return_result(v_c1);

  open v_c2 for 'select instance_name from v$instance';

  dbms_sql.return_result(v_c2);

  open v_c3 for 'select name from v$database';

  dbms_sql.return_result(v_c3);
end;
/

Continue reading →

In 12C release 1 you can define a PL/SQL function, procedure in the WITH clause of a subquery and use in ordinary SQL statements.

WITH
 PROCEDURE <NAME_PROCEDURE> 
 BEGIN
 ... 
 END;

 FUNCTION <NAME_FUNCTION>
 BEGIN
   ...
 END;
SELECT <NAME_FUNCTION>
FROM <TABLE>;
/

Continue reading →

If you need to analyze query which is based on complex view it can take a lot of time to do it.

New procedure EXPAND_SQL_TEXT of package DBMS_UTILITY helps to recursively replace any view references in the input SQL query with the corresponding view subquery.

Test data

create table test_tbl
(id number,
 id1 number);

create view test_v1
as select * from test_tbl;

create view test_v2
as
select * from test_v1;

create view test_v3
as
select id+id1 ok from test_v2;

What if you need to analyze following query

select * from test_v3

With new procedure it’s piece of cake

set serveroutput on
declare
  v_in_view  clob := 'select * from TEST_V3';
  v_out_view clob;
begin
  dbms_utility.expand_sql_text(v_in_view, v_out_view);
  dbms_output.put_line(v_out_view);
end;
/

anonymous block completed
SELECT "A1"."OK" "OK" FROM  
(SELECT "A2"."ID"+"A2"."ID1" "OK" 
FROM (SELECT "A3"."ID" "ID","A3"."ID1" "ID1" 
FROM (SELECT "A4"."ID" "ID","A4"."ID1" "ID1" 
FROM TOMASZ."TEST_TBL" "A4") "A3") "A2") "A1"

be smart do it with new feature WITH FUNCTION directly in sql

with
  function get_data(p_in_view clob)
  return clob
  is
    v_out_view clob;
  begin
    dbms_utility.expand_sql_text(p_in_view, v_out_view);

    return v_out_view;
  end;
select get_data('select * from TEST_V3') from dual
/

GET_DATA('SELECT*FROMTEST_V3')
----------------------------------------------
SELECT "A1"."OK" "OK" FROM  (SELECT "A2"."ID"+ ...

Have a fun 🙂

Tomasz