In the last issue of Oracle Magazine, I offered advice on how best to write PL/SQL code for Oracle Application Express applications. In this article, I take a look at how, when, and where to write SQL statements for those same applications.Different Language, Same Challenges
I offered three guidelines in the last article that are worth repeating here (and then exploring in the context of SQL, specifically):
This article zeroes in on that first guideline but that guideline is also closely related to, and implemented through, the other two guidelines.
I will again turn to the PL/SQL Challenge application for examples demonstrating both compliance with and violations of the above guidelines.Move All DML to PL/SQL Procedures
Let’s begin by taking a look at how to implement data manipulation language (DML) statements (specifically INSERT, UPDATE, and DELETE) you need to run in response to user input in your application.
It’s certainly easy enough to write an INSERT or UPDATE statement in an Oracle Application Express application. Because PL/SQL natively supports compilation and execution of SQL statements, you simply create a PL/SQL process and then type the DML statement into the appropriate field.
There are very few applications that do not change the contents of database tables in response to user actions, so your application will likely require the execution of many DML statements. I recommend that you never write those statements directly inside the Application Builder. Instead, you should
One of the most compelling reasons to move your DML to PL/SQL packages is this basic reality of programming: things (and, in particular, transactions) get more complicated over time. What today might be a single insert into table T1 gradually morphs into
And when you think you need to use something in only one place, you then discover you need to use it in a second and third location in your application as well.
So if you write that INSERT INTO T1 in multiple places, you’ll find yourself copying and pasting the expanded transaction into each location when that simple transaction expands to a three-step transaction. The end result is an application that is extremely difficult to debug, manage, optimize, and enhance.
I ran into precisely this issue with the PL/SQL Challenge website, which is built on Oracle Application Express and run by my son, Eli, and me. The main activity on this website is offering quizzes that test knowledge of various Oracle technologies. Users find out how well they did by visiting the Quiz Details page after the quiz has closed and reviewing their performance.
I keep track of each quiz viewing by a player in the qdb_quiz_visits table. So my first iteration of the Oracle Application Express page process on the Quiz Details page simply executed this statement:
INSERT INTO qdb_quiz_visits (user_id, quiz_id) VALUES (:p659_user_id, :p659_quiz_id);
But after implementing this process, I realized that I also wanted to record a viewing of a quiz whenever a player checks out the survey results for that quiz. So off I went to another page and with a quick copy/paste (along with careful review and editing to replace page numbers), a new page process was created:
INSERT INTO qdb_quiz_visits (user_id, quiz_id) VALUES (:p740_user_id, :p740_quiz_id);
Nothing is different except the page number. That’s certainly simple and obvious enough. So why should I bother creating a procedure in a package and putting the INSERT there? If the processing for recording a visit never changed, I suppose there wouldn’t be any reason to do that.
But, as was going to be the case for just about every one of my requirements, I did need to change this processing when I added points to the PL/SQL Challenge website. As encouragement to study, players get five points every time they view a quiz.
The code for assigning points is not much more complicated than the INSERT into the qdb_quiz_visits table:
INSERT INTO qdb_points (user_id, activity_date, activity_id, activity_key_value, points) VALUES ( :p659_user_id, SYSDATE, 'QUIZVIEW', :p659_quiz_id, 5);
And therein lies the trap, the awful temptation. Having already duplicated the INSERT statement, should I now continue down that path and add this new INSERT statement to both places?
Fortunately, the solution—the way to avoid chaos—is straightforward: hide all the transaction logic behind a single procedure, change all item references to parameters, and then call that procedure wherever it is needed. Here’s the record_quiz_view procedure that hides the inserts for visits and points:
PACKAGE BODY qdb_quiz_mgr IS PROCEDURE record_quiz_view ( user_id_in IN PLS_INTEGER, quiz_id_in IN PLS_INTEGER) IS BEGIN INSERT INTO qdb_quiz_visits (user_id, quiz_id) VALUES (user_id_in, quiz_id_in); INSERT INTO qdb_points (user_id, activity_date, activity_id, activity_key_value, points) VALUES (user_id_in, SYSDATE, 'QUIZVIEW', quiz_id_in, 5); END; END;
And then my process code is slimmed down to nothing more than
BEGIN qdb_quiz_mgr.record_quiz_view ( user_id_in => :p659_user_id, quiz_id_in => :p659_quiz_id); END;
Taking the little bit of extra time needed to isolate your DML statements into procedures is like putting money into a retirement plan. You might feel a little pinch right now (a little bit less money, a little bit less time), but in the future, you will have more money and time, because you will be able to modify and enhance your code much more easily.
But don’t worry! You won’t have to wait till you are 65 years of age to feel the benefits of “DML hiding.” That happens almost immediately, because applications change so rapidly and because application developers often don’t get it completely right the first time.
Consider the PL/SQL Challenge record_quiz_view procedure. I soon realized that if players can get five points for every viewing of the quiz details, some players might click that quiz link over and over again, artificially inflating their point total. So I needed to go back to that procedure and give points only for the first viewing on a given day.
I am sure you can all think of similar examples from your own applications. Everything gets more complicated over time; you rarely think of everything up front; you will be going back and changing that code. And when that code contains transactions that can be initiated by a user, you really need to get it right.
I’ve demonstrated the hiding of INSERT statements. With inserts, you are always creating new rows. When executing updates and deletes, however, you modify existing rows, which raises the possibility that an update can be lost. Suppose that Users A and B query the same row of data on a page of the website. Then suppose they both make changes. User A presses the Submit button, and the changes are saved. Then User B presses Submit, and those changes are made—overwriting the changes saved by User A.
If, on a given page, you are not able to take advantage of Automatic Row Processing and instead are writing your own updates and deletes, be sure to take the following steps:
You can access Create Package with Methods on Tables via SQL Workshop -> Utilities -> Methods on Tables, as shown in Figure 1. You can then utilize these subprograms to both hide the DML statements and ensure that user changes are not lost.
Figure 1: Create Package with Methods on Tables locationHide Complex Queries in Views
So I’ve addressed inserts, updates, and deletes. What about that other DML statement SELECT? I have two recommendations for you:
Suppose a PL/SQL Challenge process needed the date on which a player answered a quiz. I could write this block inside the Application Builder:
DECLARE l_date DATE; BEGIN SELECT taken_on INTO l_date FROM qdb_quiz_answers WHERE user_id = :p659_user_id AND quiz_id = :p659_quiz_id; IF l_date < SYSDATE - 2 THEN .... END IF; END;
Instead, I should move the query to a function and reduce the process code to
CREATE OR REPLACE PACKAGE BODY qdb_quiz_mgr IS FUNCTION quiz_taken_on ( user_id_in IN PLS_INTEGER, quiz_id_in IN PLS_INTEGER) RETURN DATE IS l_date DATE; BEGIN SELECT taken_on INTO l_date FROM qdb_quiz_answers WHERE user_id = user_id_in AND quiz_id = quiz_id_in; RETURN l_date; END; END; / DECLARE l_date DATE; BEGIN l_date := qdb_quiz_mgr.quiz_taken_on ( :p659_user_id, :p659_quiz_id); IF l_date < SYSDATE - 2 THEN ... END IF; END; /
And now when I need to fetch multiple rows, I’ll use BULK COLLECT and return an array, as in
CREATE OR REPLACE PACKAGE qdb_quiz_mgr IS TYPE answers_t IS TABLE OF qdb_quiz_answers%ROWTYPE; FUNCTION quizzes_taken_by ( user_id_in IN PLS_INTEGER) RETURN answers_t; END; / CREATE OR REPLACE PACKAGE BODY qdb_quiz_mgr IS FUNCTION quizzes_taken_by ( user_id_in IN PLS_INTEGER) RETURN answers_t IS l_answers answers_t; BEGIN SELECT * BULK COLLECT INTO l_answers FROM qdb_quiz_answers WHERE user_id = user_id_in ORDER BY taken_on; RETURN l_answers; END; END; /
In a properly normalized relational table design, you will end up with lots of tables and need to do the same joins over and over again. On the PL/SQL Challenge website, for example, each quiz has a topic (the Oracle feature to which it is related) and a quiz format (multiple choice, true/false, and so on). Consequently, I find myself writing queries like this a lot:
SELECT qz.question_text the_question, t.title feature, qf.text quiz_format FROM qdb_quizzes qz, qdb_topics t, qdb_quiz_formats qf WHERE qz.topic_id = t.topic_id AND qz.quiz_format_id = qf.quiz_format_id;
My fingers quickly get tired of typing that WHERE clause, and my inner voice never stops badgering me: “Why are you writing this again? Don’t you have something better to do?”
Yes, I do. So instead of writing this three-way join more than once, I create a view:
CREATE OR REPLACE VIEW qdb_quizzes_v AS SELECT qz.question_text the_question, t.title feature, qf.text quiz_format FROM qdb_quizzes qz, qdb_topics t, qdb_quiz_formats qf WHERE qz.topic_id = t.topic_id AND qz.quiz_format_id = qf.quiz_format_id;
Now the query for my report is nothing more than
SELECT * FROM qdb_quizzes_v
And if I discover a week from now that my report needs another column from one of those tables, I can add it to the view, and—voilà!—it will be available in the report.
But watch out! If you create lots of views that contain joins and then join those views to each other, you may end up doing a bunch of unnecessary work. Consider my topics table, used in the qdb_quizzes_v view. A topic is always in a domain (a technology area, such as SQL or PL/SQL). In addition, PL/SQL Challenge offers a set of resources related to a topic, including a link to the Oracle documentation.
So I create this handy view:
CREATE OR REPLACE VIEW qdb_topic_details_v AS SELECT d.domain_name technology, t.title feature, r.title doc_title, r.url doc_link FROM qdb_topics t, qdb_resources r, qdb_domains d WHERE t.topic_id = r.topic_id AND r.resource_type = 'FEATURE_DOC' AND t.domain_id = d.domain_id;
And I then use it in my Topic Details report, which is perfectly appropriate. But the next day, I need to combine quiz information with the documentation URL and the domain name. What could be easier than joining those two views?
SELECT t.technology, q.question_text, r.doc_link FROM qdb_quizzes_v q, qdb_topic_details_v t WHERE q.topic_id = t.topic_id;
Yet by doing this, I pay the price of a join with the qdb_topics table twice. Will this cause performance problems? In this simple example, probably not. But as your queries—and views—grow increasingly complex (it is not uncommon to have 10-way joins in views, for example), performance may well degrade. In addition, future generations of programmers will wonder why you didn’t just write exactly the query you needed. “Just lazy” is usually not an acceptable answer.
To conclude, keep your report queries as simple as possible through the use of views, but pay attention and maintain discipline when using those views. Don’t ask the SQL engine to do lots of extra work just so you can avoid writing a query that does what is needed and nothing more.Avoid Redundant Reports with Table Functions
The ability to create interactive reports is among the best aspects of Oracle Application Express. It not only makes it easier to build powerful reports but also gives users a tremendous amount of control over the content and appearance of those reports. I use them all over the PL/SQL Challenge website and, in particular, have found them to be critical for rankings reports.
Players want to see rankings for a variety of criteria, such as
The need for ranking across different periods introduces the greatest complexity to these reports, because I populate and rely on a different materialized view for each period. To obtain quarterly rankings, for example, I must query from the mv_qdb_rankings_q view.
Different views initially gave me a headache, because an interactive report cannot be based on a dynamic SQL statement. As a result, I built almost two dozen different reports, as shown in Figure 2.
Figure 2: The 21 initial PL/SQL Challenge interactive reports
I didn’t feel particularly clever or productive while I built these reports, but at least I figured that I would have to build them just once and then not mess with them anymore.
That fantasy lasted for a couple of months. By that time, I’d accumulated a solid half-dozen high-priority enhancement requests from players. I then faced the challenge of a very tedious and error-prone job of going through 21 interactive reports and making all the necessary changes.
Surely there is a better way (besides Oracle’s enhancing Oracle Application Express to support dynamic SQL in interactive reports). And there is. You can use a table function, a function that can be called inside the FROM clause of a query, wrapped inside the TABLE operator. The function returns a collection of data, and the TABLE operator converts that collection into rows and columns, which can then be consumed by the report (and anything else that executes a query using that table function).
It is outside the scope of this article to provide a full explanation of table functions; I encourage you to explore them through the link provided at the end of the article, because they are incredibly useful. Here I walk through only the basic steps I took to use table functions to reduce the number of interactive reports for ranking to just two.
Taking a top-down approach, I start with the query I want to be able to execute in the report. If I want to see company rankings for the daily PL/SQL quiz in the fourth quarter of 2013, the query will be nothing more than
SELECT * FROM TABLE ( qdb_ranking_mgr.ir_rankings_tf ( category_in => 'COMPANY', period_type_in => 'QUARTERLY', competition_in => 'DAILY PL/SQL QUIZ', period_in => '2013-4'));
I hard-coded the values passed to the function; in the application, these values are selected by the player.
I hope you agree that that is a very simple query—but only because I’ve hidden all the details away in the function.
So now let’s go under the covers of this table function. A table function must return a collection, so I must define a collection type. Because I am returning multiple pieces of information (company name, ranking, percentage correct, and so on), I must create a collection of object types:
CREATE OR REPLACE TYPE ir_ranking_ot IS OBJECT ( period VARCHAR2 (500), type_name VARCHAR2 (300), score INTEGER, pct_correct_answers NUMBER, answer_time VARCHAR2 (500), overall_rank INTEGER ); / CREATE OR REPLACE TYPE ir_rankings_nt IS TABLE OF ir_ranking_ot /
The header of my function now becomes
PACKAGE qdb_ranking_mgr IS FUNCTION ir_rankings_tf ( category_in IN VARCHAR2, period_type_in IN VARCHAR2, competition_in IN INTEGER, period_in IN VARCHAR2) RETURN ir_rankings_nt;
The implementation of a table function is very application-dependent. My ir_ rankings_tf function contains 230 lines of code that construct a dynamic query based on the parameter values, the most critical of which is the period type, because that determines which materialized view is needed. Here is a highly simplified version of this function, demonstrating the declaration of a local collection, the population of that collection using EXECUTE IMMEDIATE - BULK COLLECT for a very dynamic query, and the return of that collection from the function:
l_report_data ir_rankings_nt; BEGIN EXECUTE IMMEDIATE 'SELECT ir_ranking_ot(' || c_select_list || ') FROM mv_qdb_rankings_' || period_type_in || ' mv,' || c_detail_tables || ' WHERE ' || c_where_clause || ' GROUP BY ' || c_period_qualifier_column BULK COLLECT INTO l_report_data; RETURN l_report_data; END;
Table functions can be quite complicated, but their complexity is hidden behind the function interface. Developers who need the data returned by a table function can write a simple query that gets them the data without making them sort through the details.
The main benefit I have found for table functions inside Oracle Application Express, though, is to dramatically reduce the number of interactive reports I have to maintain.Control Your SQL, Control Your App
SQL statements are the most important parts of any Oracle Database–based application. They are also the parts of your applications that cause the most performance problems and change the most frequently. It is therefore extremely important to establish and follow guidelines about how, when, and where to write SQL statements.
I recommend that you follow these guidelines for SQL in Oracle Application Express:
Follow these guidelines, and you will find yourself spending less time maintaining your applications. You will, instead, have more time to think about, design, and implement exciting new features for your users.
Each PL/SQL article offers a quiz to test your knowledge of the information it provides. The quiz appears below and also at PL/SQL Challenge (plsqlchallenge.com), a website that offers online quizzes on the PL/SQL language as well as SQL, Oracle Application Express, database design, and deductive logic.
I execute these statements:
CREATE TABLE plch_employees ( employee_id INTEGER, last_name VARCHAR2 (100) ) / CREATE TABLE plch_departments ( department_id INTEGER, department_name VARCHAR2 (100) ) / BEGIN INSERT INTO plch_employees VALUES (100, 'Shubin'); INSERT INTO plch_employees VALUES (200, 'Gould'); INSERT INTO plch_employees VALUES (300, 'Dawkins'); INSERT INTO plch_departments VALUES (10, 'Analysis'); INSERT INTO plch_departments VALUES (20, 'Discovery'); COMMIT; END; /
Which of the following choices contain(s) code I can place in the query field of an Oracle Application Express interactive report so that I can display either employee or department information from that report?
BEGIN RETURN 'SELECT ' || CASE :p1000_report_type WHEN 'D' THEN 'employee_id, last_name FROM plch_employees' WHEN 'E' THEN 'department_id, department_name FROM plch_departments' END; END;
First create these objects in the database:
CREATE OR REPLACE TYPE plch_report_data_ot IS OBJECT ( report_id INTEGER, report_text VARCHAR2 (100) ) / CREATE OR REPLACE TYPE plch_report_data_nt IS TABLE OF plch_report_data_ot / CREATE OR REPLACE PACKAGE plch_pkg IS FUNCTION id_and_name ( type_in IN VARCHAR2) RETURN plch_report_data_nt; END; / CREATE OR REPLACE PACKAGE BODY plch_pkg IS FUNCTION id_and_name ( type_in IN VARCHAR2) RETURN plch_report_data_nt IS l_return plch_report_data_nt; BEGIN CASE type_in WHEN 'E' THEN SELECT plch_report_data_ot ( employee_id, last_name) BULK COLLECT INTO l_return FROM plch_employees; WHEN 'D' THEN SELECT plch_report_data_ot ( department_id, department_name) BULK COLLECT INTO l_return FROM plch_departments; END CASE; RETURN l_return; END; END; /
Then use this query in the report:
SELECT * FROM TABLE ( plch_pkg.id_and_name ( :p1000_report_type)) /
SELECT employee_id report_id, last_name report_text FROM plch_employees WHERE :p1000_report_type = 'E' UNION SELECT department_id, department_name FROM plch_departments WHERE :p1000_report_type = 'D';
TEST your PL/SQL knowledge
READ more Feuerstein
READ more about table functions
Oracle Database PL/SQL Language Reference 12c Release 1 (12.1)
Photography by Ricardo Gomez, Unsplash