Saturday, June 29, 2013

PL/SQL function in SQL - short example - Oracle 12c

This is my first post about Oracle 12c. When I first time heard about PL/SQL functions inside SQL my first thought was - can I use it in my topaas.sql script to run it in loop ? I decided to give PL/SQL functions in SQL a quick try to and here are my results. My goal was to display results from SQL line by line with small delay. This is a code a decided to use:
SQL> set arraysize 1
SQL> col slow_me noprint
SQL> with function slow_me return number is 
  2   begin
  3     dbms_lock.sleep(1);
  4     return 1;
  5   end;
  6  select level, slow_me() slow_me from dual connect by level < 10;
  7  /

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.
Unfortunately only first row is displayed alone. Rest of rows is displayed in pairs even if arraysize is set to 1. Not sure now why it happen and I would to find out some day. After problems with pure SQL I decided to check how SQL*Plus refcursor print functionality is working. Here is second version of code:
SQL> var c refcursor
SQL> declare
  2  stm varchar2(1000);
  3  begin
  4  stm:='with function slow_me return varchar2 is
  5   begin
  6     dbms_lock.sleep(1);
  7     return '''';
  8  end;
  9  select level, slow_me() slow_me from dual connect by level < 10';
 10  open :c for stm;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> print c

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.
Now it's working. This code is displaying row by row with 1 sec delay. So now it is time to display some more interesting results. Code below is displaying Average Active Sessions (15s) taken from v$sysmetric view with delay defined by user aas.sql
var c refcursor
set arraysize 1 

declare
sqlst varchar2(2000):='
with function aas RETURN varchar2 IS
v varchar2(100);
begin
select VALUE into v from v$sysmetric where METRIC_NAME = ''Average Active Sessions'' and INTSIZE_CSEC < 5000;
dbms_lock.sleep(&SLEEP_SEC);
v:=''Average Active Sessions '' || v;
return v;
end;
select aas() from dual connect by level < 1000';

begin
 open :c for sqlst;
end;
/

print c
It can be run from SQL*Plus and it will be some kind of top-like utility.
[oracle@ora12c ~]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 29 13:34:37 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @aas.sql
Enter value for sleep_sec: 1
old   7: dbms_lock.sleep(&SLEEP_SEC);
new   7: dbms_lock.sleep(1);

PL/SQL procedure successfully completed.


AAS()
--------------------------------------------------------------------------------
Average Active Sessions .0000153231179213857
Average Active Sessions .0000153231179213857
Average Active Sessions .0000153231179213857
Average Active Sessions .0000153231179213857
Average Active Sessions .0000153231179213857
Average Active Sessions .0000153231179213857
Average Active Sessions .0000153231179213857

That's only examples of new functionality. Now I have to review topaas code again and see if I can use that feature there. 

regards, 
Marcin

3 comments:

Sudhir DBAKings said...

Nice post very helpful

dbakings

Alvin said...

This is cool!

Hariharan s said...

Hi,Nice example in Oracle Performance and Backup Blog.Thanks...


Theosoft