Skip to main content

Posts

An Application Alerting Utility

A few weeks ago, Mike Hichwa asked me to come up with a package that would implement a simple alerting utility: specify a triggering event (based on a query or a PL/SQL expression) and then take the specified actions when it is triggered.

Seeing as he is my boss, I said "OK" and got to work (my favorite kind of work: writing some PL/SQL code). We did our usual bit to expand scope, then did our usual bit to agree that this is enough for a first pass. And then Mike said: "Go ahead and write a blog post about it, share it with the community."

So here goes.

The basic idea is we start up a job using DBMS_SCHEDULER that runs every N minutes. When it runs, it checks to see if any alerts need to be triggered. If so, it then takes one or more actions associated with that alert.

Let's start with our tables.
Utility configuration How often should the job wake up? Should it disable checking for alerts? What is the name of the "callout" for sending emails? And if…
Recent posts

Results are in for the PL/SQL Challenge Championship for 2019!

Another Oracle Dev Gym championship completed! Find below the rankings for the PL/SQL Challenge Championship for 2019. The number next to the player's name is the number of times that player has participated in a championship. Below the table of results for this championship, you will find another list showing the championship history of each of these players.

Congratulations first and foremost to our top-ranked players:

1st Place: MarkusId

2nd Place: mentzel.iudith

3rd Place: NielsHecker


Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, we will make the championship as a whole available as a workout, so you can take it just like these players did.

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

RankNameTotal Time% CorrectTotal Score1MarkusId (1)28 m83%41852ment…

Rankings for Logic Annual Championship for 2019

You will find below the rankings for the Logic Annual Championship for 2019, held in February 2020 on the Oracle Dev Gym. The number next to the player's name is the number of times that player has participated in a championship. Below the table of results for this championship, you will find another list showing the championship history of each of these players.

Congratulations first and foremost to our top-ranked players:

1st Place: Stelios Vlasopoulos

2nd Place: Sartograph

3rd Place: NielsHecker


Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, you can take the quizzes through the Practice feature. We will also make the championship as a whole available as a Workout, so you can take it just like these players did.

Finally, many thanks to Eli Feuerstein, Logic Quizmaster who provided a very challenging set of quizzes, and our deepest…

PL/SQL Office Hours: Virtual Private Database in the Wild

Virtual Private Database (VPD), also referred to as row-level security or RLS, is a feature built into the Oracle Database that allows you to set up security policies on tables that restrict which rows a user can see or change based on the policy logic.

One of the nicest things about VPD is that this logic (and the fact that a filter is being applied) is completely invisible to the user. They just see the data relevant to them and none the wiser about all that other data in the data.

Here's a simple example to drive the point home: suppose I am building a health care application and it contains a patients table. The security policy is straightforward:

A patient can only see their own information.
A doctor can see only the information about their own patients.
A clinic administrator can see information only about the patients in their clinic.

In all three cases, the user would sign on to the application and execute the same query:

SELECT * FROM patients

and only their rows would ap…

PL/SQL Puzzle: Getting the "right" error message to appear

I posted the following puzzle on Twitter:
What change(s) can you make to this code so that "ORA-00001: unique constraint" appears on the screen after execution? Try it yourself before reading the rest of the post!


White space


so you do not immediately



see my answer. 



:-)


Here are the answers from the TwitterSphere:

Change line 5's assignment to dbms_sql.number_table(1=>1,2=>1)

In other words, try to insert the same value twice. Since there is a unique index on the column, that will cause ORA-00001 to be raised.

So that will do it, right?

Wrong. Hans and Dirk both point out why that is not enough, and offer the second part of the solution:

The value deposited in the error_code field of the SQL%BULK_EXCEPTIONS array is unsigned. In other words, 1 rather than -1 is stored. Unfortunately, the SQLERRM function assumes that the error code you pass it will be signed (negatively). So you must multiply the value in the pseudo-collection by -1. Then SQLERRM will return the right …

Oracle Dev Gym PL/SQL Championship for 2019 Players

Throughout 2019, over 1200 Oracle Database developers participated in the Oracle Dev Gym PL/SQL Challenge weekly tournament. The top 50 ranked players shown below will compete for top honors in a championship on February 18, 2020.

The number in parentheses after their names are the number of championships in which they have already participated. As you can see, there are some very dedicated players here!

Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!

NameRankStelios Vlasopoulos (16)1mentzel.iudith (19)2NielsHecker (20)3Chad Lee (16)4Peterman (6)5siimkask (19)6_tiki_4_ (12)7Chase Mei (5)8Ludovic Szewczyk (4)9li_bao (7)10MarkusId (0)11Andrey Zaytsev (8)12Michal P. (3)13Arjun Barath (0)14Ivan Blanarik (13)15Maxim Borunov (6)16lmikhailov (0)17mcelaya (4)18tonyC (5)19patch72 (6)20Karel_Prech (9)21Sandra99 (3)22Oleksiy Ponomarenko (4)23Mike Tessier (3)24Vyacheslav Stepanov (18)25Jan Šerák (5)26seanm95 (6)27msonkoly (4)28Rakesh Dadhic…

PL/SQL Office Hours: DB Setup and Teardown for Automated Testing

On January 14, 2020 at 9 AM Eastern, I am very pleased to hold a PL/SQL Office Hours session on one of the biggest challenges faced by developers setting up automated tests for database code: setup and teardown.

No application will ever have zero bugs, but you sure want to keep them to a minimum. The best way to do this is to implement automated regression tests of your code, but "best" as usual does not equate to "easiest." Building and managing tests can be a big challenge, so in this Office Hours session, we will hear from developers who are doing just that. Learn from your peers about the obstacles they faced and how they overcame them. Bring your own stories and your questions, and let's all work together on improving our code quality!

For this session, we have two presenters: Deepti Bandari and Jasmin Fluri.

Deepti Bandari is a senior software engineer at Fidelity Investments since 2013. Her focus areas include database design and development, test automa…