Wednesday, August 24, 2016

PL/SQL at Oracle Open World 2016

If you work with PL/SQL, you will not want to miss any of these sessions:

Sunday, Sep 18, 8:00 a.m. | Moscone West—2010
The Best Oracle Database 12c New Features for Developers and DBAs [UGF2028]

Alex Zaballa,  Senior Oracle Database Administrator,  Accenture Enkitec Group

This presentation looks at which new features of Oracle Database 12c are the most interesting to DBAs and developers. If you want to get up to speed with the most current release of Oracle Database, this session is for you. The session includes examples to show the functionality of the new features: • Multitenant • In-memory • SQL query row limits and offsets • Invisible columns • Approximate count distinct • PL/SQL from SQL • Session-level sequences • Session private statistics for GTT • Temporary undo • Multiple indexes on the same set of columns • SQL*Loader Express • Limit the PGA • Statistics during loads • Partial indexes for partitioned tables • Full database caching • Oracle Recovery Manager table recovery • Identity columns • In-database archiving

Sunday, Sep 18, 10:30 a.m. | Moscone West—3024
The Hidden Face of the Cost Based Optimizer: PL/SQL-Specific Statistics [UGF2781]

Michael Rosenblum,  Software Architect/Senior DBA,  Dulcian Inc

Database statistics are not limited to tables, columns, and indexes. PL/SQL functions also have a number of associated statistics, namely costs (CPU, I/O, network), selectivity, and cardinality (for functions that return collections). These statistics have default values that only somewhat represent reality. However, these values are always used by Oracle's cost-based optimizer to build execution plans. This session uses real-life examples to illustrate how properly managed PL/SQL statistics can significantly improve executions plans. It also demonstrates that Oracle's extensible optimizer is flexible enough to support packaged functions.

Sunday, Sep 18, 1:00 p.m. | Moscone South—302
A Holistic Approach to Database Security [UGF2210]

Robert Lockard,  President,  oraclewizard

This session features a discussion on defining the attack surface and what attack vectors are relevant to each node on the attack surface. Learn the concept of exploiting different nodes on the system, how attack vectors change for nodes, and mitigation changes for nodes. Discover the trusted path concept and how to apply mitigation to points along the trusted path to protect data. This session presents four things the DBA and developer can do now to secure the database environment. It also takes a piece of PL/SQL that is subject to SQL injection and moves through securing the code using Oracle Database 12cfeatures and separating the data from the application code.

Sunday, Sep 18, 3:30 p.m. | Moscone South—306
The Five Ways of Building Oracle Applications [UGF4313]

Sten Vesterli,  Principal,  More Than Code

This session examines all the most important development tools and technologies available from Oracle today, discussing the strong and weak points of Oracle Forms (the classical Oracle development tool, PL/SQL logic), Oracle Application Development Framework (declarative development with Oracle JDeveloper, Java business logic), Oracle Application Express (declarative development in web browser, PL/SQL business logic), JavaScript Extension Framework (brand-new JavaScript-based framework for building the user interface layer), and Oracle Mobile Application Framework (specialized Oracle Application Development Framework-like tool for mobile applications, generating native iOS, Android, and Windows applications). The presentation also includes recommendations for how to choose the right tool.

Sunday, Sep 18, 3:30 p.m. | Moscone South—310
Oracle Database 12c PL/SQL Basics and Tuning for DBAs [UGF1841]

Marcelle Kratochvil,  CIO / CTO,  Piction

It is tempting for DBAs to confine their knowledge set to database management and tuning while maintaining a belief that development of applications should be left only to developers. But this is a dangerous view; a DBA unskilled in PL/SQL is an ineffective one. This lack of skill helps foster a divisive culture likely resulting in poor development practices and security holes. This session is designed for the skilled DBA who has minimal knowledge on PL/SQL. It covers the basics of how PL/SQL is used, how to review code for security issues, and methods for efficiently tuning it.

Monday, Sep 19, 4:15 p.m. | Park Central—Metropolitan II
Querying Oracle Table from Hadoop/Hive and Querying Hadoop/HDFS from PL/SQL [CON6359]

Kuassi Mensah,  Director, Product Management,  Oracle
Nicholas Van Wyen,  Programmer / Developer,  Metasystems Technologies Inc.
This session explores the integration and offloading possibilities of an existing Oracle and Hadoop infrastructure, which are accessible today. It begins by describing Oracle Table Access for Hadoop, which turns Oracle Database tables into Hadoop data sources, allowing big data (facts) to be combined with master data (i.e., products, customers, in Oracle Database) using Hive SQL or Spark-SQL. Moving large data sets to Hadoop complicates applications, SQL queries, and PL/SQL. The session then explains how to move Terabytes of cold data over to Hadoop/Hive but continue to access them as native Oracle RDBMS objects that can be queried through your current applications without code changes.

Monday, Sep 19, 5:30 p.m. | Moscone South—302
What's New in PL/SQL and EBR in Oracle Database 12c Release 2 [CON6145]

Bryn Llewellyn,  Distinguished Product Manager, Database Division,  Oracle

Oracle Database 12c Release 2 brings important improvements to the PL/SQL language, the tools that support it, and edition-based redefinition. In this session learn how a PL/SQL static expression can be used where before a literal value was required, how built-in functions support the conversion of a PL/SQL variable to its JavaScript Object Notation representation and vice versa, and how a brand new code coverage tool helps developers prove that their tests are thorough. Also covered is how PL/Scope reports the identifiers used in static SQL and whether it selects, inserts, updates, or deletes; how one session can request that another connects to the GUI debugger; and how ad hoc SQL is allowed when halted at a breakpoint. Attendees also learn how the ease of use of EBR is improved.

Tuesday, Sep 20, 6:15 p.m. - 7:00 p.m. | Moscone South—307
Ask Tom Live with Chris Saxon and Connor McDonald [MTE7199]

Connor Mcdonald, Developer Advocate for SQL, Oracle
Chris Saxon, Developer Advocate for SQL, Oracle

When it comes to Oracle Database, the most popular and highly regarded source of advice is Ask Tom (asktom.oracle.com). For years, the legendary Tom Kyte answered any and all questions, and hosted vigorous discussions on all aspects of development on Oracle Database. These days, Chris Saxon and Connor McDonald of the Oracle Database Developer Advocates team carry on the tradition. So if you've got questions or want to hear what's it like to be on the AskTom team, or just hear about some of the strange and funny things we've been asked, don't miss this session.

Wednesday, Sep 21, 1:30 p.m. | Moscone South—303
Optimizing PL/SQL for Performance and Maintainability [CON6556]

Steven Feuerstein,  Oracle Database Developer Advocate for PL/SQL, Oracle,  Oracle
Bryn Llewellyn,  Distinguished Product Manager, Database Division,  Oracle
Connor Mcdonald,  Developer Advocate for SQL,  Oracle
Michael Rosenblum,  Software Architect/Senior DBA,  Dulcian Inc
Chris Saxon,  Developer Advocate for SQL,  Oracle

After fully leveraging SQL in your Oracle Database applications, it’s time to completely utilize PL/SQL to implement business rules, create APIs on top of your data structures, and implement key processes within the database. Usually when developers think about optimization, they focus on performance, and this session does, too. But it’s also critical to think about how to write code today, so that tomorrow and years from now, future developers will be able to adapt that code to changing user requirements and database features. Join the panel for an hour of thought-provoking discussion, plus lots of Q&A.

Thursday, Sep 22, 10:45 a.m. | Moscone South—103
Thinking Clearly About Database Application Architecture [CON6491]

Gerald Venzl,  Principal Product Manager,  Oracle
Bryn Llewellyn,  Distinguished Product Manager, Database Division,  Oracle
Connor Mcdonald,  Developer Advocate for SQL,  Oracle
Toon Koppelaars,  Consulting Member of Technical Staff, Real World Performance Group,  Oracle
Cary Millsap,  Infrastructure Principal Director,  Accenture Enkitec Group

The panelists strive to deserve their “Database Scientist” informal job titles by drawing their understanding of the correctness, security, and performance properties of applications that use Oracle Database from the data that proactive, empirical investigation yields. Like all scientists, they appreciate that future experiments might prompt the modification of current understanding. With this ordinary caveat, they confidently promote the understanding born of the current corpus of evidence: that the optimal database architecture starts with a carefully designed data model, manipulated by humanly composed SQL, encapsulated in PL/SQL. Come to hear them present their evidence. Challenge them with your own. They love a feisty debate.

Thursday, Sep 22, 9:30 a.m. | Park Central—Concordia
Database-Centric APIs on the Cloud Using PL/SQL and Node.js [CON3153]

Lucas Jellema,  Unspecified,  AMIS Services BV
Paco van der Linden,  Principal Consultant,  AMIS

APIs are crucial in today's IT landscape. Enabling REST services to read and manipulate data in formats easily accessible by a wide range of consumers is a key objective. This session shows how Oracle Database can play a key role in offering such APIs. Using a combination of PL/SQL packages (using rich SQL, JavaScript Object Notation, and user-defined types) and a Node.js application with the Node Oracle Database driver, it's quite straightforward. This combination runs on-premises and just as easily on the Oracle's database-as-a-service and platform-as-a-service solutions. Demos illustrate the development and deployment on the cloud of such APIs. Expect some advanced SQL and PL/SQL and a quick introduction into Node.js.

Thursday, Sep 22, 12:00 p.m. | Marriott Marquis—Salon 10/11
PL/SQL Programming for .NET Developers: Tips, Tricks, and Debugging [CON6612]

Christian Shay,  Product Manager - .NET and Windows Technologies,  Oracle

.NET and Oracle programmers frequently work with PL/SQL, whether that means setting up a call to a stored procedure from a .NET program, executing a PL/SQL anonymous block, or writing and debugging PL/SQL stored procedure code. This session looks at leveraging PL/SQL from the point of view of a .NET developer and provides in-depth tips about how to configure and use the tightly integrated PL/SQL debugger in Visual Studio.







PL/SQL Optimization Levels and Native Code Generation

Charles Wetherell, Consulting Member of the PL/SQL development team, was kind enough to offer these insights regarding PL/SQL optimization and native code generation.

A PL/SQL programmer asked why PL/SQL native code generation was turned off when the PL/SQL optimization level was set to 1.

There are four PL/SQL optimization levels:

 0. Esoteric for some long-since-passed compatibility issues with release 9 and before
 1. Basic code generation with debugging data created
 2. Global optimization
 3. Automatic inlining of local procedures

Each level builds on the level before. Debugging data is not created above level 1.

Generally, native code generation is independent of optimization level. Native code generation is turned off at levels 1 (and 0) because it interferes with debugging. In other words, PL/SQL code compiled at optimization levels 0 and 1 is always interpreted when executed.

You should never use level 0. That is a blanket prescription. Certainly no new code should ever require it.

Level 1 basic code generation applies many simple optimizations. Level 2 code generation adds global analysis that considers the possible flow of control through each subprogram when optimizing. This extra analysis markedly improves the quality of the code generated. The default optimization level is 2 and you should not generally use level 1 unless you specifically want to debug or do something else that requires level 1. Optimization level 2 is likely to speed up PL/SQL code by a factor of 2 to 3. But see the comment on SQL below.

Probably, code will run noticeably faster if you always use level 3; the inlining is controlled in a way that almost always generates a performance improvement. I know of no realistic cases where code ran significantly slower or had other problems because of the use of level 3.

Native compilation will almost always improve code performance significantly as well. This means that the most interesting combinations are:

Native and opt = 2
Native and opt = 3

So far as I know, there are no significant cases where higher optimization levels cause the PL/SQL compiler to slow down noticeably or to consume too many resources during compilation. In other words, compilation expense should not be a factor in deciding what optimization level to use.

Most PL/SQL applications do NOT spend most of the their execution time in PL/SQL. Much of their time is spent doing the SQL triggered by embedded SQL statements in the PL/SQL program. A likely time split is something like 75% of execution time in SQL and 25% in PL/SQL. The PL/SQL compiler settings have essentially no effect on the performance of SQL. Of course, your particular application might have a substantially different time split, but spending more than 50% of application time in PL/SQL would be regarded as unusual.

The documentation describes the PL/SQL compilation controls.

I wrote a blog post about inlining.

When the optimizing PL/SQL code generator was first introduced, I wrote a paper about the definition of PL/SQL and the optimizations that are allowed in PL/SQL programs. You may find that your understanding of PL/SQL is deepened and your programming becomes more sophisticated after you read this.

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!