Wednesday, August 17, 2016

Help me test a new Oracle Dev Gym feature: Classes

Back in June, we opened the "early adaptor" doors to the Oracle Dev Gym, a modern, mobile website on top of the PL/SQL Challenge's quiz platform.



We've gotten lots of great feedback and continue to develop the site (with plans to make it generally available in Q1 CY2017).

The next feature we will be releasing on the Dev Gym is Classes.

Classes will offer us multiple opportunities to present workouts and quizzes to users.

For example, on Friday 26 August, I will hold a "live" Dev Gym workout at a Chicago Oracle Group event. We will do this via a Class. Other classes in the future will be tied to monthly webcasts and more.

And now my request for help: I'd much rather expose and fix any bugs a week before the event, rather than at the event itself. :-)

So I have set up a test class for everyone starting at 18 August 15:00 UTC (10 AM US Central time). 

I'd like as many of you as possible to take the quizzes in the two hours that the class will be open - and if at all possible, start the workout promptly at 15:00.

It consists simply of three quizzes, so it won't take you long to do.

To take a class, simply click on the Classes link in the left side navigation bar:


Then click on the name of the class you want to take (there will only be one on Thursday):


Finally, click on the Join Class button and you will be added as a student for the class. You can do this anytime before and during the class. If you have turned on reminders for quizzes, you will also receive email reminders for this class.


If you join the class after it's started, you will be taken right to the class workout.

We are working hard to keep this site simple and intuitive, so I hope that you will be able to navigate through the class without any difficulty. 

If you do run into any sorts of problems, or have ideas for how we can improve Dev Gym Classes, just click on Feedback on the top right corner of the page:


Thanks in advance for your help!

Oh and if you have not yet taken quizzes at the PL/SQL Challenge or Dev Gym, now would be a great time to get started! 

Once you are signed in to your Oracle Account, you'll be ready to go.

Thursday, August 11, 2016

Qualify names of variables inside SQL statements! (an ode to fine-grained dependency)

Received via email recently:
I made a recommendation as part of our best PL/SQL best practices as follows. 
Always qualify all variable names with the Procedure name. Example:          insert_row.p_cust_name  instead of p_cust_name ​I recollect reading about this is in one of your articles and now I am being asked to justify why I have made this recommendation. Can you please help me convey to my team why this is a good practice?
My quick email response was:
I recommend that you qualify references to variables inside SQL statements embedded in your PLSQL code. This additional information given to the compiler can help it minimize invalidation of program units when dependent objects (such as tables) are changed. 
This feature is known as fine-grained dependency management, which was introduced in Oracle Database 11g. Prior to 11.1, the granularity of automatic dependency management in PL/SQL program units was the database object. 

Suppose, for example, that a procedure P referenced table T, such as with a %ROWTYPE or %TYPE anchored declaration or a SQL statement. Then in 10.2 and earlier, any change to the table whatsoever would cause P to be invalidated. Upon recompilation, the new information about T would be used.

This is quite a wonderful feature, making the need for a make utility in PL/SQL completely unnecessary. The down side of this coarse granularity, however, is that you might make any number of changes to a table that really do not have any impact on a program unit and it would still be invalidated.

Say that procedure P's only dependency on table T was this declaration:

my_dob T.birth_date%TYPE;

Well, then certainly if I add a new column to the procedure, it couldn't possibly affect P. Yet it would still be marked as invalid.

Starting with 11.1, however, the granularity of dependency shifted down a level. Now, table dependencies are tracked down to the column level. Program unit dependencies are shifted down to the parameter list.

The result should be way fewer invalidations of program units as tables are modified. Still, though, if you do not fully qualify all your PL/SQL constants and variables that appear inside SQL statements, you could still force unnecessary invalidations. The following example should make that clear to you.

I create a table and two procedures:

CREATE TABLE t (n NUMBER, d DATE)
/

CREATE OR REPLACE PROCEDURE use_t1
   AUTHID DEFINER
IS
   nn   NUMBER := 1;
   d    DATE;
BEGIN
   SELECT d INTO d
     FROM t
    WHERE n = nn;
END;
/

CREATE OR REPLACE PROCEDURE use_t2
   AUTHID DEFINER
IS
   nn   NUMBER := 1;
   d    DATE;
BEGIN
   SELECT d INTO d
     FROM t
    WHERE n = use_t2.nn;
END;
/

The only difference between use_t1 and use_t2 is that in use_t2 I have qualified the reference to nn in the WHERE clause:

    WHERE n = use_t2.nn;

They both compile without error:

  SELECT object_name || ' - ' || status and_now
    FROM user_objects
   WHERE object_name LIKE 'USE_T%'
ORDER BY object_name
/

AND_NOW
----------------
USE_T1 - VALID
USE_T2 - VALID

Suppose that I now add a new column named "nn" to my table:

ALTER TABLE t ADD nn NUMBER
/

When I run the above query a second time, I now see;

AND_NOW
----------------
USE_T1 - INVALID
USE_T2 - VALID

"But wait!" you are saying to yourself. "Steven just said that if you add a new column, it couldn't possibly affect an existing program, so they wouldn't be invalidated."

Well, I never said adding a column would never affect any program's status. And here you can clearly see the benefit of qualifying references to variables inside SQL:

In use_t1, with my unqualified reference to "nn",  the compiler cannot be certain that changes to tables will not affect the status of the program unit, so it is marked invalid. This is true even if the variable referenced does not share the name withe the new column. In other words, the following program unit is also marked invalid after the column is added:

CREATE OR REPLACE PROCEDURE use_t3
   AUTHID DEFINER
IS
   nnn   NUMBER := 1;
   d    DATE;
BEGIN
   SELECT d INTO d
     FROM t
    WHERE n = nnn;
END;
/

But if every single constant or variable that is placed inside a SQL statement in your PL/SQL code is fully qualified with the name of the program unit, then there is no ambiguity possible (a table can't have the same name as a program unit) and so adding new columns will not force an invalidation of program units that are dependent in some way on that table.

Check out this LiveSQL script on another example of fine-grained dependency management.

And here's the full script of the code shown above if you want to quickly give a try yourself.

CREATE TABLE t
(
   n   NUMBER,
   d   DATE
)
/

CREATE OR REPLACE PROCEDURE use_t1
   AUTHID DEFINER
IS
   nn   NUMBER := 1;
   d    DATE;
BEGIN
   SELECT d
     INTO d
     FROM t
    WHERE n = nn;
END;
/

CREATE OR REPLACE PROCEDURE use_t2
   AUTHID DEFINER
IS
   nn   NUMBER := 1;
   d    DATE;
BEGIN
   SELECT d
     INTO d
     FROM t
    WHERE n = use_t2.nn;
END;
/

CREATE OR REPLACE PROCEDURE use_t3
   AUTHID DEFINER
IS
   nnn   NUMBER := 1;
   d    DATE;
BEGIN
   SELECT d
     INTO d
     FROM t
    WHERE n = nnn;
END;
/

  SELECT object_name || ' - ' || status and_now
    FROM user_objects
   WHERE object_name LIKE 'USE_T%'
ORDER BY object_name
/

ALTER TABLE t
   ADD nn NUMBER
/

  SELECT object_name || ' - ' || status and_now
    FROM user_objects
   WHERE object_name LIKE 'USE_T%'
ORDER BY object_name
/

DROP TABLE t
/

DROP PROCEDURE use_t1
/

DROP PROCEDURE use_t2
/

DROP PROCEDURE use_t3
/

Tuesday, August 9, 2016

Is it time to always declare string variables as CLOBs and forget about VARCHAR2?

I received this email from a longtime Oracle Database developer:
Why shouldn't we always use clobs instead of varchar2 in PL/SQL? Memory will be allocated once used so there should not be any overhead always to use clobs instead of a varchar2. There must be a reason, however I'm unable to come up with a explanation. If it is true,  you might say the varchar2 is deprecated, ;-)
I had to laugh a bit when I saw this - not because I thought his question was funny or silly, but because it reminded me of what I sometimes say in my trainings.

I ask students:

Why don't we always declare all string variables as:

my_variable VARCHAR2(32767);

That way, we don't have to worry about a VALUE_ERROR exception if the string gets "too long". And since memory is allocated dynamically (for lengths above 4000), it won't cause excessive memory consumption.

And my answer(s) to this question are:

  1. This declaration - especially appearing all over the place - will raise questions in a developer's mind, including: "Will my strings get that large? Really?" and "What was wrong with the person who wrote this code?" Your code should answer not raise questions.
  2. You are assuming the maximum length of a VARCHAR2 will always be 32767. Maybe someday the maximum length will go up. Maybe someday there will be no difference between VARCHAR2 and CLOBs!
And now a developer wants to know: is there a difference? Maybe it's time to go all-out-CLOBBERS!

Or maybe not. It's true that you can now use many of the typical string functions on CLOBs, such as SUBSTR. You are no longer forced to use DBMS_LOB (though that can still be very helpful). But is there really no difference "under the covers"? Is there no penalty to be paid by switching to CLOBs?

Connor McDonald, a member of my Developer Advocates team and one half of the Ask Tom Answer Team, offers these demonstrations of some of the big differences between VARCHAR2 and CLOB:

SQL> create or replace
  2  procedure p2 is
  3   c clob;
  4  begin
  5    for i in 1 .. 1000000 loop
  6      c := rpad(i,100);
  7    end loop;
  8  end;
  9  /

Procedure created.

SQL> set timing on
SQL> exec p2

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.07

SQL> exec p2

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.06

SQL> create or replace
  2  procedure p2 is
  3   c varchar2(200);
  4  begin
  5    for i in 1 .. 1000000 loop
  6      c := rpad(i,100);
  7    end loop;
  8  end;
  9  /

Procedure created.

SQL> set timing on
SQL> exec p2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29

SQL> exec p2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.30

SQL> exec p2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.30

And similarly, there's some significant memory allocation differences:

SQL> create or replace
  2  procedure p2a is
  3    type t is table of clob index by pls_integer;
  4    c t;
  5  begin
  6    for i in 1 .. 100000 loop
  7      c(i) := 'qweqweqweqweqw';
  8    end loop;
  9  end;
10  /

Procedure created.

SQL> create or replace
  2  procedure p2b is
  3    type t is table of varchar2(50) index by pls_integer;
  4    c t;
  5  begin
  6    for i in 1 .. 100000 loop
  7      c(i) := 'qweqweqweqweqw';
  8    end loop;
  9  end;
10  /

Procedure created.

SQL> conn mcdonac/*****
Connected.

SQL> exec p2a

PL/SQL procedure successfully completed.

SQL> @stat
Enter value for stat_prefix: pga

SID_STAT# NAME                                                    VALUE
---------- ----------------------------------- ----------
        37 session pga memory                  2360216
        38 session pga memory max              22610840

SQL> conn mcdonac/*****
Connected.

SQL> exec p2b

PL/SQL procedure successfully completed.

SQL> @stat
Enter value for stat_prefix: pga

SID_STAT# NAME                                                    VALUE
---------- ----------------------------------- ----------
        37 session pga memory                  918424
             38 session pga memory max              10486680 

So I would say the answer is pretty clear: only use CLOBs when you are working with strings whose length are greater than 32767.

The time to declare VARCHAR2s "deprecated" has not yet arrived!

And I bet my readers can offer lots of other reasons/demonstrations of the advantages of sticking to VARCHAR2. Well? Well? :-)

Wednesday, August 3, 2016

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts

Sten Vesterli published a very thought-provoking post on his blog:


Please stop reading this post, and read that one. When you are done, come on back here for my thoughts on Sten's thoughts.

OK. You read it. Here we go.

First, thanks, Sten, for being such an interesting, wise, sometimes provocative voice in our community.

Next, Sten writes:

Now, on the one hand, I certainly agree that the vast majority of young developers are currently caught up in the modern version of a Gold Rush, which is: "Build an app using JavaScript, pay no attention to that database behind the curtain."

But I can assure you that I still do meet young PL/SQL programmers, regularly, when I am at conferences and doing onsite presentations at companies.

So, young person who writes PL/SQL: do not be afraid! You are not alone! And you are super-smart to have made the choice you did. :-)

Next, Sten offers this advice to managers:


I agree that PL/SQL is a "specialized technology" - it always has been, almost by definition: it is a special-purpose database programming language, and used only within Oracle Database.

But I do think there is another really big "place" in which PL/SQL should be leveraged: 

Any application built on top of Oracle Database

The bottom line is that you cannot build a fully optimized, secure and easy-to-maintain application on Oracle Database without PL/SQL. Doesn't matter if the application is for batch processing, transaction processing, or otherwise.

Sure, you can build an app without PL/SQL. But if you use the database only as a bit bucket, minimizing use of SQL and avoiding PL/SQL all-but-entirely, you will end up with an insecure mess on your hands.

I am not going to go into full "YesPLSQL" mode in this post, though. Instead, I encourage you to check out Bryn Llewellyn's Why Use PL/SQL whitepaper, and his promotion of the "Thick Database" paradigm.

Back to the word "specialized" - I do think that over time we will see a certain narrowing of roles within dev teams. I don't see it as highly likely that JavaScript developers will ever take the time to learn SQL, much less PL/SQL, well enough to avoid botching it up. 

So if I were a fully enlightened manager (feel free to check with my team if you'd like to find out my current status :-) ), I would have one or more specialized people on my team who know Oracle Database inside-out from a developer perspective, and can serve up clean, secure, fast APIs to underlying data and business rules.

Next, Sten offers some advice to developers:


I agree with the first point: it is, unfortunately, impractical to try to convince a developer with no immediate prospects for applying their SQL and PL/SQL knowledge to get up to speed on it.

I don't quite agree with the second point. I suggest that all PL/SQL developers have at least a working familiarity with Java and JavaScript.

Java because you can implement Java classes in the database to complement/extend the reach of PL/SQL. 

JavaScript because you at least need to be aware of what and how many other developers are writing code these days.

Thanks, again, Sten, for this very interesting contribution to an important conversation in our community. 

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