Posts

Showing posts from 2018

Code You Should Never See in PL/SQL

Image
If you ever run across any of the following, apply the suggested cleanup, or contact the owner of the code, or run for the hills.

And I am pretty sure many of my readers will have suggestions for other code that should never appear in your PL/SQL programs. Let me know in the comments and I will add them to the post (giving me all the credit of course - no, just joking! YOUR NAME will be bright lights. :-) ).


Set default value to NULL

Whenever you declare a variable it is assigned a default value of NULL. So you should not explicitly provide NULL as a default value. It won't do any harm, but it will tell others who read your code that your understanding of PL/SQL is, shall we say, incomplete.

Bad Code

DECLARE l_number NUMBER := NULL; BEGIN ... END;
Cleaned Up

Just remove the assignment.

DECLARE l_number NUMBER; BEGIN ... END;
Select from DUAL for....just about anything

A long, long time ago, before PL/SQL was all grown up, it didn't have native implementations for some…

The PL/SQL Collection Resource Center

Image
Collections (Oracle PL/SQL's data structure to implement arrays, lists, stacks, queues, etc.) are not only handy in and of themselves, but are used for many key features of this powerful database programming language, including:
High performance querying with BULK COLLECTSuper-fast, bulk non-query DML operations with FORALLTable functions (functions that can be treated like a table in a SELECT's FROM clause) PL/SQL offers three types of collections - associative arrays, nested tables, and varrays - each with their own characteristics and ideal use cases.
If you are not already using collections on a regular basis in PL/SQL, you are really missing out.
Use this article as starting point for accessing a number of useful resources for getting up to speed on collections, and putting them to use in your programs.
Documentation
The PL/SQL User Guide offers detailed coverage of collection features here. It starts by reviewing the differences between collections types.


Articles

ORACLE-BAS…

The PL/Scope Resource Center

Image
PL/Scope is a compiler-driven tool that collects PL/SQL and SQL identifiers as well as SQL statements usage in PL/SQL source code.  PL/Scope collects PL/SQL identifiers, SQL identifiers, and SQL statements metadata at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (DECLARATION, DEFINITION, REFERENCE, CALL, ASSIGNMENT) and the location of each usage in the source code.

Starting with Oracle Database 12cRelease 2 (12.2), PL/Scope has been enhanced to report on the occurrences of static SQL, and dynamic SQL call sites in PL/SQL units. The call site of the native dynamic SQL (EXECUTE IMMEDIATE, OPEN CURSOR FOR) and DBMS_SQL calls are collected. Dynamic SQL statements are generated at execution time, so only the call sites can be collected at compilation time. The collected data in the new DBA_STATEMENTS view can be queried along with the other data dictionary views to help answer …

Class on PL/SQL Table Functions at the Oracle Dev Gym

Image
http://bit.ly/dg-tf
A table function is a function that can act like a table inside a SELECT statement. The function returns a collection, and the SQL engine converts that collection into rows and columns that can be manipulated with standard SQL operations (joins, unions, where clauses, etc.).

Far and away the most popular post on this blog is an introduction to a series of articles on table functions:














Given that level of interest in a very interesting feature of PL/SQL, I thought it would be a good thing to give you even more resources to learn about table functions.

So I put together a FREE class at the Oracle Dev Gym on PL/SQL table functions. It consists of four modules and gives you a solid grounding in table function fundamentals:



Each modules consists of a video that covers the basics, followed by a LiveSQL tutorial that dives into more of the details, and gives you an opportunity to run and play with the code. We then finish up the module with quizzes to reinforce and deepen …

How to avoid spamming users from your applications

Image
Does your application send out emails? Lots of emails?

Did you ever get that feeling like someone punched you in the stomach when you realize that you mistakenly sent out hundreds or thousands of emails to your users when you didn't mean to?

I have. It's a terrible feeling. And these days, in the age of GDPR, there can be real consequences for invading the privacy of your users. This post explores how to make sure that, at least when you are developing and testing your code, you do not inadvertently spam your users.

The Oracle Dev Gym sends out lots of different kinds of emails to those players who have opted-in for them, such as:

Results of the quiz you just completedConfirmation of sign-up in a classReminder to take our weekly tournament quizzesHourly reports to site admins with any new errors in our logWeekly activity summaries to quizmasters The Dev Gym is an Oracle Application Express app, so we are able to happily and easily take advantage of the APEX_MAIL package, and it…

How many times does my table function execute?

A left correlation join occurs when you pass as an argument to your table function a column value from a table or view referenced to the left in the table clause. This technique is used with XMLTABLE and JSON_TABLE built-in functions, but also applies to your own table functions.

Here's the thing to remember:
The table function will be called for each row in the table/view that is providing the column to the function.  Clearly, this could cause some performance issues, so be sure that is what you want and need to do.

The following code demonstrates this behavior, for both pipelined and non-pipelined functions.

CREATE TABLE things ( thing_id NUMBER, thing_name VARCHAR2 (100) ) / BEGIN INSERT INTO things VALUES (1, 'Thing 1'); INSERT INTO things VALUES (2, 'Thing 2'); COMMIT; END; / CREATE OR REPLACE TYPE numbers_t IS TABLE OF NUMBER / CREATE OR REPLACE FUNCTION more_numbers (id_in IN NUMBER) RETURN numbers_t IS l_numbers numbers_t := …

The SmartDB Resource Center

Image
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 performa…

Mutating table errors and multi-row inserts

The Oracle Dev GymPL/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, …

Error stack function now (12.2) includes backtrace information!

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 pro…

How do I get the attribute of my object type in SQL?

Image
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&#…

Oracle Dev Gym gets a facelift - and more!

Image
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…

Tips for a great presentation

Image
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 remi…

Nested blocks, autonomous transactions and "Where do I commit?"

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 ans…

A new name - and amazing new future - for PL/SQL

[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:
PL/SQL will now be a case-insensitive language. Sort of.Only lower-case…

Rankings for 2017 PL/SQL Championship on the Oracle Dev Gym

Image
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.

RankNameTotal Time% CorrectTotal Score1li_bao (4)27 m78%55922mentzel.iudith (4)44 m78%55203NielsHecker (4)43 m76%53744Oleks…