Thursday, July 28, 2016

Contacting players in the PL/SQL Challenge

Just fielded this question:
Hi, Is there any option to drop messages to different user? I would love to take help from all the top users from here.
Yes, I am sure you would love that! Hundreds of really sharp Oracle Database developers answer quizzes on the PL/SQL Challenge.

Wouldn't it be great if you could get in touch with them, with a question?

And the answer is: "You can! But only if they have enabled that option."

For example, the top player in the PL/SQL Challenge in terms of overall points is Stelios Vlasopoulos. He has also taken over 2,000 quizzes - no, wait a minute! over 3,100 quizzes! Wow, Stelio!

And if you click on his name in the Player tab (or anywhere else in the site where his name appears as a link), you will see:


The clue "Contact" link is there because in Stelios' Public Profile Settings, he has enabled contact:


So you can go right ahead and get in touch with Stelios. Of course, if many players abuse this privilege, he and others can turn off the setting.

But in the meantime, do feel encouraged to reach out to other PL/SQL Challenge players and ask for help!

Wednesday, July 27, 2016

A quick guide to writing dynamic SQL in PL/SQL


[I responded to a tweet today about why you would ever use DBMS_SQL. The answer is: for method 4 dynamic SQL implementations - and that prompted me to write a post on the topic.]

Dynamic SQL refers to SQL statements that are constructed and executed at runtime. Dynamic is the opposite of static. Static SQL (also known as Embedded SQL) refers to SQL statements that are fully specified, or fixed, at the time the code containing that statement is compiled. Dynamic PL/SQL refers to entire PL/SQL blocks of code that are constructed dynamically, then compiled and executed.

There are, broadly, two ways to execute dynamic SQL and PL/SQL:

Native dynamic SQL - use native PL/SQL statements - EXECUTE IMMEDIATE or OPEN FOR - to handle your dynamic SQL needs.

DBMS_SQL - use a built-in package with a big, fat API to parse, execute, etc. your dynamic SQL statements.

Oh, great. Two ways to do it, with the obvious next question: which one should you use?

First, the short answer, then the long answer.

Short Answer

Use native dynamic SQL, which means - almost always - use EXECUTE IMMEDIATE. It is very easy to use and is quite efficient.

There is really just one general use case for DBMS_SQL these days (Oracle Database 11g and higher): method 4 dynamic SQL. 

Which brings me to the long answer.

Long Answer

Three are four methods of dynamic SQL:

Method 1: The simplest kind of dynamic statement. Either a DDL statements (e.g., CREATE TABLE) or a non-query DML (update, insert, delete, merge) that has no bind variables. 

Examples using EXECUTE IMMEDIATE

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE my_table';
END;

[You cannot execute DDL statements natively in PL/SQL, as you could a SELECT. So you must put the statement inside quotes and execute it dynamically.]

PROCEDURE null_out_column (column_in IN VARCHAR2)
IS
BEGIN
   /* Basic protection against SQL injection */
   IF column_in NOT IN (<white list of columns>)
   THEN
      RAISE_APPLICATION_ERROR (-20000,
         'Unable to retrieve data');
   END IF;

   EXECUTE IMMEDIATE 
      'UPDATE my_table SET ' || column_in || ' = NULL';
END;

Method 2: Non-query DML statements with one or more bind variables, the number of which is known at compile time. 

Example using EXECUTE IMMEDIATE

PROCEDURE updnumval (
   col_in                     IN       VARCHAR2
 , start_in                   IN       DATE
 , end_in                     IN       DATE
 , val_in                     IN       NUMBER
)
IS
   l_update   varchar2(1000)
      :=    'UPDATE employees SET '
         || col_in
         || ' = :val 
        WHERE hire_date BETWEEN :lodate AND :hidate
          AND :val IS NOT NULL';
BEGIN
   /* Basic protection against SQL injection */
   IF numcol NOT IN ('SALARY', 'COMMISSION_PCT')
   THEN
      RAISE_APPLICATION_ERROR (-20000,
         'Unable to retrieve data');
   END IF;

   EXECUTE IMMEDIATE l_update
               USING val_in, start_in, end_in, val_in;
END;

Notice that I must provide val_in twice. Assignments of variables to placeholders is positional in dynamic SQL.

Since I know the number of placeholders in the string (4), I can use USING to bind the parameters in.

Method 3: a SELECT statement whose select list (number of elements returned by the query) and bind variables are fixed at compile-time. 

Example using EXECUTE IMMEDIATE

FUNCTION best_ever_ranking_in (
   ranking_mview_in IN VARCHAR2, user_id_in IN INTEGER) 
   RETURN INTEGER
IS
   l_ranking     INTEGER;
BEGIN
   EXECUTE IMMEDIATE
        'SELECT MAX (ranking) FROM ' 
      || sys.DBMS_ASSERT.sql_object_name (ranking_mview_in) 
      || ' WHERE user_id = :empid'
      INTO l_ranking
      USING user_id_in;

   RETURN l_ranking;
END;

This is an example from the PL/SQL Challenge. We have a set of materialized views that hold player rankings (refreshed weekly). To get the lifetime best ranking for an individual, I pass in the name of the materialized view and the user ID.

Since I know the number of elements in the SELECT list (1), I can use the INTO clause of EXECUTE IMMEDIATE to get it back.

Since I know the number of placeholders in the string (1), I can use USING to bind the parameter in.

Method 4: "I know what I don't  know." At the time I write my code, I don't know how many elements will be in my SELECT list and/or I don't know the number of variables that may need to be bound to placeholders. Wow. I don't know very much, do I?

Method 4 is hard to implement, because you have to write very flexible code. And it is extremely difficult (but not impossible) to do this with EXECUTE IMMEDIATE, because the INTO and USING clauses are static.

As a result, DBMS_SQL is usually the best implementation mechanism for method 4 dynamic SQL. It provides a very granular API that gives you complete control over every step in the dynamic SQL flow, including: create cursor, parse statement, bind variables, execute statement, get column values, close cursor.

And that's just great - if you need it. A loooong time ago, before EXECUTE IMMEDIATE was available, DBMS_SQL was mostly just a big pain, because of all the code you had to write even for simple scenarios.

Here's an example of using DBMS_SQL to implement a method 3 program  that displays the values of any two columns (well, one's of type number, another type string) in employees:

PROCEDURE showemps (
   numcol IN VARCHAR2,
   strcol IN VARCHAR2)
IS
   cur INTEGER := DBMS_SQL.OPEN_CURSOR;
   rec employees%ROWTYPE;
   fdbk INTEGER;
   loc INTEGER;
BEGIN
   /* Basic protection against SQL injection */
   IF numcol NOT IN ('SALARY', 'COMMISSION_PCT')
   THEN
      RAISE_APPLICATION_ERROR (-20000,
         'Unable to retrieve data');
   END IF;

   DBMS_SQL.PARSE
     (cur, 'SELECT ' || numcol || ', ' || strcol || 
            ' FROM employees',
      DBMS_SQL.NATIVE);

   DBMS_SQL.DEFINE_COLUMN (cur, 1, 1);
   DBMS_SQL.DEFINE_COLUMN (cur, 2, 'a', 30);

   fdbk := DBMS_SQL.EXECUTE (cur);

   LOOP
      /* Fetch next row. Exit when done. */
      EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0;
      DBMS_SQL.COLUMN_VALUE (cur, 1, rec.employee_id);
      DBMS_SQL.COLUMN_VALUE (cur, 2, rec.last_name);
      DBMS_OUTPUT.PUT_LINE (
         TO_CHAR (rec.employee_id) || '=' || rec.last_name);
   END LOOP;

   DBMS_SQL.CLOSE_CURSOR (cur);
END;

So pretty clearly you do not want to use DBMS_SQL unless you need to, and method 4 is pretty much exactly when you need to.

Showing you how to use DBMS_SQL for method 4 in a blog post leads to a really, really long blog post. So instead, I suggest you check out the LiveSQL script listed below. It should give you a pretty good feel for at least some of the steps involved. But to summarize:

If you do not know the number of items in the SELECT list, you will need to execute calls to DEFINE_COLUMN within a loop, as in:

   WHILE (l_index IS NOT NULL)
   LOOP
      IF is_string (columns_in, l_index)
      THEN
         DBMS_SQL.define_column (cursor_io,
                                 l_index,
                                 'a',
                                 columns_in (l_index).data_length);
      ELSIF is_number (columns_in, l_index)
      THEN
         DBMS_SQL.define_column (cursor_io, l_index, 1);
      ELSIF is_date (columns_in, l_index)
      THEN
         DBMS_SQL.define_column (cursor_io, l_index, SYSDATE);
      END IF;

      l_index := columns_in.NEXT (l_index);

   END LOOP;

And if you don't know how many bind variables there are, then you will need to execute calls to BIND_VARIABLE in a loop, something like this:

PROCEDURE method4_bind_variables (
   sql_in            IN VARCHAR2
 , placeholders_in   IN DBMS_SQL.varchar2a
 , values_in         IN DBMS_SQL.varchar2a)
IS
   l_cursor     INTEGER := DBMS_SQL.open_cursor;
   l_feedback   PLS_INTEGER;
BEGIN 
   /* WARNING: a program like this should NEVER be available
      directly to an end user - that is, a user should not be
      able to enter a SQL statement and leave it to you to 
      execute it for you! Major SQL injection opening. */

   DBMS_SQL.parse (l_cursor, sql_in, DBMS_SQL.native);

   FOR indx IN 1 .. values_in.COUNT
   LOOP
      DBMS_SQL.bind_variable (l_cursor
                            , placeholders_in (indx)
                            , values_in (indx));
   END LOOP;

   l_feedback := DBMS_SQL.execute (l_cursor);

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
   
   DBMS_SQL.close_cursor (l_cursor);
END;

Mostly, though, you will simply not need to bother with DBMS_SQL, because true method 4 dynamic SQL requirements are rare.

Here are some resources to help you explore dynamic SQL further:

Practically Perfect PL/SQL Video Playlist on Dynamic SQL
LiveSQL Script - Method 2
LiveSQL Script - Method 3
PL/SQL Documentation
PL/SQL Challenge Quizzes
Oracle Magazine Article
oracle-developer.net article on method 4

Tuesday, July 26, 2016

Watch out for redundant code with WHILE loops

Generally, you should use a simple loop if you always want the body of the loop to execute at least once. You use a WHILE loop if you want to check before executing the body the first time. Since the WHILE loop performs its check “up front,” the variables in the boundary expression must be initialized. The code to initialize is often the same code needed to move to the next iteration in the WHILE loop. This redundancy creates a challenge in both debugging and maintaining the code: how do you remember to look at and update both?

If you find yourself writing and running the same code before the WHILE loop and at end of the WHILE loop body, consider switching to a simple loop.

Here's an example.
I write a procedure to calculate overdue charges for books; the maximum fine to be charged is $10, and I will stop processing when there are no overdue books for a given date. Here is my first attempt at the procedure body:

DECLARE
   l_fine PLS_INTEGER := 0;
   l_date DATE := SYSDATE;
   l_overdue_count NUMBER;
BEGIN
   l_overdue_count :=
      overdue_pkg.countem (
         borrower_id => borrower_in,
         l_date);
  
   WHILE (l_overdue_count > 0 AND l_fine < 10)
   LOOP
      update_fine_info (l_date, l_one_day_fine);

      l_fine := l_fine + l_one_day_fine;
      l_date := l_date + 1;

      l_overdue_count :=
         overdue_pkg.countem (
            borrower_id => borrower_in,
            l_date);
   END LOOP;
END;   

As is readily apparent, I duplicate the assignments of values to l_overdue_count. I would be far better off rewriting this code as follows:

DECLARE
   l_fine PLS_INTEGER := 0;
   l_date DATE := SYSDATE;
   l_overdue_count NUMBER;
BEGIN
   LOOP
      EXIT WHEN
        (l_overdue_count <= 0 OR l_fine >= 10)
     
      update_fine_info (l_date, l_one_day_fine);
     
      l_fine := l_fine + l_one_day_fine;
     
      l_date := l_date + 1;

      l_overdue_count :=
         overdue_pkg.countem (
            borrower_id => borrower_in,
            l_date);
   END LOOP;
END;   


By paying close attention to your loop construction, you can avoid redundant code, always bad news in a program, since it increases maintenance costs and the chance of introducing bugs into your code.

Friday, July 15, 2016

Feedback needed for the Oracle Dev Gym dashboard

Waaaaaay back on June 27, we opened up the doors to the Oracle Dev Gym, our next generation website built on the quiz platform that powers the PL/SQL Challenge.

One of the big changes for Dev Gym is a de-emphasis on the competitive aspect of quizzes, combined with making it much, much easier for a visitor to quickly take a quiz from our library of 2000+ quizzes.

So far, overall, feedback has been quite positive, and we are very pleased.

But a number of longtime players have offered feedback like the following:
About Dev Gym: It is much easier to find what you want to do with this layout. The only thing I miss is the Dashboard where I can see at a glance the results of the latest quiz and maybe "Top Players (and You)", especially when I'm not at the first page of the Leaderboard :-)
The home page of the PL/SQL Challenge offers both a dashboard (sorry about this "empty" example, but as a site admin, I cannot compete):


and a Top Players (and You) section:


We will show lots of the Top Players report on our Leaderboard page, no problem there. But we'd like to add a high-level dashboard to the Dev Gym home page.

Requirements for this dashboard include:
  • Simple, clean presentation: keep the amount of text and data to a minimu
  • Make it interesting for both competitive players and those simple wanting to strengthen their skills
So I have decided to ask our players to give us feedback: what would you prioritize for display in the dashboard? Can you point to any other sites that present this sort of data in a way you find helpful, intuitive, dazzling?

Thanks!
Steven


Tuesday, July 12, 2016

Audit changes: differentiate between user change and app change

I learned an important lesson over the last few days.

You all probably know this already, but as you may also know I am generally not reticent to expose my relative ignorance.

So I follow a standard of adding four audit columns to my tables, populated by triggers, which keep track of who inserted/updated the row, and when:


I certainly did this for the qdb_users table, which is the users table for the PL/SQL Challenge and Oracle Dev Gym.

So far, so good.

But recently a player complained that she was not receiving emails with results of her quizzes. I checked and found that the preference was turned off. Had she modified her user profile lately or had my code done something to her row?

It was pretty much impossible to tell, because we keep track of the user's last visit to the site - which means the app itself updates the qdb_users.changed_by/on columns every time a user comes to the site. This would overwrite whatever the user's last changed_on value was.

Yuch.

I was using a "low level" audit column to also keep track of user-level behavior, with the result being a loss of information. 

So I added a new column (changed_on_by_user), which is updated only when the user executes an action that updates his or her profile - all controlled through my PL/SQL API:



Lesson learned: don't mix system information (row-level audit information) and application/user information. Keep them separate, making it much easier (possible!) to track activity within your application!