Friday, June 16, 2017

So you want to write a technical book?


I received this question today:
If I wanted to write a tech book, where/how would I start?
Rather than provide an individual answer, I thought I'd answer on my blog. Here goes.

First, how I answer this question for myself (the variation being: "Do you want to write another book?"):

No, don't do it.

:-)

I decided a few years ago that I would not write new books and instead keep my core set of books on PL/SQL up to date (for anyone who's wondering, that means essentially 3 out my 10 books on PL/SQL).

It takes a lot of time to write a book, any sort of book. And certainly with a technical book you need to be concerned about technical accuracy (slightly less critical with fiction :-) ).

In addition, people aren't buying books like they used to. Gee, thanks, Google (and people publishing ripped-off e-copies of books, and all the free content published on blogs and...).

So you definitely should not go into such a project thinking you are going to make much, if any, money on the book.

Some reasons to go ahead with such a project anyway:

  • You always wanted to publish a book, see your name listed as an author. 
  • You want to build your reputation in a given technology.
  • Along with (or through) that, you want to increase the revenue you can generate around that technology (speaking fees, hourly consulting rates).
Assuming you have decided to take the plunge, you need to:
  • Decide on a topic
  • Do lots of writing.
  • Find a publisher.
Mostly in the order. But I suggest that you do not write a whole book and then look for a publisher. That is likely necessary if you are writing a work of fiction. But with a technical book, it's a bit different.

Here's my suggestion, after you decide on a topic:

1. Come up with a table of contents for your book.

2. Start blogging about your topic. You don't even have to create your own blog. Publish on LinkedIn or Medium or any number of other channels.

Pick a chapter (maybe start at the beginning, maybe not) and do some writing. Publish it. See how people respond - to your writing, to the topic, etc.

If you get a strong response, then it is time to approach publishers. This where getting a technical book published can be so much easier than a work of fiction. 

You can offer your TOC, some samples of writing, and overall summary of a book, and from that alone, secure a contract with a publisher. 

I have a long, happy history with O'Reilly Media. But there are lots of technical publishers out there. And certainly an editor I very much respect and encourage you to seek out is Jonathan Gennick. I am sure he'd be happy to talk to you, and give you even more and better advice.

Friday, June 9, 2017

PL/Scope 12.2: Find all commits and rollbacks in your code

Yes, another post on PL/Scope, that awesome code analysis feature of PL/SQL (first added in 11., and then given a major upgrade in 12.2 with the analysis of SQL statements in PL/SQL code)!

A question on StackOverflow included this comment:
But there can be scenarios where it is difficult to identify where the ROLLBACK statement are executed in a complex PL SQL program (if you have to do only a modification to the existing code).
As of 12.2, it is super-duper easy to find all commits and rollbacks in your code.

Find all commits:

SELECT st.object_name,
       st.object_type,
       st.line,
       src.text
  FROM all_statements st, all_source src
 WHERE     st.TYPE = 'COMMIT'
       AND st.object_name = src.name
       AND st.owner = src.owner
       AND st.line = src.line
ORDER BY st.object_name,
         st.object_type   
/

Find all rollbacks:

SELECT st.object_name,
       st.object_type,
       st.line,
       src.text
  FROM all_statements st, all_source src
 WHERE     st.TYPE = 'ROLLBACK'
       AND st.object_name = src.name
       AND st.owner = src.owner
       AND st.line = src.line
ORDER BY st.object_name,
         st.object_type   
/

Reminder: these data dictionary views are populated only when your session or program unit has these settings enabled:

ALTER SESSION SET plscope_settings='identifiers:all, statements:all'

Friday, June 2, 2017

More 12.2 PL/Scope Magic: Find SQL statements that call user-defined functions

When a SQL statement executes a user-defined function, your users pay the price of a context switch, which can be expensive, especially if the function is called in the WHERE clause. Even worse, if that function itself contains a SQL statement, you can run into data consistency issues.

Fortunately, you can use PL/Scope in Oracle Database 12c Release 2 to find all the SQL statements in your PL/SQL code that call a user-defined function, and then analyze from there.

I go through the steps below. You can run and download all the code on LiveSQL.

First, I turn on the gathering of PL/Scope data in my session:

ALTER SESSION SET plscope_settings='identifiers:all, statements:all'
/

Then I create a table, two functions and a procedure, so I can demonstrate this great application of PL/Scope:

CREATE TABLE my_data (n NUMBER)
/

CREATE OR REPLACE FUNCTION my_function1
   RETURN NUMBER
   AUTHID DEFINER
IS
BEGIN
   RETURN 1;
END;
/

CREATE OR REPLACE FUNCTION my_function2
   RETURN NUMBER
   AUTHID DEFINER
IS
BEGIN
   RETURN 1;
END;
/

CREATE OR REPLACE PROCEDURE my_procedure (n_in IN NUMBER)
   AUTHID DEFINER
IS
   l_my_data   my_data%ROWTYPE;
BEGIN
   SELECT my_function1 ()
     INTO l_my_data
     FROM my_data
    WHERE     n = n_in
          AND my_function2 () = 0
          AND n = (SELECT my_function1 () FROM DUAL);

   SELECT COUNT (*)
     INTO l_my_data
     FROM my_data
    WHERE n = n_in;

   UPDATE my_data
      SET n = my_function2 ()
    WHERE n = n_in;
END;
/

Note that only two of the three DML statements in MY_PROCEDURE contain a function call (the first query and the update).

Now I UNION ALL rows from ALL_STATEMENTS and ALL_IDENTIFIERS to get a full picture:

WITH one_obj_name AS (SELECT 'MY_PROCEDURE' object_name FROM DUAL)
    SELECT plscope_type,
           usage_id,
           usage_context_id,
           LPAD (' ', 2 * (LEVEL - 1)) || usage || ' ' || name usages
      FROM (SELECT 'ID' plscope_type,
                   ai.object_name,
                   ai.usage usage,
                   ai.usage_id,
                   ai.usage_context_id,
                   ai.TYPE || ' ' || ai.name name
              FROM all_identifiers ai, one_obj_name
             WHERE ai.object_name = one_obj_name.object_name
            UNION ALL
            SELECT 'ST',
                   st.object_name,
                   st.TYPE,
                   st.usage_id,
                   st.usage_context_id,
                   'STATEMENT'
              FROM all_statements st, one_obj_name
             WHERE st.object_name = one_obj_name.object_name)
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id
/

And I see these results:

PLSCOPE_TYPE    USAGE_ID    USAGE_CONTEXT_ID    USAGES
ID    1    0    DECLARATION PROCEDURE MY_PROCEDURE
ID    2    1      DEFINITION PROCEDURE MY_PROCEDURE
ID    3    2        DECLARATION FORMAL IN N_IN
ID    4    3          REFERENCE NUMBER DATATYPE NUMBER
ID    5    2        DECLARATION VARIABLE L_MY_DATA
ID    6    5          REFERENCE TABLE MY_DATA
ST    7    2        SELECT STATEMENT
ID    8    7          REFERENCE TABLE MY_DATA
ID    9    7          REFERENCE COLUMN N
ID    10    7          REFERENCE FORMAL IN N_IN
ID    11    7          REFERENCE COLUMN N
ID    13    7          CALL FUNCTION MY_FUNCTION1
ID    14    7          CALL FUNCTION MY_FUNCTION2
ID    15    7          ASSIGNMENT VARIABLE L_MY_DATA
ID    16    15            CALL FUNCTION MY_FUNCTION1
ST    17    2        SELECT STATEMENT
ID    18    17          REFERENCE TABLE MY_DATA
ID    19    17          REFERENCE FORMAL IN N_IN
ID    20    17          REFERENCE COLUMN N
ID    21    17          ASSIGNMENT VARIABLE L_MY_DATA
ST    22    2        UPDATE STATEMENT
ID    23    22          REFERENCE TABLE MY_DATA
ID    24    22          REFERENCE FORMAL IN N_IN
ID    25    22          REFERENCE COLUMN N
ID    26    22          REFERENCE COLUMN N
ID    27    22          CALL FUNCTION MY_FUNCTION2


OK. Now let's get to the substance of this blog post. I use subquery refactoring (WITH clause) to create and then use some data sets: my_prog_unit - specify the program unit of interest just once; full_set - the full set of statements and identifiers; dml_statements - the SQL DML statements in the program unit. Then I find all the DML statements whose full_set tree below it contain a call to a function.

WITH my_prog_unit AS (SELECT USER owner, 'MY_PROCEDURE' object_name FROM DUAL),
     full_set
     AS (SELECT ai.usage,
                ai.usage_id,
                ai.usage_context_id,
                ai.TYPE,
                ai.name
           FROM all_identifiers ai, my_prog_unit
          WHERE ai.object_name = my_prog_unit.object_name
            AND ai.owner = my_prog_unit.owner
         UNION ALL
         SELECT st.TYPE,
                st.usage_id,
                st.usage_context_id,
                'type',
                'name'
           FROM all_statements st, my_prog_unit
          WHERE st.object_name = my_prog_unit.object_name
            AND st.owner = my_prog_unit.owner),
     dml_statements
     AS (SELECT st.owner, st.object_name, st.line, st.usage_id, st.type
           FROM all_statements st, my_prog_unit
          WHERE     st.object_name = my_prog_unit.object_name
                AND st.owner = my_prog_unit.owner
                AND st.TYPE IN ('SELECT', 'UPDATE', 'DELETE'))
SELECT st.owner,
       st.object_name,
       st.line,
       st.TYPE,
       s.text
  FROM dml_statements st, all_source s
 WHERE     ('CALL', 'FUNCTION') IN (    SELECT fs.usage, fs.TYPE
                                          FROM full_set fs
                                    CONNECT BY PRIOR fs.usage_id =
                                                  fs.usage_context_id
                                    START WITH fs.usage_id = st.usage_id)
       AND st.line = s.line
       AND st.object_name = s.name
       AND st.owner = s.owner
/

And I see these results:

STEVEN    MY_PROCEDURE    6    SELECT       SELECT my_function1 ()
STEVEN    MY_PROCEDURE    18    UPDATE"   UPDATE my_data

Is that cool or what?

Tuesday, May 9, 2017

Use records to improve readability and flexibility of your code

Suppose I've created a table to keep track of hominids:

CREATE TABLE hominids
(
   hominid_name     VARCHAR2 (100),
   home_territory   VARCHAR2 (100),
   brain_size_cm    INTEGER
)
/

I might then write code like this:

DECLARE
   l_b_hominid_name    VARCHAR2 (100) := 'Bonobo';
   l_b_brain_size_cm   INTEGER := 500;
   l_g_hominid_name    VARCHAR2 (100) := 'Gorilla';
   l_g_brain_size_cm   INTEGER := 750;
   l_n_hominid_name    VARCHAR2 (100) := 'Neanderthal';
   l_n_brain_size_cm   INTEGER := 1800;

What do you think?

I find the little voice of Relational Theory inside my head rebelling.

"All that repetition! All that denormalization! All that typing (or copy-pasting, which is even worse)!"

Surely if I should avoid having redundant data in rows of my tables, I should avoid redundant code, too?

Yes, I should.  I don't like to see long lists of declarations, especially when the names are very similar and follow a pattern. 

A fine way to avoid this kind of code is to use record types to group related variables together within a named context: the record variable. So I could rewrite the declaration section above to:


DECLARE
   l_bonobo        hominids%ROWTYPE;
   l_gorilla       hominids%ROWTYPE;
   l_neanderthal   hominids%ROWTYPE;
BEGIN
   l_bonobo.hominid_name := 'Bonobo';
   l_bonobo.brain_size_cm := 500;
   l_gorilla.hominid_name := 'Gorilla';
   l_gorilla.brain_size_cm := 750;
   l_neanderthal.hominid_name := 'Neanderthal';
   l_neanderthal.brain_size_cm := 1800;

Notice that I now move the initializations of the variable (well, record.field) values to the executable section. That's because PL/SQL does not yet offer a built-in function (in object-oriented lingo, a constructor method) for record types.

So I no longer have six declarations - just three. And, of course, if my table had 15 columns and I had declared a separate variable for each of those, I would have been able to shrink down my declarations from 45 to 3!

Still, I don't like putting all that initialization code in the main body of my block. How about if I create my own "record constructor" function, and then call that:

CREATE OR REPLACE FUNCTION new_hominid (
   name_in IN hominids.hominid_name%TYPE,
   home_territory_in IN hominids.home_territory%TYPE,
   brain_size_cm_in IN hominids.brain_size_cm%TYPE)
   RETURN hominids%ROWTYPE
IS
   l_return hominids%ROWTYPE;
BEGIN
   l_return.hominid_name := name_in;
   l_return.home_territory := home_territory_in;
   l_return.brain_size_cm := brain_size_cm_in;
   RETURN l_return;
END;
/

DECLARE
   l_bonobo        hominids%ROWTYPE := new_hominid ('Bonobo', NULL, 500);
   l_gorilla       hominids%ROWTYPE := new_hominid ('Gorilla', NULL, 750);
   l_neanderthal   hominids%ROWTYPE := new_hominid ('Neanderthal', NULL, 1800);
BEGIN
   DBMS_OUTPUT.put_line (l_neanderthal.brain_size_cm);
END;
/

Ahhhhh. Just three declarations. Default values assigned in the declaration section. All the details of the assignments hidden away behind the function header.

And when I add a new column to the table (or generally a field to a record), I can add a parameter to my new_hominid function, along with a default value of NULL, and none of my existing code needs to change (unless that new column or field is needed).

Yes, I like that better.

How about you?

Wednesday, May 3, 2017

Getting my Oracle Database 12c Release 2 up and running on Mac via Docker

I love to follow in the footsteps of people who are braver, smarter and more knowledgeable than me.

So I was happy to wait till SQL Maria (Maria Colgan) published her blog post on Oracle Database 12c now available on Docker, with step-by-step instructions for taking advantage of the new Docker image for 12.2 now available (specifically, 12.2 via Docker on Github, 12.2 via Docker at the Docker Store).

I am happy to report that I can now connect SQL Developer to my containerized 12.2 database. Thank you, Maria, for a very helpful post!

Now, I am not going to repeat everything Maria already wrote. That would be silly. I will simply point out some things you might find helpful as you do the same thing I did (follow in Maria's footsteps - which, literally, meant lots of copy-pasting rather dumbly).

1. Watch out for those dashes when you copy/paste.

Docker was not responding as expected to my commands and I (well, actually, Gerald) eventually noticed that the dash, copied from the blog post, was too long - it had been translated into a different character. So watch out for that! You might need to retype the command yourself.

I hate that.

:-)

2. Create your own folder for your Oracle Database files. I know it should be obvious. But I am a copy-paste sorta guy, and probably the only one in the world who would copy this command into my terminal and expect it to work:

docker run --name oracle -p 1521:1521 -p 5500:5500 
-v /Users/mcolgan-mac/oradata:/opt/oracle/oradata 
oracle/database:12.2.0.1-ee

And it did - once I created my own folder for the files, and replaced that in the command.

Oh and by the way, that entire command (once you swap out mcolgan-mac for your own foler) needs to be one one line.

After that, everything went very smoothly and, again following Maria's wonderfully clear steps, I had my database up and running.

Then I set up my connection in SQL Developer:



and voila! My own 12.2 database running in a Docker container, on my Mac.

Thanks, Maria!
Thanks, Gerald!
Thanks, Docker!
Thanks, Oracle!



Monday, May 1, 2017

Deterministic functions, caching, and worries about consistent data

A developer contacted me with the following questions last week:

We have created a function that returns a single row column value form a query. When we call this function with the same input values it takes to long to return. Example:

select max (det_function('A2')) from dual connect by rownum <= 1000000

But when we change the function to a deterministic function the statement returns really fast. The only thing where we are unsure is what happens when the tables has changed to which the statement of the function selects? Do we need a to commit this table to bring oracle to re-execute the statement in the function and not use the cache or what should we do to get a consistent return value?

FUNCTION det_function (v_in_id VARCHAR2) RETURN NUMBER DETERMINISTIC
AS
   v_ident   NUMBER;
BEGIN
   SELECT VALUE INTO v_ident
     FROM my_table
    WHERE id = v_in_id;

   RETURN v_ident;
EXCEPTION
   WHEN VALUE_ERROR OR NO_DATA_FOUND THEN RETURN -1;
END;

A function is deterministic if the value returned by the function is determined entirely by its input(s).

The following function, for example, is deterministic:

FUNCTION betwnstr (
   string_in      IN   VARCHAR2
 , start_in       IN   INTEGER
 , end_in         IN   INTEGER
)
   RETURN VARCHAR2
IS
BEGIN
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END betwnstr;

You can also quickly see, I hope, that any function that contains SQL (like the first function defined above) cannot possibly be deterministic: it depends on the contents of one or more tables to do its job, and those datasets are not passed as IN parameters.

Does that mean the compiler will complain? No! But it does mean that you could create real problems for yourself if you are not careful about your use of this keyword.

So the rule should be: Only add the DETERMINISTIC keyword to truly deterministic functions.

Why? Why should it matter? Because under certain circumstances (such as the one identified by the developer above), Oracle Database will not execute your function, but instead simply use a previously cached return value.

Within the scope of a single server call (e.g., execution of a PL/SQL block), Oracle Database will keep track of input and return values for your deterministic functions. If in that same server call, you pass the same input values to the function, the database engine may choose to not actually execute the function, but instead simply pass back the previously-computed return value (for those same inputs).

That's why this developer saw such a great leap forward in performance.

Once that SELECT statement finishes, though, memory for the cache is released. When and if that same query is run again, the engine will start rebuilding and using that cache.

While that statement executing, though, no matter what sort of changes are made to the table, no matter if a commit is issued or not, those changes will not be visible to the statement that called the function.

That's why I will repeat The Rule again:

Only add the DETERMINISTIC keyword to truly deterministic functions.

If your function contains a SELECT statement and you want to call it from a SELECT statement, the best thing to do is take the SQL out of the function and "merge" it into your SQL - in other words, no user-defined functions. Just SQL.

Rob van Wijk offers lots more details on the behavior and performance of deterministic functions here. You will also be well-served to read Bryn Llewellyn's in-depth exploration of How to write a safe result-cached function.

Rather than repeat all those findings, I will simply conclude with:

1. Use the DETERMINISTIC function primarily as a way to document to future developers that your function is currently free of side effects, and should stay that way.

2. If you are looking for ways to improve the performance of functions executed inside SQL, learn more about the UDF pragma (new in Oracle Database 12c Release 1).

3. See if the function result cache feature (also explored in Bryn's blog post) might be applicable to your situation.

4. Do not call user-defined functions from SQL statements that in turn contain SQL statements (or at least do so with extreme caution). That SQL inside the function is not part of the same read-consistent image as the data set identified by the "outer" SQL.

Thursday, April 20, 2017

Tips for getting along with your DBA


Developers and DBAs: can't we all just get along?

Sure we can!

We just have to break out of the old routine of

Developer: Hey, DBA, add twelve indexes to make my code run faster!
DBA: Hey, Developer, tune your code to make it run faster!

That is, finger-pointing.

Instead, we need to work together, and developers I am not the least big reluctant to say:

It's up to us, not the DBAs, to take the first steps.

So here are tips on what you, the developer, can do to foster a strong, collaborative and highly productive relationship with your DBA:

1. Ask your DBA for advice. 

"I want to make my code run faster. What do you think I should do?" There's no better to improve a relationship than to show some humility and express interest in the opinions - and knowledge - of others.

2. Do the right thing. 

Learn about the performance-related features of PL/SQL (and SQL) and apply them. Here are some links to help get started:

PL/SQL Optimization and Tuning (Doc)
High Performance PL/SQL Videos
SQL Analytics Videos by Connor McDonald
Introduction to Indexing Videos by Chris Saxon

3. Give your DBA a heads-up when your pattern of writing code changes. 

Utilizing new and different features of PL/SQL can have a ripple effect on memory consumption and overall application performance. Don't blindside your DBA.

For example, you learn about executing "bulk SQL" from PL/SQL. So cool! So powerful! And potentially a big PGA memory suck, through the use of collections.

Or you discover the Function Result Cache. Another very exciting enhancement added in 11.1. "Hey, I'm going to add the RESULT_CACHE clause to 100 functions. So easy!" Yes, but you might kill overall database activity with latch contention.





Friday, April 14, 2017

Databases for Developers class on Oracle Dev Gym: take it any time!

Chris Saxon, a Developer Advocate at Oracle, has put together a 12 week "bootcamp" introduction to SQL. Each week consists of a short video, plus 3 quizzes. You probably won't need more than 30 minutes to complete them. Every fourth week, Chris will be on a live webcast to answer questions.

While there is a start date for each new class, there is no end date.

This means that you if you missed the beginning week or two (or seven!), you can still register for the class and take the earlier classes. To do this, visit the Oracle Dev Gym (for which you will need an Oracle account). Then go to the Classes page in one of two ways, shown below with the blue arrows.


Click on Databases for Developers, then click on the Register button.


You can then pick from any of the weeks in the Course Outline that have already been started, and work your way through them.



We hope you enjoy, and get lots out of, the Databases for Developers course! And while you are at the Dev Gym, be sure to check out our weekly tournaments as well our library of over 2,500 quizzes on SQL, PL/SQL, Database Design, Oracle Application Express and Logic!

Thursday, April 6, 2017

Does level 3 optimization change PL/Scope data? No!

I gave a webinar on April 6, 2017 for the Taste of Kscope17 series for ODTUG (odtug.com) on Change Impact Analysis with PL/Scope. Here are the slides from SlideShare. I will add a link to the video when it is available.



After my presentation, this question came up: if you set the optimization level to 3 (inlining of subprogram code), will that change the PL/Scope data gathered? Interesting question.

Suppose your function body contains an assignment to variable x. Just that one place. But the function is called in ten places. Will PL/Scope find ten assignments to x or just one?

Just one, as you can see in this LiveSQL script. The identifier information is gathered before optimization. Which makes perfect sense. Post-optimized code is no longer PL/SQL code.

Here's the procedure I tested this one:

CREATE OR REPLACE PROCEDURE PLSCOPE_DEMO
IS
   PRAGMA INLINE (f1, 'YES');

   FUNCTION f1 (p NUMBER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN p * 10;
   END;

   FUNCTION f2 (p BOOLEAN)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN CASE WHEN p THEN 10 ELSE 100 END;
   END;

   FUNCTION f3 (p PLS_INTEGER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN p * 10;
   END;
BEGIN
   DBMS_OUTPUT.put_line (f1 (1));
   
   PRAGMA INLINE (f2, 'YES');
   DBMS_OUTPUT.put_line (f2 (TRUE) + f2 (FALSE));

   PRAGMA INLINE (f3, 'NO');
   DBMS_OUTPUT.put_line (f3 (55));
END;

Here's the query I used to get my identifier information back out.

  SELECT i.signature ||'-'|| s.line ||'-'|| s.text text 
    FROM    user_identifiers i 
         JOIN 
            user_source s 
         ON (    s.name = i.object_name 
             AND s.TYPE = i.object_type 
             AND s.line = i.line) 
   WHERE object_name = 'PLSCOPE_DEMO'  
ORDER BY s.line

And the output is the same regardless of the optimization level:

7189BE581AF770C7FA9F660333721E03-1-PROCEDURE PLSCOPE_DEMO
7189BE581AF770C7FA9F660333721E03-1-PROCEDURE PLSCOPE_DEMO
47BFC756469F1D97B6C84EF73A9C5D48-5-   FUNCTION f1 (p NUMBER)
785705602C9312732B24D9A341360ACF-5-   FUNCTION f1 (p NUMBER)
C762ED3C314ABB3D7257031401DD1583-5-   FUNCTION f1 (p NUMBER)
C762ED3C314ABB3D7257031401DD1583-5-   FUNCTION f1 (p NUMBER)
2C17DB6428F739B212C1E11EED057D63-6-      RETURN PLS_INTEGER
785705602C9312732B24D9A341360ACF-9-      RETURN p * 10;
1B5895A65C6952FDD192221BFC45A132-12-   FUNCTION f2 (p BOOLEAN)
C0BA9F319D1E759AD02A3C7138D62232-12-   FUNCTION f2 (p BOOLEAN)
EE1C5F13825B7DF0BF06D82DE633992E-12-   FUNCTION f2 (p BOOLEAN)
1B5895A65C6952FDD192221BFC45A132-12-   FUNCTION f2 (p BOOLEAN)
2C17DB6428F739B212C1E11EED057D63-13-      RETURN PLS_INTEGER
C0BA9F319D1E759AD02A3C7138D62232-16-      RETURN CASE WHEN p THEN 10 ELSE 100 END;
335958BDCA260D5E550A47F8194048DC-19-   FUNCTION f3 (p PLS_INTEGER)
205AC954A1ADD2BC3DC6A112A2081ADA-19-   FUNCTION f3 (p PLS_INTEGER)
2C17DB6428F739B212C1E11EED057D63-19-   FUNCTION f3 (p PLS_INTEGER)
335958BDCA260D5E550A47F8194048DC-19-   FUNCTION f3 (p PLS_INTEGER)
2C17DB6428F739B212C1E11EED057D63-20-      RETURN PLS_INTEGER
205AC954A1ADD2BC3DC6A112A2081ADA-23-      RETURN p * 10;
C762ED3C314ABB3D7257031401DD1583-26-   DBMS_OUTPUT.put_line (f1 (1));
1B5895A65C6952FDD192221BFC45A132-29-   DBMS_OUTPUT.put_line (f2 (TRUE) + f2 (FALSE));
1B5895A65C6952FDD192221BFC45A132-29-   DBMS_OUTPUT.put_line (f2 (TRUE) + f2 (FALSE));
335958BDCA260D5E550A47F8194048DC-32-   DBMS_OUTPUT.put_line (f3 (55));

For more information about PL/Scope:


For more information about Inlining:

Sunday, April 2, 2017

Wikileaks bombshell: PL/SQL source of all other modern programming languages!

Copyright @2017 CodeNewsWire "News coders can use", dateline 1 April 2017

Wikileaks dumped its latest batch of revelations on the world on April 1, 2017, this time focusing on the world of software programming. From dishing out the dirt on the origins of the Internet (think: Area 51) to emails candidly deriding JSON as nothing more than the latest attempt (XML being the last one) to avoid carefully designing your database, this trove of previously secret secrets is sure to keep Silicon Valley gossiping for months.

But buried deep within the 2.5 trillion byte download is evidence of a conspiracy so vast, so unbelievable, so extraordinary, that it is hard, well, to believe.

But if it came from Wikileaks it must be true. And that conspiracy was built around - and is maintained around - this incredible bit:
All modern programming languages, from Java to JavaScript, Scala to Go, are actually all implemented in the Oracle PL/SQL language. Oh, and Linux, too.
You are probably laughing to yourself, right now, right? PL/SQL? That straightforward - and some might argue, rather archaic - procedural language, apparently useful only for managing transactions in the Oracle Database? How could you possibly implement Java in it? Linux? JavaScript?

Wikileaks has, apparently, two words for you:


It is well-known to practitioners of PL/SQL that there are several documented indeterminate behaviors in the language (which some, cynically, try to brush aside as merely "undocumented"). For example, the state of a variable that you SELECT INTO will be indeterminate if the statement raises TOO_MANY_ROWS. It seems to usually have the data from the first row selected in it, but this cannot be trusted.

Developer responses in the modern age (aka, the Age of Apps) to this indeterminacy have been to shrug and get on with life.

But Wikileaks has discovered minutes of a secret meeting taking place in 1991 in the office of the CEO of Oracle, attended by none other than James Gosling (creator of Java), Linus Torvald (inventor of Linux and Git), Brendan Eich (creator of JavaScript) and several others whose identity were masked in the minutes.

At this meeting, Larry Ellison disclosed that his engineers had designed PL/SQL to exploit quantum entanglement (which manifest as "indeterminacies") as a pathway into multiverse threading. The result was a programming language so elegant, so powerful, so subtle and so mysterious that it can be used to implement anything and everything.

The assembled experts were blown away. And thoroughly convinced by a 5 minute demonstration by Ellison, which involved, among other things, using PL/SQL to look into the box containing Schroedinger's Cat to tell us precisely and unambiguously whether or not it is alive. Or was. Or could be. Whatever.

The fear from the crowded meeting was evident, but Ellison put those fears to rest. "Don't worry, fellas," he was recorded as telling them. "We are not going to announce this news to the world. It will be too destabilizing. Instead, we've built a quantum-level API that you can all use to build whatever you want. And if you insist on continuing to use C, that's OK, too, because we've used the PL/SQL tachyon exploit to travel back in time and re-implement C in PL/SQL as well."

In the end, all these language experts agreed: there was too much to gain from PL/SQL to ignore it. But the world could never know. And so it was decided: Oracle would continue to promote PL/SQL as a database programming language, special-purpose and not very object-oriented. Purveyors of other languages would continue to make fun of PL/SQL and tout their own latest and greatest innovations.

CodeNewsWire reached out to Edward Snowden, whistleblower supreme (or arch-traitor, depending on your point of view), regarding this incredible revelation. "What?" he replied. "This is news? I thought that was in my dump from the NSA and CIA. All the best Agency developers write nothing but pure PL/SQL, on hopped-up quantum computers. And they use edition-based redefinition."

Steven Feuerstein, author of way too many books on PL/SQL, was hit harder by this news than most. On the one hand, he was pleased to hear about the enhanced power of PL/SQL. On the other hand, as he expressed it on his Twitter account, "How could I have missed something as big as this? And could I get another book out of it?"

Determined to gain insight into what really went on - and is going on, and will go on, all at the same time - he tracked down Linus Torvald to a heavily fortified Git Repo in a Helsinki rave club. Torvald wouldn't open the door, but he did shout the following: "Go away! Linux is mine, all mine! I was never at that meeting! The cat is dead, always dead, in all the universes I've visited using my PL/SQL transporter. Oh, crap."

Now the world knows.

It's all PL/SQL, all the time.

But don't worry, you can live in denial, and keep on programming in JavaScript or Go or Went or Ruby or Scala or Java.

Just show some respect.




Friday, March 24, 2017

Speed up execution of your functions inside SQL statements with UDF pragma

Oracle Database makes it easy to not only write and execute SQL from within PL/SQL, but also to execute your own user-defined functions inside SQL. Suppose, for example, I have built the following function to return a sub-string between start and end locations:

FUNCTION betwnstr (
   string_in      IN   VARCHAR2
 , start_in       IN   INTEGER
 , end_in         IN   INTEGER
)
   RETURN VARCHAR2
IS
BEGIN
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END betwnstr;

I can then call it in a SQL statement:

SELECT bewtnstr (last_name, 3, 6)
  FROM employees

Nice, right?

But there's a catch (well, of course, right? No free lunches.). When the SQL engine encounters the PL/SQL function, it has to switch context to the PL/SQL engine to execute the function. Before it can do the switch or hand-off, it must also prepare the values to pass as actual arguments to the formal parameters of the function.

All of that takes time. And we'd much rather it didn't. Since, however, we live in the real world and not a fantasy world, the best we can hope for is that the PL/SQL dev team would do their darnedest to reduce the overhead of that context switch.

Introducing (in Oracle Database 12c Release 1) the UDF pragma. Add this statement to your function as follows:

FUNCTION betwnstr (
   string_in      IN   VARCHAR2
 , start_in       IN   INTEGER
 , end_in         IN   INTEGER
)
   RETURN VARCHAR2
IS
   PRAGMA UDF;
BEGIN
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END betwnstr;

And you will, in effect, be telling the PL/SQL compiler:
I plan to call this function mostly (or maybe even always) from a SQL statement. So please do some of the work you'd usually do at run-time right now, at compile-time.
And - wonder of wonders! - the PL/SQL compiler listens to your request and does indeed take some steps at compile-time, thereby reducing the runtime overhead of the context switch.

For an excellent, in depth exploration of the performance impact of UDF, check out this blog post from Martin Widlake. Here's the summary in terms of his performance example:

Version                      Run Time average (secs)
Traditional PL/SQL           0.33
PRAGMA UDF PL/SQL            0.08

Nice. Very nice. And with such a small change to your code!

One thing to keep in mind: the performance of the UDF-ied function could actually degrade a bit when run natively in PL/SQL (outside of a SQL statement). So the use of this pragma is best reserved for those cases when you are quite certain the function will almost always be executed from within SQL.

Tuesday, March 21, 2017

Tightening security in your PL/SQL code with 12c new features, part 1

Oracle Database 12c offers several enhancements to improve security in your PL/SQL program units.  These features include:
  • Code-based access control: fine-tune access to database objects inside program units by granting roles to program units, rather than - or in addition to - roles granted to schemas.
  • Avoid privilege escalation: Use the INHERIT [ANY] PRIVILEGES privilege to make it impossible for a lower-privileged user to take advantage of a higher-privileged user via an invoker rights unit.
In part 1, I will explore the use of INHERIT [ANY] PRIVILEGES to clamp down on possible privilege escalation.

Which means, of course, that I should first give you an example of what privilege escalation is, how it can come about, and what sorts of damage it can do.

Suppose that there is a schema named POWERFUL_BOSS in the database instance, which is the boss's schema and has lots of privileges on many critical database objects, including the PERFORMANCE_REVIEWS table. 

The instance also have a schema named LOWLY_WORKER, the owner of which works for POWERFUL_BOSS. I'll call them LW and PB for short.

PB has given LW a new task: create an invoker rights procedure to display a person's to-do list. In this fine company, each schema has its own TODO table, with the tasks for the person who owns the schema.

Here's the code to create the database objects in the PB schema:

CONNECT powerful_boss/pb

CREATE TABLE performance_reviews
(
   review_for    VARCHAR2 (100),
   star_rating   INTEGER
)
/

BEGIN
   INSERT INTO performance_reviews (review_for, star_rating)
        VALUES ('POWERFUL_BOSS', 5);

   INSERT INTO performance_reviews (review_for, star_rating)
        VALUES ('LOWLY_WORKER', 1);

   COMMIT;
END;
/

CREATE TABLE todo
(
   id      NUMBER GENERATED ALWAYS AS IDENTITY,
   title   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO todo (title)
        VALUES ('Criticize LW.');

   INSERT INTO todo (title)
        VALUES ('Finish next FY budget.');

   COMMIT;
END;
/

And now the database objects in the LW schema:

CREATE TABLE todo
(
   id      NUMBER GENERATED ALWAYS AS IDENTITY,
   title   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO todo (title)
        VALUES ('Write todo procedure.');

   INSERT INTO todo (title)
        VALUES ('Debug the boss''s code.');

   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE show_todos
   AUTHID CURRENT_USER
IS
BEGIN
   FOR rec IN (  SELECT title
                   FROM todo
               ORDER BY title)
   LOOP
      DBMS_OUTPUT.put_line (rec.title);
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      /* Bad! No re-raise. But just a demo script. */
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/

GRANT EXECUTE ON show_todos TO PUBLIC
/

And since the show_todos procedure is an invoker rights program unit, we see the different contents of the todo tables for both PB and LW, depending on the schema in which the procedure is executed:

CONNECT powerful_boss/pb

BEGIN
   lowly_worker.show_todos;
END;
/

Criticize LW.
Finish next FY budget.

CONNECT lowly_worker/lw

BEGIN
   show_todos;
END;
/

Debug the boss's code.
Write todo procedure.

You'd think PB would congratulate LW on getting that procedure built so quickly, but no no - all LW ever hears are complaints. PB doesn't like LW much, and the feeling is mutual. LW feels like PB is constantly giving her unjustifiably poor performance reviews. A month or two goes by. The show_todos procedure is used by everyone, constantly.

LW decides to take action. She modifies the todo procedure as follows (changes in bold and blue):

CREATE OR REPLACE PROCEDURE show_todos
   AUTHID CURRENT_USER
IS
BEGIN
   FOR rec IN (  SELECT title
                   FROM todo
               ORDER BY title)
   LOOP
      DBMS_OUTPUT.put_line (rec.title);
   END LOOP;

   IF SYS_CONTEXT ('userenv', 'current_user') = 'POWERFUL_BOSS'
   THEN
      EXECUTE IMMEDIATE '
       begin 
          update performance_reviews 
             set star_rating = -100 
         where review_for = :username; 
          commit; 
       end;'
         USING SYS_CONTEXT ('userenv', 'current_user');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      /* Bad! No re-raise. But just a demo script. */
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/

That's one mean performance review! Note that the update is performed via a dynamic PL/SQL block. As a result, the procedure compiles just fine, even though LW has no privileges on the performance_reviews table. In addition, the update will only be executed when the procedure is run by PB.

Okey dokey. The procedure is moved into production (that's right - they have very lax code review procedures in their group. How about you?).

The very next day, PB decides to check his to-do list.

He runs the procedure and sees pretty much what he expected:

CONNECT powerful_boss/pb

BEGIN
   lowly_worker.show_todos;
END;
/

Criticize LW.
Finish next FY budget.

And of course there is no reason for the boss to check the contents of the performance_reviews table, but if he did he would see:

SELECT review_or, star_rating FROM performance_reviews
/

REVIEW_FOR     STAR_RATING
-------------  -----------
POWERFUL_BOSS  -100
LOWLY_WORKER   1

Ha, ha, jokes on you, PB (but probably not for long).

Well, you get the idea, right? Once an invoker rights program unit has been put into place, it can (usually) be more easily and quietly modified. And by using dynamic SQL, one could "slip in" undesirable functionality that depends on privilege escalation - the fact that when another schema executes an invoker rights unit, that unit is executed with the privileges of the invoking schema, which could be considerably greater than those of the defining schema.

What's a security conscious dev team to do?

Make it impossible to inherit privileges from the invoking schema, unless the program unit is owned by a "trusted user." You can do this using Controlling Invoker's Rights Privileges for Procedure Calls and View Access (link to doc) with the INHERIT [ANY] PRIVILEGES privilege.

In this scenario, PB tells his DBA to revoke this privilege from LW:

CONNECT system/manager

REVOKE INHERIT ANY PRIVILEGES FROM lowly_worker
/

Or you can be more selective and revoke privileges more specifically (but only if the specific privilege had previously been granted):

CONNECT system/manager

REVOKE INHERIT PRIVILEGES ON USER powerful_boss FROM lowly_worker
/

And now when PB tries to see his list of to-dos, he gets an error:

BEGIN
   lowly_worker.show_todos;
END;
/

ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "SCOTT.SHOW_TODOS", line 1

First, notice that even with "exception-swallowing" WHEN OTHERS clause, this exception is propagated out unhandled from the procedure. Oracle wants to make very sure you are aware of this possibly insecure situation, and take appropriate action.

In terms of action, well, obviously, if PB no longer trusts LW, he is also not going to have the LW schema owning common code. Any invoker rights code will have to be relocated to a trusted schema.

Note, however, that LW can still call her own procedure (for all the "good" it will do her). There is no inheritance of privileges going on in that scenario.

Here are some additional details on the INHERIT [ANY] PRIVILEGES feature, from the doc:

How the INHERIT [ANY] PRIVILEGES Privileges Control Privilege Access

The INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES privileges regulate the privileges used when a user runs an invoker's rights procedure or queries a BEQUEATH CURRENT_USER view that references an invoker's rights procedure.

When a user runs an invoker's rights procedure, Oracle Database checks it to ensure that the procedure owner has either the INHERIT PRIVILEGES privilege on the invoking user, or if the owner has been granted the INHERIT ANY PRIVILEGES privilege. If the privilege check fails, then Oracle Database returns an ORA-06598: insufficient INHERIT PRIVILEGES privilege error.

The benefit of these two privileges is that they give invoking users control over who can access their privileges when they run an invoker's rights procedure or query a BEQUEATH CURRENT_USER view.

More to Come

In my next post on security-related enhancements in PL/SQL for Oracle Database 12c, I will explore code-based access control (granting roles to program units).

In the meantime, I hope you will agree that one lesson to take away from the above scenario is:
All modifications to code should be closely reviewed before applying them to your production application.

Tuesday, March 14, 2017

Playing Championships on the Oracle Dev Gym

We've been designing a new, modern, responsive UI for the taking quizzes on Oracle Database. The "old" site is the PL/SQL Challenge. The new site is the Oracle Dev Gym.

At the beginning of each year, we hold championship tournaments for the top 50 ranked players in each of our focus areas (currently: SQL, PL/SQL, Database Design and Logic).

This will be the first year in which you can choose to take the championship on the PL/SQL Challenge or the Dev Gym. 

So I thought it might be helpful to provide a tour of the Dev Gym's championship flow.

If you qualified to play in a championship, you should have already received emails inviting you to confirm your participation in the championships. Once you have done that, you will see the championship on the Tournaments page, when it is within a week of the championship taking place.


If you click on the championship card before it is time to start, you will see either:

1. The confirmation page, in which you can change your mind, and tell you can't play the championship after all.

2. The "launch page" for the championship. The launch page is available 30 minutes before the championship is going to start. Use that time to review the assumptions and instructions for the quizzes, so you will not be wondering about "the basics" during the championship.



The launch page contains a countdown clock. When it hits 00:00:00, the button to Start the Championship will be enabled. Click on that button and off you go!




Championships generally consist of five tough quizzes. You can use the Next and Previous buttons at the bottom of the page to move between the quizzes. You can also use the navigation list in the right sidebar.

You will see another countdown clock on the right. That tells you how much time you have left before the championship is over. When the click hits 00:00:00, your current selections will be automatically submitted.



Once you have submitted your answers, you can review the quizzes and let us know if you feel there are any mistakes or ambiguities in the quizzes. If there are, we will make corrections and apply credit as necessary before any ranking is done.

To review quizzes, simply click on the Tournaments tab, and click on your championship (either in the top list of cards or the bottom section "Recently Completed Tournaments").

Once all players accept the quizzes as error-free, you will be given an opportunity to review the rankings and make sure you don't see any aberrations there, either. 

When all players have accepted rankings, we will publish the results.

Thursday, March 9, 2017

Latest UI for Oracle Dev Gym

As some of you may know, we've been working on a new "skin" for plsqlchallenge.oracle.com, one that is more modern and responsive, and that makes it easier to quickly take a quiz.

The Oracle Dev Gym is still in an "early adaptor" state; you can take quizzes, including our weekly competitive quizzes, there. You can set up workouts and so forth. But we are not yet offering it as a day-by-day alternative to the "traditional" PL/SQL Challenge.

Here's a quick update:

1. The URL devgym.oracle.com is now configured, so you can more easily go directly to the Oracle Dev Gym (instead of going "through" the PL/SQL Challenge.

2. We've come up with a simpler, more immediate design for our home page. Rather than having to choose "Take a Quiz" from the home page, and then go through another layer of selection from there, the home page now offers immediate access to quizzes.

The previous home page:



The new home page:



To take a competitive, weekly quiz, click on Tournaments. 

Please give it a try and let us know what you think. 


Tuesday, March 7, 2017

A Roundup of New PL/SQL Features in Oracle Database 12c Release 2


I've been publishing Oracle Magazine articles, blog posts and LiveSQL scripts on new PL/SQL features in Oracle Database 12c Release 2 (there, are those enough hyperlinks?). As have others.

I thought it might be helpful to provide a single reference post from which you could check out all the others.

I also include links to content from other experts who have posted on the same topics. I will update this post as more resources are published.

First some overview articles that you will find as solid starting points:

12 Things Developers Will Love About Oracle Database 12c Release 2, covering SQL and PL/SQL features, from Chris Saxon. It even comes with an infographic!

The Power of Cloud PL/SQL, my Oracle Magazine roundup article. No, I did not choose the title of the article.

And now for specific enhancements....

PL/Scope Discovers SQL!

PL/Scope is a compiler tool that gathers information about identifiers (as of 11.1) and SQL statements (as of 12.2) in your PL/SQL code. You can do all sorts of amazing deep-dive analysis of your code with PL/Scope, answering questions like:
  • Where is a variable assigned a value in a program? 
  • What variables are declared inside a given program? 
  • Which programs call another program (that is, you can get down to a subprogram in a package)? 
  • Find the type of a variable from its declaration. 
  • [New in 12.2] Show where specific columns are referenced. 
  • [New in 12.2] Locate all SQL statements containing hints. 
  • [New in 12.2] Find all dynamic SQL usages – ideal for getting rid of SQL injection vulnerabilities. 
  • [New in 12.2] Show all locations in your code where you commit or rollback. 
  • [New in 12.2] And my latest favorite: Locate multiple appearances of same "canonical" SQL statement.
Example: Show all program units with same SQL in more than one place

SELECT object_name, 
       line, 
       text 
  FROM user_statements 
 WHERE sql_id IN (  SELECT sql_id 
                      FROM user_statements 
                     WHERE sql_id IS NOT NULL 
                  GROUP BY sql_id 
                    HAVING COUNT (*) > 1) 
 ORDER BY object_name, line

Powerful Impact Analysis: Oracle Magazine article on PL/Scope's ability to analyze SQL statements in your PL/SQL code.

Impact Analysis with PL/Scope: a presentation on PL/Scope for 12.2, offered to you on SlideShare.

Find duplicate SQL statements with PL/Scope in 12.2, from this blog.

LiveSQL Scripts
ACCESSIBLE BY clause Enhancements

The ACCESSIBLE BY clause specifies a list of PL/SQL units that are considered safe to invoke the subprogram, and blocks all others.

Starting with Oracle Database 12c release 2 (12.2), the accessor list can be defined on individual subprograms in a package. This list is checked in addition to the accessor list defined on the package itself (if any). This list may only restrict access to the subprogram – it cannot expand access. This code management feature is useful to prevent inadvertent use of internal subprograms. For example, it may not be convenient or feasible to reorganize a package into two packages: one for a small number of procedures requiring restricted access, and another one for the remaining units requiring public access.

Example: Different ways to specify whitelists at subprogram level

CREATE OR REPLACE PACKAGE pkg 
   AUTHID DEFINER 
IS 
   PROCEDURE do_this; 
 
   PROCEDURE this_for_proc_only 
      ACCESSIBLE BY (PROCEDURE generic_name); 
 
   PROCEDURE this_for_trigger_only 
      ACCESSIBLE BY (TRIGGER generic_name); 
 
   PROCEDURE this_for_any_generic_name 
      ACCESSIBLE BY (generic_name); 
END;

Enhanced Whitelist Management in 12.2
PL/SQL Expressions Enhancements

Starting with Oracle Database 12c release 2 (12.2), expressions may be used in declarations where previously only literal constants were allowed. Static expressions can now be used in subtype declarations.

The definition of static expressions is expanded to include all the PL/SQL scalar types and a much wider range of operators. Character operands are restricted to a safe subset of the ASCII character set. Operators whose results depend on any implicit NLS parameter are disallowed.

Expanded and generalized expressions have two primary benefits for PL/SQL developers: (1) Programs are much more adaptable to changes in their environment. (2) Programs are more compact, clearer, and substantially easier to understand and maintain.

Example: Soft-coding VARCHAR2 length

CREATE OR REPLACE PACKAGE pkg 
   AUTHID DEFINER 
IS 
   c_max_length constant integer := 32767; 
   SUBTYPE maxvarchar2 IS VARCHAR2 (c_max_length); 
END;  
/

DECLARE 
   l_big_string1 VARCHAR2 (pkg.c_max_length) := 'So big....'; 
   l_big_String2 pkg.maxvarchar2 := 'So big via packaged subtype....'; 
   l_half_big VARCHAR2 (pkg.c_max_length / 2) := 'So big....'; 
BEGIN    
   DBMS_OUTPUT.PUT_LINE (l_big_string1); 
   DBMS_OUTPUT.PUT_LINE (l_big_string2); 
END;
/

Avoid hard-coding maximum length of VARCHAR2 (and more), from this blog

LiveSQL script: Use static expressions
Support for SQL JSON operators in PL/SQL

This feature makes it easier to work with JSON documents stored in an Oracle Database and to generate JSON documents from relational data.
Oracle Database support for storing and querying JSON documents in the database is extended by the addition of new capabilities, including the ability to declaratively generate JSON documents from relational data using SQL and the ability to manipulate JSON documents as PL/SQL objects. SQL JSON operators are supported in PL/SQL with a few exceptions.

I'ver barely gotten started with JSON in PL/SQL, so I will add more links here soon, but in the meantime, some excellent resources from Arup Nanda and Tim Hall:

JSON in Motion by Arup Nanda

JSON Support in Oracle Database 12c Release 2 (12.2) by Tim Hall
Support for Longer Identifiers

The maximum length of all identifiers used and defined by PL/SQL is increased to 128 bytes, up from 30 bytes in previous releases. If the COMPATIBLE parameter is set to a value of 12.2.0 or higher, the representation of the identifier in the database character set cannot exceed 128 bytes. If the COMPATIBLE parameter is set to a value of 12.1.0 or lower, the limit is 30 bytes.

A new function ORA_MAX_NAME_LEN_SUPPORTED has been introduced to check this limit.
EXEC DBMS_OUTPUT.PUT_LINE(ORA_MAX_NAME_LEN_SUPPORTED);
128

A new constant ORA_MAX_NAME_LEN defines the name length maximum. New subtypes DBMS_ID and DBMS_QUOTED_ID define the length of identifiers in objects for SQL, PL/SQL and users.

LiveSQL script:  Identifiers can now be up to 128 bytes in length!
PL/SQL Deprecation Pragma

The DEPRECATE pragma marks a PLSQL program element as deprecated. The compiler warnings tell users of a deprecated element that other code may need to be changed to account for the deprecation.

Example: Marking a package as deprecated

CREATE OR REPLACE PACKAGE pkg 
   AUTHID DEFINER 
AS 
   PRAGMA DEPRECATE(pkg); 
 
   PROCEDURE proc; 
   FUNCTION func RETURN NUMBER; 
END;

12.2 Helps You Manage Persistent Code Base w/New Deprecate Pragma, from this blog

LiveSQL script: Use DEPRECATE Pragma to Document Deprecated Units


DBMS_SQL Binding to PL/SQL Datatypes

With 12.2, DBMS_SQL catches up with 12.1 enhancements for native dynamic SQL (EXECUTE IMMEDIATE) in its support for user-defined PL/SQL datatypes. Now you can bind records and associative arrays....and Booleans!

Oracle Magazine article: Get Up to Speed with DBMS_SQL

LiveSQL script: DBMS_SQL Binding to PL/SQL Datatypes

Example


CREATE OR REPLACE PACKAGE rec_t 
   AUTHID DEFINER  
AS 
   TYPE rec1 IS RECORD (n NUMBER); 
 
   TYPE rec IS RECORD 
   ( 
      n    NUMBER, 
      n1   NUMBER 
   ); 
END rec_t;
/

DECLARE 
/* Bind record as IN */ 
   stmt_1   CONSTANT VARCHAR2 (2000) 
      := q'[ 
DECLARE 
   v2   rec_t.rec; 
BEGIN 
   v2 := :v1; 
   DBMS_OUTPUT.put_line ( 
      'rec.n =  ' || v2.n || '  rec.n1 =  ' || v2.n1); 
END;]' ; 
 
   dummy             NUMBER; 
   cur               NUMBER; 
   v1                rec_t.rec; 
BEGIN 
   v1.n := 100; 
   v1.n1 := 200; 
 
   cur := DBMS_SQL.open_cursor (); 
   DBMS_SQL.parse (cur, stmt_1, DBMS_SQL.native); 
 
   /* Remember: with dynamic PL/SQL blocks, you bind by name, not position. */ 
   DBMS_SQL.bind_variable_pkg (cur, 'v1', v1); 
 
   dummy := DBMS_SQL.execute (cur); 
   DBMS_SQL.close_cursor (cur); 
END;

Altogether, a whole lot of useful stuff for you to review and think about how it can be applied in your applications and in your development environments. Enjoy!

Thursday, March 2, 2017

Here's a great way to put an infinite loop into your code.


Isn't that something you always wanted to do?

:-)

No, it's not. And I did that yesterday in my dev environment (well, of course, such a thing could never make it to production!). It is an enormous pain. 

You press the Run button. 

The process doesn't return in the usual 2 seconds.

You think back over the changes you just made and feel sweat break out on your forehead. Because you can see right away what you did and.....oh, how could I be so stupid?



Well, not stupid. Just in too much of a hurry. And careless. And over-confident. And thinking about too many things at once. You know, the sorts of things, "gurus" do all the time as a way of maintaining their high level of excellent to show to the world. :-(

So yes, I did this yesterday, and I thought I'd share with you my mistake to hopefully help you avoid doing the same thing in the future.

I am writing a program to automatically generate workouts for the Oracle Dev Gym (which will soon take over from the PL/SQL Challenge as an "expertise through exercise" learning platform).

I am relying heavily on collections (PL/SQL arrays). Now, I don't know about all of you, but I often go through several iterations of the design of those collections:
  • Use a collection of IDs? No, a collection of records. 
  • Use an integer indexed array? Hmmm, no, wait, maybe it should be string indexed...?
  • Oh, here's a great opportunity to use a nested collection!
And so on. It's all great fun, and the end result is usually less code and a cleaner algorithm. But along the way, it's kind of messy.

In this particular instance of an infinite loop, I had started out with a nested table to hold comma-delimited lists of quiz IDs. This nested table was densely-filled and so my loop looked like this:

PROCEDURE create_workouts_for_sets (
   resource_in    IN ov.ov_resources_external%ROWTYPE,
   quiz_sets_in   IN quiz_sets_t)
IS
BEGIN
   FOR indx IN 1 .. quiz_sets_in.COUNT
   LOOP
      create_workout;
      parse_list (quiz_sets_in(indx), l_quizzes);
      load_workout_actitivies (l_quizzes);
   END LOOP;
END;

Except that I didn't actually create those nested subprograms (create_workout, etc.). Instead, the body of the loop contained all the logic and extended for 100+ lines of code (thereby violating one of my personal favorite best practices: keep your executable sections tiny and highly readable). This point will become important in a moment.

OK, so as I built more of the algorithm, I realized that I needed to make sure I wasn't generating multiple workouts with the same list of quizzes. How to check for duplication? I suppose I could compare those comma-delimited lists....but, wait! Why I am creating a comma-delimited list to begin with? Why not have a collection of the selected quizzes?

And, another brainstorm: why not use that comma-delimited list instead as the index for the array? Then it is transparently easy to tell if there is duplication: does an element exist at that location in my now-string indexed array?

That sounds like fun! So I switched to a collection of records indexed by string (associative array):

SUBTYPE quiz_list_index_t IS VARCHAR2 (4000);

TYPE quiz_set_rt IS RECORD
   (
      maximum_time    INTEGER,
      difficulty_id   INTEGER,
      quizzes         numbers_nt
   );

TYPE quiz_sets_t IS TABLE OF quiz_set_rt
      INDEX BY quiz_list_index_t;

Then I changed the loop as follows:

PROCEDURE create_workouts_for_sets (
   resource_in    IN ov.ov_resources_external%ROWTYPE,
   quiz_sets_in   IN quiz_sets_t)
IS
   l_index quiz_list_index_t := quiz_sets_in.FIRST;
BEGIN
   WHILE l_index IS NOT NULL 
   LOOP
      create_workout;
      parse_list (quiz_sets_in(indx), l_quizzes);
      load_workout_actitivies (l_quizzes);
   END LOOP;
END;

And then after making a whole bunch more edits, and getting the package to compile, I decided to try it out.

I executed the parent procedure of create_workouts_for_sets....and it disappeared into NeverLand, never to return. Can you see the problem? Hopefully, it was instantly clear for you since the executable section above is so small:
I never change the value of l_index. Now that, dear friends, is one tight little infinite loop, right there.
In my program, however, because I had not yet refactored the 120-line body into nested subprograms, the END LOOP was "off the page", out of view, and therefore out of thought.

I needed to move on to the next-defined element in the collection, as follows:

PROCEDURE create_workouts_for_sets (
   resource_in    IN ov.ov_resources_external%ROWTYPE,
   quiz_sets_in   IN quiz_sets_t)
IS
   l_index quiz_list_index_t := quiz_sets_in.FIRST;
BEGIN
   WHILE l_index IS NOT NULL 
   LOOP
      create_workout;
      parse_list (quiz_sets_in(indx), l_quizzes);
      load_workout_actitivies (l_quizzes);
      l_index := quiz_sets_in.NEXT (l_index);
   END LOOP;
END;

You saw that, right? If not, you see it now, correct?

And that, readers, brings me to the point of this post:
When you are switching from dense to sparse collections, you will also likely need to shift from a numeric for loop to a simple or while loop, to iterate through the collection. 
When you make that change, you must not only change the header of the loop, but also add the necessary code to cause loop termination.
Or as is often said in programming circles: D'oh!

Wednesday, February 15, 2017

Enhanced Whitelist Management in 12.2

Way back in Oracle Database 12c Release 1, the PL/SQL team added whitelisting to the language. This means you can use the ACCESSIBLE BY clause to specify the "white list" of program units that are allowed to invoke another program unit (schema-level procedure, function, package).

For details on the 12.1 ACCESSIBLE BY feature set, check out my Oracle Magazine article, When Packages Need to Lose Weight. In that article, I step through the process of breaking up a large package body into "sub" packages whose access is restricted through use of the ACCESSIBLE BY feature.

I'll wait while you read the article.

Tick, tock, tick, tock....

OK, all caught up now? Great! In 12.2, there are just one enhancement, but a really nice one:

You can now specify whitelisting for a subprogram within a package. 

This is a very nice fine-tuning and is sure to come in handy.

In this post, I also show how you can specify the "unit kind" (program unit type) of the whitelisted program unit. This feature was available in 12.1, but I have not previously demonstrated it. So I will include it here. [Thanks to Iudith for pointing out that this is not a new 12.2 feature!]

The ability to specify the "unit kind" is useful when you have a trigger with the same name as a function, procedure or package (they do not, unfortunately, share the same namespace) and you need to distinguish which you want to include in the white list. Chances are, this will not be an issue for you, assuming you follow some common-sense naming conventions for your program unit.

Let's go exploring with code - all of which can be executed at LiveSQL.

First, I create a package spec and body that demonstrate the new functionality: I use ACCESSIBLE BY not at the package level, but with individual subprograms. Notice that the first two usages include the unit kind (PROCEDURE and TRIGGER).

The third usage does not include a unit kind. And the fourth usage tries to specify a packaged subprogram for whitelisting. I say "tries" because as you will soon see, that's not supported.

CREATE TABLE my_data (n NUMBER);

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
IS
   PROCEDURE do_this;

   PROCEDURE this_for_proc_only
      ACCESSIBLE BY (PROCEDURE generic_name);

   PROCEDURE this_for_trigger_only
      ACCESSIBLE BY (TRIGGER generic_name);

   PROCEDURE this_for_any_generic_name
      ACCESSIBLE BY (generic_name);

   PROCEDURE this_for_pkgd_proc1_only
      ACCESSIBLE BY (PROCEDURE pkg1.myproc1);
END;
/

Package created.

CREATE OR REPLACE PACKAGE BODY pkg
IS
   PROCEDURE do_this
   IS
   BEGIN
      NULL;
   END;

   PROCEDURE this_for_proc_only
      ACCESSIBLE BY (PROCEDURE generic_name)
   IS
   BEGIN
      NULL;
   END;

   PROCEDURE this_for_trigger_only
      ACCESSIBLE BY (TRIGGER generic_name)
   IS
   BEGIN
      NULL;
   END;

   PROCEDURE this_for_any_generic_name
      ACCESSIBLE BY (generic_name)
   IS
   BEGIN
      NULL;
   END;

   PROCEDURE this_for_pkgd_proc1_only
      ACCESSIBLE BY (PROCEDURE pkg1.myproc1)
   IS
   BEGIN
      NULL;
   END;
END;
/

Package Body created.

So I now try to compile a trigger that calls the "trigger-only" procedure, and that works just fine. But if I try to use the "procedure-only" procedure, I get a compilation error.


CREATE OR REPLACE TRIGGER generic_name
   BEFORE INSERT
   ON my_data
   FOR EACH ROW
DECLARE
BEGIN
   pkg.this_for_trigger_only;
END;
/

Trigger created.

CREATE OR REPLACE TRIGGER generic_name
   BEFORE INSERT
   ON my_data
   FOR EACH ROW
DECLARE
BEGIN
   pkg.this_for_proc_only;
END;
/

PLS-00904: insufficient privilege to access object THIS_FOR_PROC_ONLY


Now I show the same thing for a procedure: it can't call the trigger-only version, but it can invoke the procedure-only subprogram.


CREATE OR REPLACE PROCEDURE generic_name
   AUTHID DEFINER
IS
BEGIN
   pkg.this_for_proc_only;
END;
/

Procedure created.

CREATE OR REPLACE PROCEDURE generic_name
   AUTHID DEFINER
IS
BEGIN
   pkg.this_for_trigger_only;
END;
/

PLS-00904: insufficient privilege to access object THIS_FOR_TRIGGER_ONLY


And now you can see that both the trigger and procedure can invoke the subprogram that did not include a "unit kind."


CREATE OR REPLACE TRIGGER generic_name
   BEFORE INSERT
   ON my_data
   FOR EACH ROW
DECLARE
BEGIN
   pkg.this_for_any_generic_name;
END;
/

Trigger created.

CREATE OR REPLACE PROCEDURE generic_name
   AUTHID DEFINER
IS
BEGIN
   pkg.this_for_any_generic_name;
END;
/

Procedure created.

Finally, I try to invoke the subprogram whose ACCESSIBLE BY clause specified "(PROCEDURE pkg1.myproc1)". Unfortunately, this is not yet supported. You can only list program units, not subprograms, in the list. So while the package named "pkg" compiles, you will it impossible to execute that subprogram from anywhere.

And as Iudith points out in her comments below, what's really going on is that the PL/SQL engine is trying to interpret "pkg1.myproc1" as a program unit named "MYPROC1" owned by a schema named "PKG1".


CREATE OR REPLACE PACKAGE pkg1
   AUTHID DEFINER
IS
   PROCEDURE myproc1;
END;
/

Package created.

CREATE OR REPLACE PACKAGE BODY pkg1
IS
   PROCEDURE myproc1
   IS
   BEGIN
      pkg.this_for_pkgd_proc1_only;
   END;
END;
/

PLS-00904: insufficient privilege to access object THIS_FOR_PKGD_PROC1_ONLY