Loading...
I put together this blog post for those interested in learning more about the SmartDB (also or formerly known as "ThickDB") architecture and how to apply it in your applications. I will update it as more resources become available.

What is SmartDB?

Bryn Llewellyn, PL/SQL Product Manager, offers this description:

Large software systems must be built from modules. A module hides its implementation behind an interface that exposes its functionality. This is computer science’s most famous principle. For applications that use an Oracle Database, the database is, of course, one of the modules. The implementation details are the tables and the SQL statements that manipulate them. These are hidden behind a PL/SQL interface.

This is the Smart Database paradigm: select, insert, update, delete, merge, commit, and rollback are issued only from database PL/SQL. Developers and end-users of applications built this way are happy with their correctness, maintainability, security, and performance. But when developers follow the NoPlsql paradigm, their applications have problems in each of these areas and end-users suffer.

Experts

The two leading proponents of SmartDB from Oracle are:

Bryn Llewellyn, Product Manager for PL/SQL and Edition-based Redefinition

Bryn Llewellyn has worked in the software field for more than thirty-five years. He joined Oracle UK in 1990 at the European Development Center to work on the Oracle Designer team. He transferred to the Oracle Text team and then into consulting as the text specialist for Europe. He relocated to Redwood Shores in 1996 to join the Oracle Text Technical Marketing Group. He has been the product manager for PL/SQL since 2001. In 2005, he became responsible, additionally, for edition-based redefinition (EBR for short). This is the Oracle Database capability that supports online application upgrade.

It’s hard for Bryn to remember his life before Oracle. He started off doing image analysis and pattern recognition at Oxford University (programming in FORTRAN) and then worked in Oslo, first at the Norwegian Computing Center and then in a startup. In Norway, Bryn programmed in Simula (its inventors were his close colleagues). This language is recognized as the first object-oriented programming language and was the inspiration for Smalltalk and C++. Bryn is an Oak Table member.

Follow Bryn on Twitter and subscribe to his blog.

Toon Koppelaars, Real World Performance Team

Toon has been promoting the SmartDB architecture for a long time, as witnessed by his authorship of the Helsinki Declaration (IT Version) in 2009.

Follow Toon on Twitter.





Resources

NoPLSql and Thick Database Approaches with Toon Koppelaars
Which one do you think requires a bigger database server?

Toon Koppelaars describes an experiment to measure the work done by Oracle Database to complete a specific task using different approaches. The NoPlsql approach treats the database as no more than a persistence layer, using only naive single-row SQL statements; it implements all business logic outside of it. The Thick Database approach treats the database as a processing engine; it uses a combination of sophisticated set-based SQL statements and PL/SQL to implement all business logic inside it. “No business logic in the database” advocates take note: the Thick Database approach gets the task done with far less database work than the NoPlsql approach. 

Guarding Your Data Behind a Hard Shell PL/SQL API

This session examines in practical detail how to ensure that the hard shell of a database’s PL/SQL API is impenetrable. It advocates strict adherence to the principle of least privilege by using a four-schema model (data, code implementation, API, and connect) and invokers rights units together with code-based access control. Scrupulous care is taken to ensure that the privileges needed for installation and patching are not available at runtime, and the approach is reinforced by secure error-handling.

The Database: Persistence Layer (NoPlsql) or Processing Engine (SmartDB)?

Slide deck from Toon's presentations at ODTUG's Kscope17 conference. Toon goes deep into the question of where business logic should reside, and the benefits you get from putting that logic into the database.

Also: Why SmartDB?

How to install a #SmartDB application back-end

Bryn Llewellyn offers a "sketch" of how developers and DBAs should set up their application in the database to follow a SmartDB architecture.

Why Use PL/SQL?

Bryn Llewellyn's definitive white paper on the key advantages accrued when you use the PL/SQL language, to build secure, maintainable, high performance applications that guarantee data integrity and consistency.

Doing SQL from PL/SQL: Best and Worst Practices

Assuming you buy into the SmartDB paradigm and will enclose your SQL statements inside  PL/SQL "hard shell", this white paper from Bryn Llewellyn will help you do it properly.

Moovit: A View From the Trenches

Millions of people develop applications on top of Oracle Database. The most secure and optimized of those applications take full advantage of SQL and PL/SQL. In this CodeTalk webcast, Steven Feuerstein interviews Oren Nakdimon of Moovit (http://moovitapp.com, lead developer for the backend of this popular transit app, to find out just how he and his small team have made the most of PL/SQL, and how they manage their PL/SQL code base.

How to Prove That Your SmartDB App Is Secure

If you are guarding your data behind a hard shell PL/SQL API as Bryn Llewellyn, Toon Koppelaars and others recommend, then it should be quite easy to prove, that your PL/SQL application is secured against SQL injection attacks. The basic idea is 1) that you do not expose data via tables nor views to Oracle users used in the middle-tier, by end-users and in the GUI; and 2) that you use only static SQL within PL/SQL packages. By following these two rules, you ensure that only SQL statements with bind variables are used in your application, making the injection of unwanted SQL fragments impossible. In this blog post, Philipp Salvisberg shows how to check if an application is complying to these two rules.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
The Oracle Dev Gym PL/SQL Challenge quiz played 28 Apr - 4 May explored the interactions between row-level triggers and multi-row inserts, particularly when it comes to mutating table errors. If you didn't happen to take the quiz and already learn its lesson, here goes.

[Note: you can also click on the link above and play the quiz right now, before you read this post!]

Here's the main rule to keep in mind:
A BEFORE INSERT trigger will not cause a mutating table error as long as the triggering INSERT statement is a single row insert (INSERT-VALUES).
Let's take a closer look.

I create a table and a trigger on that table:

CREATE TABLE qz_flowers
(
fl_num NUMBER,
fl_name VARCHAR2 (30)
)
/

CREATE OR REPLACE TRIGGER qz_flowers_bir
BEFORE INSERT ON qz_flowers
FOR EACH ROW
DECLARE
l_count INTEGER;
BEGIN
SELECT COUNT (*) INTO l_count FROM qz_flowers;
DBMS_OUTPUT.PUT_LINE ('Count = ' || l_count);
END;
/

The trigger queries from the qz_flowers table, which introduces the possibility of a mutating table error.

But if I insert a single row using the INSERT-VALUES format, I do not get that error:

SQL> BEGIN
2 INSERT INTO qz_flowers VALUES (100, 'Orchid');
3 DBMS_OUTPUT.PUT_LINE ('Inserted');
4 END;
5* /

Count = 0
Inserted

That makes, right? If I am inserting a single row in the table and a BEFORE INSERT trigger fires, that row is not yet in the table, the table is not mutating, and there can be no mutating table error.

I can even insert two rows in the same block and no error:

SQL> BEGIN
2 INSERT INTO qz_flowers VALUES (100, 'Orchid');
3 INSERT INTO qz_flowers VALUES (200, 'Tulip');
4 DBMS_OUTPUT.PUT_LINE ('Inserted');
5 END;
6 /
Count = 0
Count = 1
Inserted

The trigger fires for each individual row inserted. The logic I stated above applies to each insert separately: no mutating table error. Ah, but if my INSERT statement inserts (or, as we shall soon see, might insert) more than one row, KABOOM!

SQL> BEGIN
2 INSERT INTO qz_flowers
3 SELECT 100, 'Orchid' FROM DUAL
4 UNION ALL
5 SELECT 200, 'Tulip' FROM DUAL;
6 DBMS_OUTPUT.PUT_LINE ('Inserted');
7 END;
8 /

Error starting at line : 1 in command -
BEGIN
INSERT INTO qz_flowers
SELECT 100, 'Orchid' FROM DUAL
UNION ALL
SELECT 200, 'Tulip' FROM DUAL;
DBMS_OUTPUT.PUT_LINE ('Inserted');
END;
Error report -
ORA-04091: table QDB_PROD.QZ_FLOWERS is mutating, trigger/function may not see it
ORA-06512: at "QDB_PROD.QZ_FLOWERS_BIR", line 4
ORA-04088: error during execution of trigger 'QDB_PROD.QZ_FLOWERS_BIR'
ORA-06512: at line 2
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

Once that first row is inserted, the table is now "mutating" and the SELECT against that table cannot be safely performed.

And even if my INSERT-SELECT only inserts a single row, the ORA-04091 error will be raised.

SQL> BEGIN
2 INSERT INTO qz_flowers
3 SELECT 100, 'Orchid' FROM DUAL;
4 DBMS_OUTPUT.PUT_LINE ('Inserted');
5 END;
6 /

Error report -
ORA-04091: table QDB_PROD.QZ_FLOWERS is mutating, trigger/function may not see it

And what about FORALL? In at least one way, that PL/SQL bulk processing statement acts like an INSERT-SELECT. Specifically, ON INSERT statement-level triggers (firing before or after) fire just once for the entire FORALL statement.

When it comes to the mutating table errors and the BEFORE INSERT trigger, FORALL behaves like a mix of INSERT-VALUES and INSERT-SELECT.

If the FORALL statement binds just a single value from its array, then it behaves like a single row INSERT-VALUES statement:

SQL> DECLARE
2 TYPE flowers_t IS TABLE OF qz_flowers%ROWTYPE;
3 l_flowers flowers_t := flowers_t();
4 BEGIN
5 l_flowers.EXTEND;
6 l_flowers (1).fl_num := 100;
7 l_flowers (1).fl_name := 'Orchid';
8
9 FORALL indx IN 1 .. l_flowers.COUNT
10 INSERT INTO qz_flowers VALUES l_flowers (indx);
11 DBMS_OUTPUT.PUT_LINE ('Inserted');
12 END;
13 /
Count = 0
Inserted

If the FORALL statement binds more than one value from its array, then it behaves like INSERT-SELECT:

SQL> DECLARE
2 TYPE flowers_t IS TABLE OF qz_flowers%ROWTYPE;
3 l_flowers flowers_t := flowers_t();
4 BEGIN
5 l_flowers.EXTEND (2);
6 l_flowers (1).fl_num := 100;
7 l_flowers (1).fl_name := 'Orchid';
8 l_flowers (2).fl_num := 200;
9 l_flowers (2).fl_name := 'Tulip';
10
11 FORALL indx IN 1 .. l_flowers.COUNT
12 INSERT INTO qz_flowers VALUES l_flowers (indx);
13 DBMS_OUTPUT.PUT_LINE ('Inserted');
14 END;
15 /
Count = 0

Error report -
ORA-04091: table QDB_PROD.QZ_FLOWERS is mutating, trigger/function may not see it


Check out these links for more information on mutating table errors and getting around them:

Mutating Table Exceptions by Tim Hall
Get rid of mutating table trigger errors with the compound trigger (my blog)
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
The DBMS_UTILITY has long (since 10.2) offered three functions that are very handy when either tracing execution or logging errors:
  • FORMAT_CALL_STACK - answering the question "How did I get here?"
  • FORMAT_ERROR_STACK - answering the question "What was the error?" (or a stack of errors, depending on the situation)
  • FORMAT_ERROR_BACKTRACE - answering the question "On what line was my error raised?"
Therefore (and prior to 12.2), if you wanted to get the error information + the line number on which the error was raised, you would need to call both of the "*ERROR*" as in:

CREATE OR REPLACE PROCEDURE p3
AUTHID DEFINER
IS
BEGIN
p2;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
RAISE;
END;

Of course, in the real world, you would not display the text on the screen. You would write them to a log table via an autonomous transaction procedure, as I show in this simple error logging package.

When I run the above procedure on a version of Oracle Database through 12.1, I might see output like this:

ORA-06501: PL/SQL: program error

ORA-06512: at "STEVEN.P1", line 4
ORA-06512: at "STEVEN.PKG", line 6
ORA-06512: at "STEVEN.P2", line 5
ORA-06512: at "STEVEN.P3", line 5

In other words: the error stack shows just the error message, and the backtrace shows just the backtrace.

But as of 12.2, the error stack function has been enhanced to also include backtrace information (more details available in this My Oracle Support note). So the output run on 12.2, 18.1 or higher shows:

ORA-06501: PL/SQL: program error
ORA-06512: at "STEVEN.P1", line 4
ORA-06512: at "STEVEN.PKG", line 6
ORA-06512: at "STEVEN.P2", line 5

ORA-06512: at "STEVEN.P1", line 4
ORA-06512: at "STEVEN.PKG", line 6
ORA-06512: at "STEVEN.P2", line 5
ORA-06512: at "STEVEN.P3", line 5

Nice stuff!
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
This question found its way into my In Box yesterday:

I have a table with an object type column. I want to way to get the value of an attribute of that object type in my query. But Oracle keeps telling me "ORA-00904: invalid identifier". What am I doing wrong?

Almost certainly what you are doing wrong is forgetting to use a table alias. Yeah, it's that simple.
Don't forget the table alias.
Let's take a look.

I create an object type, use that object type as a column in a table, and insert a couple of rows:

CREATE TYPE food_t AS OBJECT (
NAME VARCHAR2 (100)
, food_group VARCHAR2 (100)
, grown_in VARCHAR2 (100)
)
/

CREATE TABLE food_table (id number primary key, my_food food_t)
/

BEGIN
INSERT INTO food_table
VALUES (1, NEW food_t ('Mutter Paneer', 'Curry', 'India'));

INSERT INTO food_table
VALUES (2, NEW food_t ('Cantaloupe', 'Fruit', 'Backyard'));

COMMIT;
END;
/

OK, let's query some data. The following output is what you see in SQLcl:

SQL> SELECT * FROM food_table;

ID
ID MY_FOOD(NAME, FOOD_GROUP, GROWN_IN)
----- ------------------------------------------
1 FOOD_T('Mutter Paneer', 'Curry', 'India')
2 FOOD_T('Cantaloupe', 'Fruit', 'Backyard')

In SQL Developer, I see:


While the output format varies, the basic idea is the same: I have asked for all columns, one of those columns is an object type, so I see the instance of that type.

What I want, though, is to select the individual attributes, such as the same. OK, here goes:

SQL> SELECT name FROM food_table;

Error starting at line : 1 in command -
SELECT name FROM food_table
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00904: "NAME": invalid identifier

What? No NAME column? Oh, that's right. It's not a column - it's an attribute of a column. OK, let's try that again:

SQL> SELECT my_food.name FROM food_table;
Error starting at line : 1 in command -
SELECT my_food.name FROM food_table
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00904: "MY_FOOD"."NAME": invalid identifier

Still no good. But, but. OK, so here's the deal: you must provide an alias to the table, prefix the object type column name with the alias, and then you are good to go. You can even reference the attribute in the WHERE clause:

SQL> SELECT ft.my_food.name FROM food_table ft;

MY_FOOD.NAME
-----------------------------------------------
Mutter Paneer
Cantaloupe

SQL> SELECT ft.my_food.name FROM food_table ft
2 WHERE ft.my_food.name LIKE 'C%';

MY_FOOD.NAME
-----------------------------------------------
Cantaloupe

For more information on working with object types in SQL and PL/SQL, check out the Object-Relational Developer's Guide.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Over the weekend of April 21, we upgraded the Oracle Dev Gym site to v3 (code name: ORANGE). Here's the v2 home page:


and now v3:


Now you see the reason for the code name. It's orange!

Here are the key changes you will find on the Dev Gym:
  • Orange theme: all that red was hurting our eyes, but the main reason to switch to orange was to make it visually clear that this site, as with AskTOM, is part of the broader Oracle Developer initiative.
  • Site search: type in a keyword, such as "FORALL" or "listagg" in the search bar on the home page, and we will find all quizzes, workouts and classes that match your criteria. You can further hone your search on the results page.
  • The tournament quizzes are now offered on the home page; no need to click on the Tournaments tab to see them. These quizzes are produced fresh each week, and often focus on the latest features in SQL, PL/SQL and Oracle Database.
  • Your recent activity on the site is available on the home page so that you can more easily continue your workout or class, or check the results of a recently-completed quiz.
  • Improved performance on the Tournament Archive (formerly Library) page, the Quizzes Taken page and more.
  • Redesigned workouts and classes page to make it easier to find and take these exercises at the gym.
Other, smaller changes you might notice:
  • The Trivadis class (which consisted of a single workout) is now a workout. All classes on the Dev Gym henceforth will consist of multiple modules. If you were in the middle of taking that class, visit the Workouts page to continue via the workout.
I hope you like the new, clean UI as much as I do. Many thanks to UI/UX guru and Application Express wunderkind Shakeeb Rahman for guidance. Who did he guide? Eli Feuerstein, the primary APEX developer for the Oracle Dev Gym, who did a fantastic job of rendering Shakeeb's design into CSS and JavaScript, SQL and PL/SQL. Thanks, Eli!

Looking ahead, we will be announcing at least two new classes on the Dev Gym over the next several months (Databases for Developers: Next Level by Chris Saxon and SQL Analytics for Developers by Connor McDonald). And in June we plan to launch a brand-new weekly Java quiz!
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
There's no shortage of people giving advice on how to improve your presentation skills and impact. I offer a short list of links at the bottom of this post. 

I though I'd take a few moments to share some tips I follow to help me make the most of my time in front of audiences.

Why listen to me? I've been doing talks on the PL/SQL language since 1992 and I am pretty sure that only 3 members of all those audiences ever fell asleep during my talk.

What are the (at most) three key takeaways?

Most attendees will forget most of what you said soon after leaving the session. Certainly almost every single technical detail will be lost. So you need to decide before you start your talk what  are the at most three things you want an attendee to remember.

Then put those in a slide and tell them right at a start.

Remind them during your talk when you are getting to one of those top 3 things.

Use the slide again at the end of your talk to drive the points home.

I also find it helpful to remind attendees that I do not expect them to remember all the details and while they are welcome to ask questions about any of the code, the most important thing to remember is why you would want to use a feature and how it will help you.

That way, you let them off the hook, give them permission to relax and not feel like they have to understand every single thing you say or show.

Who is in your audience?

My team of Developer Advocates includes two members who are tasked with helping open source developers be successful with Oracle Database. Developers from the world of FOSS (free and open source software) often have little knowledge about SQL and even less about technologies like PL/SQL, Oracle Text, Oracle Spatial, flashback, and so on.

Yet these same Developer Advocates also give the same or similar presentations at Oracle User Group events, Oracle Open World, and so on.

It has, therefore, been especially important for Dan and Blaine to ask themselves before they start a talk "Who's in my audience?" Are they mostly Oracle Database-savvy folks? In that case, they can go into a bit more detail on the SQL side of things and they might also position the non-Oracle parts of the stack different.

Conversely, if the audience is mostly composed of "next generation" developers living in the world of JavaScript or Python, the Oracle Database content needs to be positioned and explained differently.

If you are unsure of your audience, it will be quite the challenge to give a fantastic presentation (since "fantastic" is in the minds of the audience, not the presenter).

So ask yourself before you start: "Who's out there?" and if you do not know, make that one of the first things you do in your presentation: Ask the audience. Get a feel for who they are and what they are looking to get out of your talk. Then adjust accordingly.

What's so funny?

About your talk, that is.

If the answer is: "Not much." or "I don't know, never thought about it." then now is precisely the right time to think about it.

Everybody likes to learn new stuff, but what they like even better is to have some fun. So many technical talks are deadly boring precisely because the speaker thinks what they have to say is so seriously important.

That might be true, but that argues even more strongly for making sure you entertain your audience.

Now, you might not have many programmer jokes ready to go (if you want some just search for "programmer jokes"), but that's OK. I don't think you want to tell jokes, per se. Instead you want to inject light-hearted commentary into the presentation itself.

But, wait, don't most jokes end up making fun of somebody or something? Don't we have to be really careful about the "target" of our humor?

Oh my, yes.

Who are you going to make fun of?

Never anyone in the audience. Never anyone with less visibility, influence or reputation points than you.

The safest bet when it comes to telling a joke or making fun during your presentation is to make yourself the target of that joke.

Self-deprecating humor goes over well in a presentation. It comes in especially handy when you are having problems (projector bulb blows, the Internet is unavailable, none of your demos work, etc.).

Don't get flustered or upset. Instead, get people laughing, and they will be on your side and, as often as not, help you resolve the problem.

What words should I avoid using?

When you're presenting, you're the expert (even if you know only a little bit more than those in your audience). This automatically puts you on a pedestal, puts some distance between you and the attendees. This is not an all-good or all-bad thing.

It's a good thing because people came to learn from you and they are open to what you have to sya.

It can be a not-so-good thing if your expertise comes off as a put-down to everyone else. This can happen in very subtle ways, from body language to your choice of words.

I recommend that you avoid words like:

"Obviously" 

If it's so blindingly obvious, you shouldn't have to call it out. You might not need to even bother talking about it. But chances are what is obvious to you the speaker is not so obvious to attendees, and if it is not obvious to them, you just made them feel stupid.

and...."Of course"

A minor variation on "obviously," with all the same drawbacks. See above.

and...."Everyone knows"

Everyone knows that when you say "everyone knows", it is very similar to saying "Obviously" and "Of course." Everyone knows this, except for the ones who don't.

Use words that instead lift up the attendee, make them feel better about themselves, pull them into the talk, rather than pushpin them away. Invite comments and questions regularly from the audience, let them know that you, even you, had trouble getting your head around a given topic.

So: avoid put-down words....

Unless, obviously and of course, you are directing comments with these words jokingly towards yourself, as in:

"Of course, everyone knows that obviously I am an imposter."

Even More Advice

But wait, there's more! You might find these posts interesting as well:

http://jackmalcolm.com/2012/08/how-much-of-your-presentation-will-they-remember/

http://sethgodin.typepad.com/seths_blog/2018/04/how-to-give-a-five-minute-presentation-.html

https://www.linkedin.com/pulse/20140904170930-15291682-5-tips-for-giving-effective-technical-presentations/
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
This question rolled into my In Box today:
If I have a procedure that is AUTONOMOUS_TRANSACTION that does an insert and then it calls a procedure with an insert, does the second procedure need a commit, or will the procedure with the AUTONOMOUS_TRANSACTION handle the commit? If you don't know off the top of your head, don't worry, I can build a test.
First of all, if you ever find yourself writing something like "If you don't know off the top of your head, don't worry, I can build a test." then please by all means go right ahead and build yourself a test script.

By doing so, you will better understand the feature in question and remember what you learned. Plus you end up with a script you can share with the community on LiveSQL.

But I don't mind answering such questions. That way I get to better understand the feature in question, remember what I learned, share a script on LiveSQL (link at bottom of post), and also add to my blog. :-)

So here goes: the answer is NO. The "second" procedure - invoked by the first - does not have to include a COMMIT statement.

Would you like proof or more information about the autonomous transaction feature of PL/SQL? Then keep reading.

When you add this statement to the declaration section of a procedure or function...

PRAGMA AUTONOMOUS_TRANSACTION;

the following rule then applies:
Before the subprogram can be closed and control passed back to the calling block, any DML changes made within that subprogram must be committed or rolled back.
If there are any unsaved changes, the PL/SQL engine will raise the ORA-06519 exception, as shown below:

CREATE OR REPLACE FUNCTION nothing RETURN INTEGER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE employees SET last_name = 'abc';

RETURN 1;
END;
/

BEGIN
DBMS_OUTPUT.put_line (nothing);
END;
/

ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "STEVEN.NOTHING", line 10
ORA-06512: at line 2

OK, so that's the basic idea. Now let's move on the specific question. What if an autonomous transaction procedure calls another procedure, which does not include the pragma shown above but does execute a DML statement and does not commit? Will we see an ORA-06519 error? The code below shows that we will not.

CREATE TABLE me_and_my_lovelies (name VARCHAR2 (100));

BEGIN
INSERT INTO me_and_my_lovelies VALUES ('Grandpa Steven');
INSERT INTO me_and_my_lovelies VALUES ('Loey');
INSERT INTO me_and_my_lovelies VALUES ('Juna');
COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE not_auton_no_commit
AUTHID DEFINER
IS
BEGIN
UPDATE me_and_my_lovelies
SET name = UPPER (name);
END not_auton_no_commit;
/

CREATE OR REPLACE PROCEDURE auton_commit
AUTHID DEFINER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
not_auton_no_commit ();

COMMIT;
END auton_commit;
/

BEGIN
auton_commit;
END;
/

SELECT COUNT(*) low_name
FROM me_and_my_lovelies
WHERE name <> UPPER (name)
/

LOW_NAME
--------
0

No error is raised. All rows have been updated. So let's go back to the rule:
Before the subprogram can be closed and control passed back to the calling block, any DML changes made within that subprogram must be committed or rolled back.
You might be thinking: But the UPDATE statement (the "DML change") was not made "within" auton_commit. Yes and no. Yes, the UPDATE statement is not part of the text of auton_commit. But the UPDATE statement was executed within the scope of auton_commit. And that's what counts. Any code executed by auton_commit, either "directly" in its executable section or "indirectly" by invoking another subprogram, is part of the autonomous transaction.

The only point at which the rule is applied is when PL/SQL attempts to close auton_commit and return control to the outer block.

LiveSQL script containing the above code here.

More information about autonomous transactions here.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
[You might think that this was published on April 2nd, but in fact it was published on April 1st.]

PL/SQL, the database programming language from Oracle, introduced in 1991 and used by millions over the years to implement data APIs and business logic in mission critical applications from which billions of humans benefit daily, is undergoing a radical transformation in order to stay relevant for, and meta-cool to, future generations of developers.

After a careful examination of all modern programming languages and the definitive StackOverflow developer surveys, the PL/SQL development team implemented a super-secret plan (yes, that’s correct, even the Distinguished Product Manager for PL/SQL, Bryn Llewellyn, is unaware of what you are about to read. So don’t bother him about it, OK?).

I am, therefore, inordinately pleased and honored to be the first to announce the following changes for PL/SQL in Oracle Database 20c:
  1. PL/SQL will now be a case-insensitive language. Sort of.
  2. Only lower-case letters will be supported.
  3. All keywords will now be encased within squiggly brackets. 
  4. The name of the language will change to {plsql}. 
  5. SQL statements are replaced by “yo, oracle!” commands.
  6. All procedures and functions are implemented as recursive callback functions executed asynchronously across all Oracle Database instances in all parallel universes available through the Oracle Quantum Universe Cloud Service.
Let’s take a look at how {plsql} differs from PL/SQL.

Here’s “Hello World” in PL/SQL 18c:

BEGIN
DBMS_OUTPUT.PUT_LINE (‘Hello World’);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/

And now in {plsql}:

{begin}
{'Hello {dbms_output(.)put_line} World’)(call)(home)(et);
{wotn};
{end};
/

And you won’t recognize it, but you sure will be impressed by what’s happened to “select from dual”.

DECLARE
l_dummy sys.dual.dummy%TYPE;
BEGIN
SELECT dummy
INTO l_dummy
FROM sys.dual;
END;
/

And now in {plsql}:

{declare}
l_dummy {yooracle}”What’s the type of dummy in dual?”;
{begin}
{yooracle}”What’s the value of dummy in dual?”:l_dummy;
{end};
/

For really complicated SQL statements, you might want to switch to the even more flexible and powerful MLC (“machine learning cloud”) mode, demonstrated so ably below:

{begin}
{ihaveadream}”What’s the running total for all orders placed
last month by customers located within a kilometer
of their parents?”
=> {oraclevrconsole};
{end};
/

I could go on and on and on and on and on and on and on and on and on and on (Seriously, I could. The Oracle Quantum Universe Cloud Service, while not available in April 2018,  is up and running in 2020. I am using it to spray tachyons backwards and forwards through time, thereby allowing  me to sign up for as many Oracle Public Cloud trials as I want and write this mind-boggling post).

I could even show you an example of a recursive callback function executed asynchronously across all Oracle Database instances in all parallel universes.

But I won’t. I like you too much.

2018 marks the 37th year I am have working with PL/SQL I am proud of the many achievements of Oracle Database developers over those years. And today, on April 1, 2018, I am confident of a “squiggly bright” future for {plsql} over the next 37 years.
Join me for the journey!
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Thirty-six Oracle Database technologists competed on March 22nd in the 2017 PL/SQL Annual Championship at the Oracle Dev Gym. With five tough quizzes by yours truly, the competition was fierce! Congratulations first and foremost to our top-ranked players:

1st Place: li_bao of Russia
2nd Place: mentzel.iudith of Israel
3rd Place: NielsHecker of Germany

Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational.

Finally, our deepest gratitude to our reviewer, Elic, who has once again performed an invaluable service to our community.

In the table below of results for this championship, the number next to the player's name is the number of times that player has participated in a championship. Below that table, you will find another list showing the championship history of each of these players.

Rank Name Total Time % Correct Total Score
1 li_bao (4) 27 m 78% 5592
2 mentzel.iudith (4) 44 m 78% 5520
3 NielsHecker (4) 43 m 76% 5374
4 Oleksiy Ponomarenko (2) 44 m 76% 5370
5 Stelios Vlasopoulos (4) 44 m 73% 5220
6 Rimantas Adomauskas (2) 34 m 71% 5112
7 Chad Lee (4) 41 m 71% 5082
8 Maxim Borunov (4) 42 m 71% 5079
9 Karel_Prech (4) 43 m 71% 5076
10 Sartograph (1) 43 m 71% 5074
11 Henry_A (4) 21 m 69% 5014
12 siimkask (4) 26 m 69% 4995
13 msonkoly (3) 44 m 69% 4920
14 JustinCave (4) 27 m 67% 4839
15 Sandra99 (2) 37 m 67% 4801
16 Michal P. (2) 42 m 65% 4630
17 Ivan Blanarik (4) 33 m 63% 4517
18 seanm95 (4) 44 m 63% 4471
19 Joaquin_Gonzalez (4) 30 m 61% 4378
20 Chase Mei (4) 34 m 61% 4364
21 Andrey Zaytsev (4) 41 m 61% 4335
22 Jan Šerák (4) 43 m 61% 4326
23 PZOL (3) 44 m 61% 4320
24 Rytis Budreika (4) 14 m 59% 4294
25 JasonC (3) 28 m 59% 4238
26 pablomatico (1) 22 m 57% 4111
27 Hertha Rettinger (1) 24 m 55% 3950
28 Otto Palenicek (2) 29 m 55% 3934
29 mcelaya (3) 44 m 55% 3870
30 tonyC (4) 40 m 53% 3737
31 Rakesh Dadhich (4) 11 m 51% 3705
32 swesley_perth (2) 23 m 51% 3657
33 Mike Tessier (2) 31 m 51% 3626
35 whab@tele2.at (1) 24 m 47% 3353
36 patch72 (4) 16 m 45% 3234
Championship Performance HistoryAfter each name, the quarter in which he or she played, and the ranking in that championship.

Name History
li_bao 2014:36th, 2017:1st
mentzel.iudith 2014:1st, 2015:2nd, 2016:18th, 2017:2nd
NielsHecker 2014:21st, 2015:1st, 2016:15th, 2017:3rd
Oleksiy Ponomarenko 2016:10th, 2017:4th
Stelios Vlasopoulos 2014:37th, 2015:19th, 2016:24th, 2017:5th
Rimantas Adomauskas 2017:6th
Chad Lee 2014:13th, 2015:28th, 2016:19th, 2017:7th
Maxim Borunov 2015:9th, 2016:17th, 2017:8th
Karel_Prech 2014:4th, 2015:6th, 2016:11th, 2017:9th
Sartograph 2017:10th
Henry_A 2014:32nd, 2016:33rd, 2017:11th
siimkask 2014:15th, 2015:14th, 2016:13th, 2017:12th
msonkoly 2015:15th, 2017:13th
JustinCave 2015:3rd, 2017:14th
Sandra99 2015:28th, 2017:15th
Michal P. 2015:32nd, 2017:16th
Ivan Blanarik 2014:16th, 2015:16th, 2017:17th
seanm95 2014:34th, 2015:4th, 2016:9th, 2017:18th
Joaquin_Gonzalez 2014:26th, 2015:36th, 2016:12th, 2017:19th
Chase Mei 2014:25th, 2015:26th, 2016:3rd, 2017:20th
Andrey Zaytsev 2014:2nd, 2015:5th, 2016:1st, 2017:21st
Jan Šerák 2014:24th, 2015:8th, 2016:7th, 2017:22nd
PZOL 2015:35th, 2017:23rd
Rytis Budreika 2014:18th, 2015:12th, 2016:32nd, 2017:24th
JasonC 2015:42nd, 2016:26th, 2017:25th
pablomatico 2017:26th
Hertha Rettinger 2017:27th
Otto Palenicek 2016:29th, 2017:28th
mcelaya 2015:38th, 2016:34th, 2017:29th
tonyC 2014:31st, 2015:25th, 2016:25th, 2017:30th
Rakesh Dadhich 2014:29th, 2015:31st, 2016:35th, 2017:31st
swesley_perth 2016:21st, 2017:32nd
Mike Tessier 2017:33rd
HotCoder27 2017:34th
whab@tele2.at 2017:35th
patch72 2014:22nd, 2015:11th, 2017:36th
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
As anyone who has followed me over the years knows, I like the Oracle PL/SQL language. Sure, it's not the newest, coolest kid on the block (it probably never was). But then, either am I. :-) PL/SQL is, on the other hand, a delightfully straightforward, easy to learn and write language that serves its purpose well: implement APIs to data (SQL) and business logic, right inside the database.

To serve that purpose, of course, PL/SQL needs to support lots of "big ticket" functionality: super-smooth and easy native dynamic SQL, canonicalization of static SQL to minimize the need for hard-parsing, invoker rights (AUTHID CURRENT_USER) and so much more.

But I must confess: the features of PL/SQL that I love the best are the relatively "little" things that make it easy for me to be productive as I churn out the packages (and, yes, I still do write lots of PL/SQL code, most lately for the Oracle Dev Gym, an "active learning" website featuring quizzes, workouts and classes).

And that's why my favorite PL/SQL enhancement in Oracle Database 18c is the qualified expression.

That's a fancy way of saying "constructor function". Or as we say in the documentation:

Through Oracle Database 12c release 2, it was possible to supply the value of certain non-scalar datatype with an expression, by using the type constructor for an object type, nested table or varray.

So if I wanted to initialize a nested table of integers with three elements, I can do this:

DECLARE
TYPE numbers_t IS TABLE OF NUMBER;
l_numbers numbers_t := numbers_t (1, 2, 3);
BEGIN
DBMS_OUTPUT.put_line (l_numbers.COUNT);
END;

But if I was using an associative array (aka, index-by table), this was not allowed. Instead, I had to assign elements to the array, one at a time, as in:

DECLARE
TYPE numbers_t IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
l_numbers numbers_t;
BEGIN
l_numbers (1) := 100;
l_numbers (2) := 1000;
l_numbers (3) := 10000;
END;

We have had the same problem with populating values of fields in a record:

DECLARE
TYPE person_rt IS RECORD (last_name VARCHAR2(100), hair_color VARCHAR2(100));
l_person person_rt;
BEGIN
l_person.last_name := 'Feuerstein';
l_person.hair_color := 'Not Applicable';
END;

That's cumbersome, irritating and....as of Oracle Database Release 18c, you don't have bother with that sort of thing anymore.

Now, any PL/SQL value can be provided by an expression, just like a constructor provides an abstract datatype value. Specifically, this means that you

In PL/SQL, we use the terms "qualified expression" and "aggregate" rather than the SQL term "type constructor", but the functionality is the same. Qualified expressions improve program clarity and developer productivity by providing the ability to declare and define a complex value in a compact form where the value is needed.

A qualified expression combines expression elements to create values of a RECORD type or associative array type (both integer and string indexed). Qualified expressions use an explicit type indication to provide the type of the qualified item. This explicit indication is known as a typemark.

I've put together a couple of LiveSQL scripts to make it easy for you to play around with this great features:

Qualified Expressons for Records (aka, record constructors)
Qualified Expressions for Associative Arrays (aka, collection constructors)

But I will offer up the code right here as well:

Qualified Expressions for Records - Positional Notation

This example uses positional notation to associate values with fields. Notice that I can also use the qualified expression as a default value for my parameter.

DECLARE 
TYPE species_rt IS RECORD (
species_name VARCHAR2 (100),
habitat_type VARCHAR2 (100),
surviving_population INTEGER);

l_elephant species_rt := species_rt ('Elephant', 'Savannah', '10000');

PROCEDURE display_species (
species_in species_rt DEFAULT species_rt ('Not Set', 'Global', 0))
IS
BEGIN
DBMS_OUTPUT.put_line ('Species: ' || species_in.species_name);
DBMS_OUTPUT.put_line ('Habitat: ' || species_in.habitat_type);
DBMS_OUTPUT.put_line ('# Left: ' || species_in.surviving_population);
END;
BEGIN
display_species (l_elephant);

/* Use the default */
display_species ();
END;
/

Species: Elephant
Habitat: Savannah
# Left: 10000
Species: Not Set
Habitat: Global
# Left: 0

Qualified Expressions for Records - Named Notation

This example uses named notation to associate values with fields. Notice that I can also use the qualified expression as a default value for my parameter.

DECLARE 
TYPE species_rt IS RECORD (
species_name VARCHAR2 (100),
habitat_type VARCHAR2 (100),
surviving_population INTEGER);

l_elephant species_rt
:= species_rt (species_name => 'Elephant',
surviving_population => '10000',
habitat_type => 'Savannah');
BEGIN
DBMS_OUTPUT.put_line ('Species: ' || l_elephant.species_name);
END;
/

Species: Elephant

Qualified Expressions for Arrays 

With associative arrays, you always have to specify the index value (integer or string) with each expression your want to stuff into the array, as in:

DECLARE 
TYPE ints_t IS TABLE OF INTEGER
INDEX BY PLS_INTEGER;

l_ints ints_t := ints_t (1 => 55, 2 => 555, 3 => 5555);
BEGIN
FOR indx IN 1 .. l_ints.COUNT
LOOP
DBMS_OUTPUT.put_line (l_ints (indx));
END LOOP;
END;
/

55
555
5555

As should be obvious given the use of named notation, you don't have to specify index values in order - and your array doesn't have to be dense (all index values between lowest and highest defined):

DECLARE
TYPE ints_t IS TABLE OF INTEGER
INDEX BY PLS_INTEGER;

l_ints ints_t := ints_t (600 => 55, -5 => 555, 200000 => 5555);
l_index pls_integer := l_ints.first;
BEGIN
WHILE l_index IS NOT NULL
LOOP
DBMS_OUTPUT.put_line (l_index || ' => ' || l_ints (l_index));
l_index := l_ints.NEXT (l_index);
END LOOP;
END;
/

-5 => 555
600 => 55
200000 => 5555

Works for string-indexed arrays:

DECLARE 
TYPE by_string_t IS TABLE OF INTEGER
INDEX BY VARCHAR2(100);

l_stuff by_string_t := by_string_t ('Steven' => 55, 'Loey' => 555, 'Juna' => 5555);
l_index varchar2(100) := l_stuff.first;
BEGIN
DBMS_OUTPUT.put_line (l_stuff.count);

WHILE l_index IS NOT NULL
LOOP
DBMS_OUTPUT.put_line (l_index || ' => ' || l_stuff (l_index));
l_index := l_stuff.NEXT (l_index);
END LOOP;
END;
/

3
Juna => 5555
Loey => 555
Steven => 55

The index values do not have to be literals. They can be expressions!

DECLARE
TYPE by_string_t IS TABLE OF INTEGER
INDEX BY VARCHAR2 (100);

l_stuff by_string_t :=
by_string_t (UPPER ('Grandpa Steven') => 55,
'Loey'||'Juna' => 555,
SUBSTR ('Happy Family', 7) => 5555);

l_index varchar2(100) := l_stuff.first;
BEGIN
DBMS_OUTPUT.put_line (l_stuff.count);

WHILE l_index IS NOT NULL
LOOP
DBMS_OUTPUT.put_line (l_index || ' => ' || l_stuff (l_index));
l_index := l_stuff.NEXT (l_index);
END LOOP;
END;
/

3
Family => 5555
GRANDPA STEVEN => 55
LoeyJuna => 555

And with arrays of records, you can use qualified expressions with both:

DECLARE
TYPE species_rt IS RECORD (
species_name VARCHAR2 (100),
habitat_type VARCHAR2 (100),
surviving_population INTEGER
);

TYPE species_t IS TABLE OF species_rt INDEX BY PLS_INTEGER;

l_species species_t :=
species_t (
2 => species_rt ('Elephant', 'Savannah', '10000'),
1 => species_rt ('Dodos', 'Mauritius', '0'),
3 => species_rt ('Venus Flytrap', 'North Carolina', '250'));
BEGIN
FOR indx IN 1 .. l_species.COUNT
LOOP
DBMS_OUTPUT.put_line (l_species (indx).species_name);
END LOOP;
END;
/

Dodos
Elephant
Venus Flytrap

More? You Want More?

If you still haven't gotten enough of this great feature, check out Tim Hall's ORACLE-BASE article as well. It's the usual top-notch treatment.
Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview