Obsessed with Oracle PL/SQL

The Oracle catalog contains hundreds of views (generally referred to as "data dictionary views") that provide information about the objects stored in the database (tables, views, PL/SQL program units, etc.). Several of these views are extremely helpful to PL/SQL developers in analyzing and managing their code. Here are a few examples: ALL_OBJECTS - information about all database objects which can be accessed by the current user. ALL_ARGUMENTS - information about every argument of every packaged subprogram and schema-level program unit for which the current user has EXECUTE authority. ALL_IDENTIFIERS - information about identifiers in program units, gathered by the Oracle Database 11g PL/Scope feature. In this blog post, I explore a change in behavior for ALL_ARGUMENTS (and its USER* and DBA* variants) as of Oracle Database 18c. ALL_ARGUMENTS lists the arguments of the procedures and functions that are accessible to the current user. USER_ARGUMENTS, as you might kn...

The first and most important criteria of high quality code is that it meets user requirements (it's "correct"). The second most important criteria is that it fast enough to meet user needs. It doesn't matter that you might be able to make it go faster . The third most important criteria of excellent code is that it is maintainable. Why, you might be wondering, is maintainabiliity so important? Most of the code we write, especially code that lives in the database, sticks around for a long time. Well past the point when you were familiar with the code you wrote. And likely past the time when you are still working on that same code or project. So the overall cost of development of that code is not just the initial build expense, but also must include the cost of maintaining that code. All too often we are so absorbed in meeting deadlines that we sacrifice clarity and readability to "get the job done." And then we - or others - pay the price later. C...

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