Obsessed with Oracle PL/SQL

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 12 c Release 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 hel...

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

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 completed Confirmation of sign-up in a class Reminder to take our weekly tournament quizzes Hourly reports to site admins with any new errors in our log Weekly 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 AP...