plsql

Instrumentation … not just for debugging

connormcdonald , ,

Yeah I know.  You’re probably thinking “Here’s another blog post from someone telling us how important it is to instrument our code, so we can get better debugging, better performance, blah blah blah”.

If that is your sentiment, then I’m thrilled because it means the instrumentation message has got through to the extent that it is now considered just a statement of the obvious.  As many will know, my predecessor was a huge advocate of extensive instrumentation in your application code.  And there is no shortage of examples out there of other Oracle professionals who similarly wax lyrical on the incredible importance of well instrumented code.  For the record, I’m in that camp as well, and we now even have contributions to the Oracle community for everyone to have a robust instrumentation framework in their code.  So there is plenty of information out there, and it is pretty safe to say that there are no more excuses left for un-instrumented or poorly instrumented code.

So why the blog post ?  Well…I’ve got a slightly different assertion to make when it comes to instrumentation, namely, instrumentation can help your user interface.  You will be able to provide a better experience to the users of your application by having your code instrumented.  Now you might be thinking that I’m just banging the same drum here, that is, by instrumenting I’ll be fixing bugs faster, or picking up performance issues more easily, or collecting metrics to help improve application quality.  These are of course true benefits of instrumentation, but we all know that right ?

No…my motivation for this blog post stems from an activity on one of my laptop’s last week.

I went to upgrade it to Windows 10.  I fired off the process and after a short while, the following window appeared

image

and the familiar “something is happening” feedback swirling dots…

image

I have several laptops at home, so there was no need to sit and watch this one, so I left it and worked on another tackling some AskTom questions.  The laptop I upgrading just sits on the shelf, and occasionally plays Netflix for my children.  Hence, naturally enough… I totally forgot about it!

That was Saturday.  Now on Monday… 48 hours later, I’ve just been over to and waved the mouse to restore the display.  And this is what is on the screen:

image

My oh my …. there must be a lot of things to get ready Smile

The obvious question now is – has anything actually happened ?  Has the program hung ?  Or is it downloading a huge Windows 10 OS bundle ?  As a consumer of this “application” (the upgrade process) I have no idea.  There is nothing that tells me what state the application is in.  Now let me give the Microsoft developers the benefit of the doubt here, and make the assumption that the upgrade is extensively instrumented, and that if I looked in a logging file somewhere on the laptop hard drive, there would be a capture of the current state which would tell me whether any true progress is being made.  And that is typically how most us of implement instrumentation – a means to capture information for diagnosis.

But to do so is to miss an opportunity to directly assist the user.  Even if the content of the instrumentation would appear to be gibberish to a typical Windows consumer, just the presence of that detail on the user interface  becomes an improvement to the user experience.  If my upgrade dialog box had looked something like this:

image

then even if I have no idea what “getting platform details” means, the fact that I was on Step 17 at 9:00:12 helps me as a consumer, because I have immediate feedback as to whether progress is being made or not.  If I see that message at 9:05am, then I’m likely to let it sit a little longer to see if a “Step 18” ever eventuates.  But if I’m seeing that message at 5pm, then I can be reasonably sure that all is not well.

And no, I don’t think a swirling icon is sufficient.  We all used to criticize the “Progress Bars” on older versions of Windows and the like, when it would either exceed 100% or never get there, or jump back and forth.  So I can understand the motivation for the swirly icons we see in all modern devices and operating systems, because they avoid the task of giving any true prediction of completion.  And don’t get me wrong, I’m not saying that a return to progress bars is the solution, because that would be understating the difficultly of being able to know how long a task will take.  But simply taking advantage of the instrumentation code that I know you already have Smile, and presenting that in some way to the consumers of your application can be a better user experience for them.

Keep that in mind.

Haversine PL/SQL

connormcdonald ,

I didn’t see a PL/SQL version on https://rosettacode.org for the Haversine formula so here’s a version for anyone that needs it.



SQL> create or replace
  2  function p2p_distance(
  3              p_latitude1 number,
  4              p_longitude1 number,
  5              p_latitude2 number,
  6              p_longitude2 number) return number deterministic is
  7    earth_radius  number := 6371;
  8    pi_approx     number := 3.1415927/180; 
  9    lat_delta     number := (p_latitude2-p_latitude1)*pi_approx;
 10    lon_delta     number := (p_longitude2-p_longitude1)*pi_approx;
 11    arc           number := sin(lat_delta/2) * sin(lat_delta/2) +
 12                                 sin(lon_delta/2) * sin(lon_delta/2) * cos(p_latitude1*pi_approx) * cos(p_latitude2*pi_approx);
 13  begin
 14    return earth_radius * 2 * atan2(sqrt(arc), sqrt(1-arc));
 15  end;
 16  /

Function created.

SQL>
SQL> select p2p_distance(36.12, -86.67, 33.94, -118.4) from dual;

P2P_DISTANCE(36.12,-86.67,33.94,-118.4)
---------------------------------------
                             2886.40705

Simple demo of message propagation

connormcdonald , ,

If you’re using AQ, then it’s simple to setup simple enqueue and dequeue facilities on your local database to provide all sorts of asynchronous style processing in your applications.  As long as you’re applications are designed and built to handle it, the “fire and forget” model to keep user applications responsive, and all of the “heavy lifting” done in the background is a very attractive one.

You can also use AQ to achieve the same concept across multiple databases, and the database will take care of propagating the messages from one database to the other.  Here’s a simple demo of that.

Database 1




SQL> connect / as sysdba
Connected.

--
-- A user to hold all of our AQ stuff
--
SQL> create user aqtest identified by aqtest;

User created.

SQL> grant connect, resource, aq_administrator_role to aqtest;

Grant succeeded.

SQL> grant execute on dbms_aq to aqtest;

Grant succeeded.

SQL> grant execute on dbms_aqadm to aqtest;

Grant succeeded.

SQL> alter user aqtest quota unlimited on users;

User altered.

SQL> grant create database link to aqtest;

Grant succeeded.

SQL> begin
  2    dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','AQTEST',FALSE);
  3    dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','AQTEST',FALSE);
  4  end;
  5  /

PL/SQL procedure successfully completed.

--
-- Now we connect as AQTEST and construct our message payload types and our local queues
--

SQL> connect aqtest/aqtest
Connected.

SQL> create type aqtest.message_typ as object(subject varchar2(30), text varchar2(80));
  2  /

Type created.

SQL> begin
  2    dbms_aqadm.create_queue_table(queue_table => 'aqtest.messages_qtab',
  3                                  queue_payload_type =>  'aqtest.Message_typ',
  4                                  multiple_consumers => TRUE);
  5    dbms_aqadm.create_queue(queue_name => 'MSG_QUEUE',
  6                            queue_table => 'aqtest.messages_qtab');
  7    dbms_aqadm.start_queue(queue_name => 'MSG_QUEUE');
  8  end;
  9  /

PL/SQL procedure successfully completed.

--
-- And here is a basic enqueue routine.  If a remote address is specify, then we will propagate
-- the message to that address.  Otherwise the message will stay in the local queue.
--
SQL> create or replace procedure enqueue_msg(p_msg in varchar2,
  2                                          p_remote_address in varchar2 default null)
  3  as
  4    l_enqueue_options    dbms_aq.enqueue_options_t;
  5    l_message_properties dbms_aq.message_properties_t;
  6    l_message_handle     raw(16);
  7    l_message            aqtest.message_typ;
  8    l_recipients         dbms_aq.aq$_recipient_list_t;
  9  BEGIN
 10    l_recipients(1) := SYS.aq$_agent('RECIPIENT', p_remote_address, null);
 11    l_message_properties.recipient_list := l_recipients;
 12
 13    l_message := message_typ('NORMAL MESSAGE',  p_msg );
 14    dbms_aq.enqueue(queue_name => 'msg_queue',
 15                    enqueue_options => l_enqueue_options,
 16                    message_properties => l_message_properties,
 17                    payload => l_message,
 18                    msgid => l_message_handle);
 19  end;
 20  /

Procedure created.

Database 2, we do the exact same setup



SQL> connect / as sysdba
Connected.

--
-- A user to hold all of our AQ stuff
--
SQL> create user aqtest identified by aqtest;

User created.

SQL> grant connect, resource, aq_administrator_role to aqtest;

Grant succeeded.

SQL> grant execute on dbms_aq to aqtest;

Grant succeeded.

SQL> grant execute on dbms_aqadm to aqtest;

Grant succeeded.

SQL> alter user aqtest quota unlimited on users;

User altered.

SQL> grant create database link to aqtest;

Grant succeeded.

SQL> begin
  2    dbms_aqadm.grant_system_privilege('ENQUEUE_ANY','AQTEST',FALSE);
  3    dbms_aqadm.grant_system_privilege('DEQUEUE_ANY','AQTEST',FALSE);
  4  end;
  5  /

PL/SQL procedure successfully completed.

--
-- Now we connect as AQTEST and construct our message payload types and our local queues
--

SQL> connect aqtest/aqtest
Connected.

SQL> create type aqtest.message_typ as object(subject varchar2(30), text varchar2(80));
  2  /

Type created.

SQL> begin
  2    dbms_aqadm.create_queue_table(queue_table => 'aqtest.messages_qtab',
  3                                  queue_payload_type =>  'aqtest.Message_typ',
  4                                  multiple_consumers => TRUE);
  5    dbms_aqadm.create_queue(queue_name => 'MSG_QUEUE',
  6                            queue_table => 'aqtest.messages_qtab');
  7    dbms_aqadm.start_queue(queue_name => 'MSG_QUEUE');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Back to Database 1


--
-- We need a database link to the remote database, plus a quick query to test that its working
--

SQL> create database link remote_db connect to aqtest identified by aqtest using 'db11';

Database link created.

SQL> select * from tab@remote_db;

no rows selected


--
-- We get our propagation schedule running, and we're ready to go.   
--

SQL> begin
  2    dbms_aqadm.schedule_propagation(queue_name  => 'MSG_QUEUE',
  3                                        destination => 'remote_db',
  4                                        start_time  => sysdate,
  5                                        latency     => 0);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select * from user_queue_schedules
  2  @pr
==============================
QNAME                         : MSG_QUEUE
DESTINATION                   : REMOTE_DB
START_DATE                    :
START_TIME                    : 14:16:01
PROPAGATION_WINDOW            :
NEXT_TIME                     :
LATENCY                       : 0
SCHEDULE_DISABLED             : N
PROCESS_NAME                  : J000
SESSION_ID                    : 400, 38936
INSTANCE                      : 1
LAST_RUN_DATE                 : 28-NOV-16 02.16.01.283000 PM +08:00
LAST_RUN_TIME                 : 14:16:01
CURRENT_START_DATE            : 28-NOV-16 02.16.01.283000 PM +08:00
CURRENT_START_TIME            : 14:16:01
NEXT_RUN_DATE                 : 28-NOV-16 02.16.01.280000 PM +08:00
NEXT_RUN_TIME                 : 14:16:01
TOTAL_TIME                    : 0
TOTAL_NUMBER                  : 0
TOTAL_BYTES                   : 0
MAX_NUMBER                    : 0
MAX_BYTES                     : 0
AVG_NUMBER                    : 0
AVG_SIZE                      : 0
AVG_TIME                      : 0
FAILURES                      : 0
LAST_ERROR_DATE               :
LAST_ERROR_TIME               :
LAST_ERROR_MSG                :
MESSAGE_DELIVERY_MODE         : PERSISTENT
ELAPSED_DEQUEUE_TIME          :
ELAPSED_PICKLE_TIME           :
JOB_NAME                      : AQ_JOB$_6438

PL/SQL procedure successfully completed.

--
-- a message that will not be propagaged, because remote recipient is not specified
--
SQL> begin
  2    enqueue_msg('This message will stay local');
  3    commit;
  4  end;
  5  /

PL/SQL procedure successfully completed.

--
-- a message that WILL be propagated, because remote recipient is specified
--
SQL> begin
  2    enqueue_msg('This message will be propagated.',
  3                 'aqtest.msg_queue_other@remote_db');
  4    commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

--
-- So if everything is working correctly, we have 2 messages on the local queue, and 1 message on the remote queue
--

SQL>
SQL> select t1.cnt,
  2         t2.cnt
  3  from (select count(*) cnt from messages_qtab) t1,
  4       (select count(*) cnt from messages_qtab_other@remote_db) t2
  5  /

       CNT        CNT
---------- ----------
         2          1

1 row selected.

SQL>
SQL>

And there you go.  Messages between databases using the in-built propagation mechanisms.

BULK COLLECT into nested table

connormcdonald , ,

I had an observation come to me last week about PL/SQL and populating nested tables.

“The BULK COLLECT into statement cannot be used repeatedly to append results into a table.
Instead, it silently truncates the target table each time. “

This is true.  However, if you need to use BULK COLLECT to append results into a single nested table data structure, it is trivial to come up with a workaround using the MULTISET syntax.


SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 as select * from dba_objects;

Table created.

SQL>
SQL> drop table t2 purge;

Table dropped.

SQL> create table t2 as select * from dba_objects;

Table created.

SQL>
SQL> set serverout on
SQL> declare
  2    type ntt is table of t1%rowtype;
  3    r1 ntt := ntt();
  4    r2 ntt := ntt();
  5  begin
  6    select * bulk collect into r1 from t1 ;
  7    select * bulk collect into r2 from t2 ;
  8    dbms_output.put_line('T1 count= '||r1.count);
  9    dbms_output.put_line('T2 count= '||r2.count);
 10
 11    r1 := r1 MULTISET UNION ALL r2;
 12
 13    dbms_output.put_line('TOTAL = '||r1.count);
 14  end;
 15  /
T1 count= 99250
T2 count= 99250
TOTAL = 198500

PL/SQL procedure successfully completed.

Read more about the multiset syntax here

Dealing with URL’s

connormcdonald ,

If you are creating or handling URL’s, there is a nice little package routine that assists with handling all those pesky special characters. I’ve added a little wrapper just to make the parameter handling easier


SQL> create or replace function utl_url_escape(x varchar2) return varchar2 is
  2  begin
  3    return utl_url.escape(x,true);
  4  end;
  5  /

Function created.

SQL> col escaped_char format a20
SQL> WITH special_chars
  2  AS (SELECT '<' AS sp_char FROM dual UNION ALL
  3      SELECT '>' AS sp_char FROM dual UNION ALL
  4      SELECT '.' AS sp_char FROM dual UNION ALL
  5      SELECT '#' AS sp_char FROM dual UNION ALL
  6      SELECT '{' AS sp_char FROM dual UNION ALL
  7      SELECT '}' AS sp_char FROM dual UNION ALL
  8      SELECT '|' AS sp_char FROM dual UNION ALL
  9      SELECT '\' AS sp_char FROM dual UNION ALL
 10      SELECT '^' AS sp_char FROM dual UNION ALL
 11      SELECT '~' AS sp_char FROM dual UNION ALL
 12      SELECT '[' AS sp_char FROM dual UNION ALL
 13      SELECT ']' AS sp_char FROM dual UNION ALL
 14      SELECT '<' AS sp_char FROM dual UNION ALL
 15      SELECT '`' AS sp_char FROM dual UNION ALL
 16      SELECT '+' AS sp_char FROM dual UNION ALL
 17      SELECT '/' AS sp_char FROM dual UNION ALL
 18      SELECT '?' AS sp_char FROM dual UNION ALL
 19      SELECT '&' AS sp_char FROM dual UNION ALL
 20      SELECT '''' AS sp_char FROM dual)
 21  SELECT sp_char
 22  ,      utl_url.escape(sp_char) AS escaped_char
 23  FROM   special_chars;

S ESCAPED_CHAR
- --------------------
< %3C
> %3E
. .
# %23
{ %7B
} %7D
| %7C
\ %5C
^ %5E
~ ~
[ [
] ]
< %3C
` %60
+ +
/ /
? ?
& &
' '

19 rows selected.

FOLLOWS clause

connormcdonald ,

In early Oracle releases, you could have 1 trigger per type of trigger per table (eg if you had a before-row-insert trigger, you could have only 1). Now there is no limit. But that does raise the question – if I have “n” BEFORE ROW triggers on a table, in which order do they fire ? Let’s look at the following example:


SQL> drop table t1 purge;

Table dropped.

SQL> create table T1 ( x int, y int, z int );

Table created.

SQL> create or replace
  2  trigger trg1
  3  before insert on t1
  4  for each row
  5  begin
  6    :new.y := :new.x;
  7  end;
  8  /

Trigger created.

SQL> create or replace
  2  trigger trg2
  3  before insert on t1
  4  for each row
  5  begin
  6    :new.z := :new.y;
  7  end;
  8  /

Trigger created.

Seems simple enough…Copy ‘x’ into ‘y’, and then copy ‘y’ into ‘z’. So lets see what happens



SQL> insert into t1 (x) values (1);

1 row created.

SQL> select * from t1;

         X          Y          Z
---------- ---------- ----------
         1          1

Where did “Z” go ? What happened was TRG2 fired first, and then TRG1 fired. The firing order is indeterminate.

To solve this, we can use the FOLLOWS command to dictate the order in which triggers must fire.


SQL> create or replace
  2  trigger trg2
  3  before insert on t1
  4  for each row
  5  follows trg1
  6  begin
  7    :new.z := :new.y;
  8  end;
  9  /

Trigger created.

SQL> insert into t1 (x) values (1);

1 row created.

SQL> select * from t1;

         X          Y          Z
---------- ---------- ----------
         1          1
         1          1          1

SQL>
SQL>

So now you can see (if you have a lot of triggers) where FOLLOWS might come in useful.