Wednesday, May 25, 2016

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages):

The Oracle Dev Gym!



In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love. 

We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year.

Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16, where I will present it to a packed room of APEX experts).

What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards.

Regional Display Selector (RDS)

This is not a new feature to 5, but it sure works nicely and looks great! RDS gives you a way to easily create tabbed "sub-pages" on your page. You move oh-so-quickly between the content on each tab, because the page is not being refreshed. 


And they are so easy to set up! You define the "tab" region with a type of Region Display Selector:


Then if you want a region to be appear as a tab, simply change the RDS setting in Properties for that region to Yes.


This sort of high-level declarative approach to designing sites makes me happy. Sure, the RDS feature could be more configurable, and I am sure it will be in future releases. In the meantime, it offers a great combination of super productivity and nice UI with minimal coding.

Cards

So in our redesign of PL/SQL Challenge, we wanted to accomplish, hmmm, well, actually many things, but here was a big one: make the site responsive. Allow users to easily take a quiz on their tablet, maybe even their phone. 

You can do it with the current set, as players have told me so, but it isn't the most pleasant experience. We simply didn't design the site with that in mind five years ago.

But now? It's a necessity and fortunately APEX 5 generally and the Universal Theme in particular makes that much easier to accomplish. 

One key element is our shift to using cards. The first image in this post shows the Dev Gym home page, featuring big, attractive "buttons" a user can click or press to make their selection. These are actually "cards".

What this means is that I create a region of type Classic Report. I then drill down to the Attributes for the region, and set the template to Cards:


I can then set all sorts of options. For example, I can choose to Display Icons and then simply specify a FontAwesome icon by name, associating in the query with a card_icon column. 


That's it. From there APEX 5 takes over and does its beautiful job. Of course, if you want to tweak the built-in behavior and so some really "fancy" stuff, then you can use JavaScript.

You can. I can't, because I have not learned JavaScript. Fortunately, that's been one of Eli's main foci in his APEX work, so I have him work that magic.

There is one particular usage of Cards we are exploring that replaces a LOV selector against a tree. I think the approach is very intuitive and way better suited to mobile device usage. We will soon see what our users think of it.

So bottom line: if you are using Oracle Application Express 5 you really owe it to yourself to explore and make best use of these two great features: the Region Display Selector and Cards.

Thursday, May 19, 2016

While in Chicago for Kscope16...

I've gotten a request or two to recommend places to eat and drink while in Chicago for Kscope16.

Confession: I am a home body, a creature of habit, and definitely not a downtown body. So....I don't have much of any ideas to offer in terms of eating around the conference hotel downtown. But I can certainly share with you some of my favorite restaurants on the north side of Chicago.

Italian

Anna Maria's Pasteria - lovely, small, neighborhood restaurant

Via Venetto - I am not sure I have ever tasted anything better than their Fagottini Di Ricotta E Pere 

Mediterranean

Andie's - in the heart of Andersonville, we've been going to this restaurant for 20+ years.

Seafood

Glenn's Diner - excellent seafood, not too expensive, small diner experience

Mariscos El Venero  - Mexican seafood restaurant where Mexicans go. Nothing fancy, but amazing food and great experience overall!

Indian

If you really like Indian food and/or culture, you should visit Devon Avenue. It is a remarkable stretch of road in Chicago. Decades ago, predominantly Jewish (and the larger neighborhood, Rogers Park, still has one of the largest concentrations of Orthodox Jews in the country), it has gradually shifted to the Midwest center of "Desi" (Pakistani and Indian) food and culture.

Park and wander. Some restaurants you will be sure to enjoy: Hema's, Udupi, Viceroy.

Plus, I live just a 1/4 mile away - so you can wave to me as you chow down on wonderful food!

Besides Food

I don't know about you, but I get tired of steel and concrete. I yearn for the green. Chicago is justifiably famous for its lakefront parks and trails. The Sheraton is close to the lake, and you should be able to rent a bicycle easily and enjoy lovely Lake Michigan.

In addition, the beautiful Northpark Village Nature Center would make a nice afternoon or morning getaway.

Friday, May 13, 2016

Types of Cursors Available in PL/SQL

Let's take a look at the different ways you can define and use cursors (pointers to SQL result sets) in PL/SQL, including: implicit cursor, explicit cursor, cursor expressions, cursor variables, DBMS_SQL cursor handles. 

Check out the LiveSQL script that demonstrates the points made in this post.

SELECT-INTO (aka, Implicit Cursor)

The SELECT-INTO statement is called an implicit cursor, because we write the SELECT statement, and the PL/SQL engine takes care of all the cursor operations for us implicitly: open, fetch, close.

SELECT-INTO is the best (smallest amount of code, best performance) way to fetch a single row.

Here's an example, based on the standard HR schema.
PACKAGE BODY employee_mgr
IS
   FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE)
      RETURN hr.employees%ROWTYPE
   IS
      onerow_rec   hr.employees%ROWTYPE;
   BEGIN
      SELECT *
        INTO onerow_rec
        FROM employees
       WHERE employee_id = employee_id_in;

      RETURN onerow_rec;
   END;
END;
SELECT-INTO famously can raise (at least) two exception:
  1. NO_DATA_FOUND - zero rows were found for the specified WHERE clause.
  2. TOO_MANY_ROWS - two or more rows were found for the specified WHERE clause.
When you write a SELECT-INTO, you should decide how you want to handle these exceptions - and you should also be aware of how different they are. In many situations, NO_DATA_FOUND doesn't even indicate a real error. It probably just reflects a data state: no row yet in database for that primary or unique key. TOO_MANY_ROWS almost always indicates a serious data error, such a disabled constraint or missing unique index, leading to bad data.

So usually when I write functions that fetch single rows (or individual column values from a row), I write something more like this:
PACKAGE BODY employee_mgr
IS
   FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE)
      RETURN hr.employees%ROWTYPE
   IS
      onerow_rec   hr.employees%ROWTYPE;
   BEGIN
      SELECT *
        INTO onerow_rec
        FROM employees
       WHERE employee_id = employee_id_in;

      RETURN onerow_rec;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN NULL;
      WHEN TOO_MANY_ROWS
      THEN
         /* Replace with your logging routine, such as logger.log_error */
         log_error ('employee_mgr.onerow', 'Too many rows for '|| employee_id_in);
   END;
END;
SELECT-INTO Documentation

CURSOR xxx IS (aka, Explicit Cursor)

With an explicit cursor, you associate a SELECT statement with an cursor that you declare in a declaration section or package specification. You can even define a parameter list for your cursor, just as you would a function. Then you get to control all aspects or cursor operation: open, fetch, close. If that's what you want. Generally do not use explicit cursors for single row lookups; implicits are simpler and faster.

But because you have total control, you also have lots of flexibility, Takes a look at some examples. First, I create and populate a table:
CREATE TABLE endangered_species 
( 
   common_name    VARCHAR2 (100), 
   species_name   VARCHAR2 (100) 
)
/

BEGIN
   INSERT INTO endangered_species
        VALUES ('Amur Leopard', 'Panthera pardus orientalis');

   INSERT INTO endangered_species
        VALUES ('Hawksbill Turtle', 'Eretmochelys imbricata');

   INSERT INTO endangered_species
        VALUES ('Javan Rhino', 'Rhinoceros sondaicus');

   COMMIT;
END;
I can then define and use an explicit cursor as follows:
DECLARE 
   CURSOR species_cur 
   IS 
        SELECT * 
          FROM endangered_species 
      ORDER BY common_name; 
 
   l_species   species_cur%ROWTYPE; 
BEGIN 
   OPEN species_cur; 
   FETCH species_cur INTO l_species; 
   CLOSE species_cur; 
END;
Notice that I define my record based on the cursor, not the underlying table. That way, if the SELECT list ever changes in the cursor's query, I don't have to change the record declaration. It's anchored back to the cursor.

Then I open, fetch, close. That block shows the basic operations, but a reminder: if all you are doing is fetching a single row, once, like this, use SELECT-INTO.

There is, however, more that I can do. Here's an example of a cursor with a parameter:
DECLARE 
   CURSOR species_cur (filter_in IN VARCHAR2) 
   IS 
        SELECT * 
          FROM endangered_species 
         WHERE species_name LIKE filter_in 
      ORDER BY common_name; 
 
   l_species   species_cur%ROWTYPE; 
BEGIN 
   OPEN species_cur ('%u%'); 
   FETCH species_cur INTO l_species; 
   CLOSE species_cur; 
 
   /* Use same cursor a second time, avoiding copy-paste of SQL */ 
   OPEN species_cur ('%e%'); 
   FETCH species_cur INTO l_species; 
   CLOSE species_cur; 
 
   /* I can even use it in a cursor FOR loop */ 
   FOR rec IN species_cur ('%u%') 
   LOOP 
      DBMS_OUTPUT.PUT_LINE (rec.common_name); 
   END LOOP; 
END;
So you can see that I can reuse my explicit cursor, but I cannot reuse an implicit cursor in the same way. With an implicit, I would have to put the SELECT-INTO inside a function and call the function. But, hey, that's what I recommend you do anyway! :-)

Another nice feature of the explicit cursor is that you can declare the cursor in the specification, but not show the SQL. Then you define the cursor (including the SELECT) in the package body - as shown in next step. This way, you hide the details of the query and "force" the user of the cursor to simply rely on its documented specification.
CREATE PACKAGE species_pkg 
IS 
   CURSOR species_cur 
      RETURN endangered_species%ROWTYPE; 
END;
/

CREATE PACKAGE BODY species_pkg 
IS 
   CURSOR species_cur 
      RETURN endangered_species%ROWTYPE 
   IS 
        SELECT * 
          FROM endangered_species 
      ORDER BY common_name; 
END;
/
Explicit Cursor Documentation

Cursor FOR Loop

The cursor FOR Loop is one of my favorite PL/SQL features. No need to open, fetch, close. Just tell the PL/SQL engine you want to work with each row returned by the query. Plus, with your optimization level set to 2 (the default) or higher, this code is automatically optimized to return 100 rows with each fetch (similar to BULK COLLECT).

You can embed the SELECT statement within the loop header, or you can declare an explicit cursor and reference it by name in the loop header. That means you can, again, parameterize the cursor and reuse it in multiple loops.
BEGIN 
   FOR rec IN (  SELECT * 
                   FROM endangered_species 
               ORDER BY common_name) 
   LOOP 
      DBMS_OUTPUT.put_line (rec.common_name); 
   END LOOP; 
END;
/

DECLARE 
   CURSOR species_cur 
   IS 
        SELECT * 
          FROM endangered_species 
      ORDER BY common_name; 
 
   PROCEDURE start_conservation_effort 
   IS 
   BEGIN 
      DBMS_OUTPUT.put_line ('Remove human presence'); 
   END; 
BEGIN 
   FOR rec IN species_cur 
   LOOP 
      DBMS_OUTPUT.put_line (rec.common_name); 
   END LOOP; 
 
   FOR rec IN species_cur 
   LOOP 
      start_conservation_effort; 
   END LOOP; 
END;
/
Things to keep in mind with cursor FOR loops:
  • Never use a cursor FOR loop to fetch a single row. It's a lazy way to avoid declaring the INTO variable or record, and that's bad lazy.
  • If you need to iterate through rows of data but then conditionally exit the loop under certain data conditions, use a WHILE or simple loop with an explicit cursor. Why? Because...
  • Any kind of FOR loop is saying, implicitly, "I am going to execute the loop body for all iterations defined by the loop header." (N through M or SELECT) Conditional exits means the loop could terminate in multiple ways, resulting in code that is hard to read and maintain.
Cursor FOR Loop Documentation

Cursor Variables

A cursor variable is, well, just that: a variable pointing back to a cursor/result set. Some really nice aspects of cursor variables, demonstrated in this package: you can associate a query with a cursor variable at runtime (useful with both static and dynamic SQL); you can pass the cursor variable as a parameter or function RETURN value (specifically: you can pass a cursor variable back to a host language like Java for consumption).

Here's a package and script that demonstrates several cursor variable features
CREATE OR REPLACE PACKAGE refcursor_pkg 
IS 
   /* Use this "strong" REF CURSOR to declare cursor variables whose 
      queries return data from the endangered_species table. */ 
 
   TYPE endangered_species_t IS REF CURSOR 
      RETURN endangered_species%ROWTYPE; 
 
   /* Use a "weak" REF CURSOR to declare cursor variables whose 
      queries return any number of columns. 
 
      Or use the pre-defined SYS_REFCURSOR, see example below. 
   */ 
 
   TYPE weak_t IS REF CURSOR; 
 
   FUNCTION filtered_species_cv (filter_in IN VARCHAR2) 
      RETURN endangered_species_t; 
 
   /* Return data from whatever query is passed as an argument. */ 
   FUNCTION data_from_any_query_cv (query_in IN VARCHAR2) 
      RETURN weak_t; 
 
   /* Return data from whatever query is passed as an argument. 
      But this time, use the predefined weak type. */ 
   FUNCTION data_from_any_query_cv2 (query_in IN VARCHAR2) 
      RETURN SYS_REFCURSOR; 
END refcursor_pkg;
/

CREATE OR REPLACE PACKAGE BODY refcursor_pkg 
IS 
   FUNCTION filtered_species_cv (filter_in IN VARCHAR2) 
      RETURN endangered_species_t 
   IS 
      l_cursor_variable   endangered_species_t; 
   BEGIN 
      IF filter_in IS NULL 
      THEN 
         OPEN l_cursor_variable FOR SELECT * FROM endangered_species; 
      ELSE 
         OPEN l_cursor_variable FOR 
            SELECT * 
              FROM endangered_species 
             WHERE common_name LIKE filter_in; 
      END IF; 
 
      RETURN l_cursor_variable; 
   END filtered_species_cv; 
 
   FUNCTION data_from_any_query_cv (query_in IN VARCHAR2) 
      RETURN weak_t 
   IS 
      l_cursor_variable   weak_t; 
   BEGIN 
      OPEN l_cursor_variable FOR query_in; 
 
      RETURN l_cursor_variable; 
   END data_from_any_query_cv; 
 
   FUNCTION data_from_any_query_cv2 (query_in IN VARCHAR2) 
      RETURN SYS_REFCURSOR 
   IS 
      l_cursor_variable   SYS_REFCURSOR; 
   BEGIN 
      OPEN l_cursor_variable FOR query_in; 
 
      RETURN l_cursor_variable; 
   END data_from_any_query_cv2; 
END refcursor_pkg;
/

DECLARE 
   l_objects   refcursor_pkg.endangered_species_t; 
   l_object    endangered_species%ROWTYPE; 
BEGIN 
   l_objects := refcursor_pkg.filtered_species_cv ('%u%'); 
 
   LOOP 
      FETCH l_objects INTO l_object; 
 
      EXIT WHEN l_objects%NOTFOUND; 
 
      DBMS_OUTPUT.put_line (l_object.common_name); 
   END LOOP; 
 
   CLOSE l_objects; 
END;
/
Bottom line: Once you've got a cursor variable, you can use all the familiar features of explicit cursors with them: fetch, close, check cursor attribute values.

Cursor Variable Documentation

Cursor Expressions

A cursor expression, in essence, converts a subquery (SELECT statement) into a nested cursor (cursor variable).

Switching back to the HR schema, notice my use of CURSOR inside the all_in_one_cur. My INTO can then deposit the set return by CURSOR directly into a cursor variable defined in my PL/SQL block. I can then use standard cursor processing to fetch rows from that cursor variable.

CREATE OR REPLACE PROCEDURE cursor_expression_demo (location_id_in NUMBER) 
IS 
   /* Notes on CURSOR expression: 
 
      1. The query returns only 2 columns, but the second column is 
         a cursor that lets us traverse a set of related information. 
 
      2. Queries in CURSOR expression that find no rows do NOT raise 
         NO_DATA_FOUND. 
   */ 
   CURSOR all_in_one_cur 
   IS 
      SELECT l.city, 
             CURSOR (SELECT d.department_name, 
                            CURSOR (SELECT e.last_name 
                                      FROM hr.employees e 
                                     WHERE e.department_id = d.department_id) 
                               AS ename 
                       FROM hr.departments d 
                      WHERE l.location_id = d.location_id) 
                AS dname 
        FROM hr.locations l 
       WHERE l.location_id = location_id_in; 
 
   department_cur   SYS_REFCURSOR; 
   employee_cur     SYS_REFCURSOR; 
   v_city           hr.locations.city%TYPE; 
   v_dname          hr.departments.department_name%TYPE; 
   v_ename          hr.employees.last_name%TYPE; 
BEGIN 
   OPEN all_in_one_cur; 
 
   LOOP 
      FETCH all_in_one_cur INTO v_city, department_cur; 
 
      EXIT WHEN all_in_one_cur%NOTFOUND; 
 
      -- Now I can loop through deartments and I do NOT need to 
      -- explicitly open that cursor. Oracle did it for me. 
      LOOP 
         FETCH department_cur INTO v_dname, employee_cur; 
 
         EXIT WHEN department_cur%NOTFOUND; 
 
         -- Now I can loop through employee for that department. 
         -- Again, I do need to open the cursor explicitly. 
         LOOP 
            FETCH employee_cur INTO v_ename; 
 
            EXIT WHEN employee_cur%NOTFOUND; 
            DBMS_OUTPUT.put_line (v_city || ' ' || v_dname || ' ' || v_ename); 
         END LOOP; 
 
         /* Not necessary; automatically closed with CLOSE all_in_one_cur
            See comment by Albert below.
         CLOSE employee_cur; */
      END LOOP; 
 
      /* Not necessary; automatically closed with CLOSE all_in_one_cur.
         See comment by Albert below.
      CLOSE department_cur; */
   END LOOP; 
 
   CLOSE all_in_one_cur; 
END;
/

BEGIN
   cursor_expression_demo (1700);
END;
/
Cursor expressions are also commonly used in streaming and pipelined table functions.  Here's an example:
INSERT INTO tickertable
   SELECT *
     FROM TABLE (stockpivot (CURSOR (SELECT *
                                       FROM stocktable)))
Cursor Expression Documentation

DBMS_SQL Cursor Handle

Most dynamic SQL requirements can be met with EXECUTE IMMEDIATE (native dynamic SQL). Some of the more complicated scenarios, however, like method 4 dynamic SQL (variable number of elements in SELECT list and/or variable number of bind variables) are best implemented by DBMS_SQL. You allocate a cursor handle and then all subsequent operations reference that cursor handle. Here's an example:
CREATE OR REPLACE PROCEDURE show_common_names (table_in IN VARCHAR2)  
IS  
   l_cursor     PLS_INTEGER := DBMS_SQL.open_cursor ();  
   l_feedback   PLS_INTEGER;  
   l_name       endangered_species.common_name%TYPE;  
BEGIN  
   DBMS_SQL.parse (l_cursor,  
                   'select common_name from ' || table_in,  
                   DBMS_SQL.native);  
  
   DBMS_SQL.define_column (l_cursor, 1, 'a', 100);  
  
   l_feedback := DBMS_SQL.execute (l_cursor);  
  
   DBMS_OUTPUT.put_line ('Result=' || l_feedback);  
  
   LOOP  
      EXIT WHEN DBMS_SQL.fetch_rows (l_cursor) = 0;  
      DBMS_SQL.COLUMN_VALUE (l_cursor, 1, l_name);  
      DBMS_OUTPUT.put_line (l_name);  
   END LOOP;  
  
   DBMS_SQL.close_cursor (l_cursor);  
END;
/

BEGIN
   show_common_names ('ENDANGERED_SPECIES');
END;
/

DBMS_SQL Documentation 

Almost all code in this blog post may be accessed and executed on LiveSQL.

Thursday, May 12, 2016

Why You Should ALWAYS Use Packages

Received this request via email today:
Hi Steven I our shop we have an ongoing debate regarding when to use packages and when to create a collection of procedures/functions. We are not aligned at all, some people here tend to ignore packages for some strange reasons, I would like to nudge them in the right direction, so that where you come into play.  
One of my co workers tried to look into a couple of you books to your argumentation for use of packages, and could not, to her astonishment, see any recommendations from you regarding this, can you point us in the right direction, surely you must have debated this issue somewhere.
This came as a bit of a surprise to me (inability to find recommendations from me on packages).

So I checked, and quickly found and reported back:
In my humongous 6th edition Oracle PL/SQL Programming, there is a whole chapter on packages (18) and on 651 I offer “Why Packages?”.  
In my Best Practices book I have a whole chapter on Packages and on page 207 I make the argument to ALWAYS use packages, not schema level subprograms.
In case, however, you don't have the books handy, here are my high-level thoughts on packages.

First, what are some of the key benefits of using packages?

Enhance and maintain applications more easily

As more and more of the production PL/SQL code base moves into maintenance mode, the quality of PL/SQL applications will be measured as much by the ease of maintenance as they are by overall performance. Packages can make a substantial difference in this regard. From data encapsulation (hiding all calls to SQL statements behind a procedural interface to avoid repetition), to enumerating constants for literal or “magic” values, to grouping together logically related functionality, package-driven design and implementation lead to reduced points of failure in an application.

Improve overall application performance

By using packages, you can improve the performance of your code in a number of ways. Persistent package data can dramatically improve the response time of queries by caching static data, thereby avoiding repeated queries of the same information (warning: this technique is not to be relied upon in stateless applications). Oracle’s memory management also optimizes access to code defined in packages (see OPP6 Chapter 24 for more details). 

In addition, when you invoke one subprogram in a package, the entire package is loaded into memory. Assuming you have designed your packages well (see "Keep your packages small and narrowly focused" below), this will 

Shore up application or built-in weaknesses

It is quite straightforward to construct a package on top of existing functionality where there are drawbacks. (Consider, for example, the UTL_FILE and DBMS_OUTPUT built-in packages in which crucial functionality is badly or partially implemented.) You don’t have to accept these weaknesses; instead, you can build your own package on top of Oracle’s to correct as many of the problems as possible. 

For example, the do.pkg script I described in OPP6 Chapter 17 offers a substitute for the DBMS_OUTPUT.PUT_LINE built-in that adds an overloading for the XMLType datatype. Sure, you can get some of the same effect with standalone procedures or functions, but overloading and other package features make this approach vastly preferable.

Minimize the need to recompile code

As you will read below, a package usually consists of two pieces of code: the specification and body. External programs (not defined in the package) can only call programs listed in the specification. If you change and recompile the package body, those external programs are not invalidated. Minimizing the need to recompile code is a critical factor in administering large bodies of application logic.

And I finish up with a few recommendations for writing PL/SQL code:

Avoid writing schema-level procedures and functions

Always start with a package. Even if there is just one program in the package at the moment, it is very likely that there will be more in the future. So "put in the dot at the start," and you won't have to add it later.

Use packages to group together related functionality

A package gives a name to a set of program elements: procedures, functions, user-defined types, variable and constant declarations, cursors, and so on. By creating a package for each distinct area of functionality, you create intuitive containers for that functionality. Programs will be easier to find, and therefore less likely to be reinvented in different places in your application.

Keep your packages small and narrowly focused

It doesn't do much good to have just three packages, each of which has hundreds of programs. It will still be hard to find anything inside that bunch of code. Instead, keep your packages small and focused: all subprograms in a package should be "related" in some fashion that is reflected by the package name, and that are commonly used together or in close proximity.

And if you do have a monster package that you need to break up into more manageable pieces, be sure to check out the new-to-12.1 ACCESSIBLE_BY feature.