Obsessed with Oracle PL/SQL

I offered up a past blog post on Twitter today: And I saw this reply: I am unfamiliar with this magical expression: DATE '2016-10-31'. How is this being resolved when it doesn't match the NLS date format? Which reminded me than many developers are not aware of the date Literal feature of both SQL andPL/SQL. So I figured I should give you all a bit more detail on the topic. So you are likely familiar with string literals, like 'ABC' and q'[don't need two single quotes]' .  And who isn't aware of using pretty much all the time number literals, like 123 and 2e7? Relatively few developers know that you can have a date literal, too (and timestamp literal and timestamp with local timezone literal and interval literals). Literals of these types generally have the form: TYPE string-literal where TYPE is the name of the  datatype  and string-literal is, well, you get the idea. :-) Here are some examples: 1. Date literal DATE '20...

Dynamic SQL means a SQL statement that is constructed, parsed and executed "dynamically" at run time (vs. "statically" at compile time). It's very easy to write static SQL in PL/SQL program units (one of the great joys of working with this database programming language). It's also quite easy to implement dynamic SQL requirements in PL/SQL. But that doesn't mean you should . The bottom line regarding dynamic SQL is: Construct and execute SQL at runtime only when you have to. There are several good reasons to avoid unnecessary dynamic SQL: Security : dynamic SQL opens up the door to SQL injection, which can lead to data corruption and the leaking of sensitive data. Performance : while the overhead of executing dynamic SQL has gone way down over the years, it is certainly still faster to use static SQL. Maintainability : the code you write to support dynamic SQL is more - literally more code - and harder to understand and maintain. Sometime...

I received this question yesterday: Is there a shortcut for referencing package variables in the package body? In Java, as an example, you can use the "this" keyword as a reference to the current object. This came about as I needed to create a copy of a package for debug purposes and realized I had to rename all the references to the package name within the package body. Before I answer, let's look at an example of what Tony is talking about. I create a package specification and then a body with package-level variable (declared outside any subprogram of the package): CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER IS PROCEDURE proc (n_in IN NUMBER); END; / CREATE OR REPLACE PACKAGE BODY pkg IS smallest NUMBER; PROCEDURE proc (n_in IN NUMBER) IS BEGIN IF n_in < pkg.smallest THEN DBMS_OUTPUT.put_line ('too small'); END IF; END; END; / Notice the line in blue and bold . I reference the package level variab...

Have you ever run into the following error when trying to create a materialized view? ORA-22818: subquery expressions not allowed here Yes, it is true: you cannot have a scalar subquery in the SQL statement used to create your materialized view. Here's an example of what won't work (note: I am not claiming this query makes any sense): CREATE MATERIALIZED VIEW hr_demo_mv AS SELECT employee_id, (SELECT MAX (hire_date) FROM employees ce) maxhd FROM employees t / ORA-22818: subquery expressions not allowed here 22818. 00000 - "subquery expressions not allowed here" *Cause: An attempt was made to use a subquery expression where these are not supported. *Action: Rewrite the statement without the subquery expression. Rewrite my query without the subquery expression? But I just spent an hour putting it all together. Works great. Gives me exactly the results I want and need. Rewrite it? ARGH. Calm yourself. While ...

Oracle Database 12c offers several enhancements to improve security in your PL/SQL program units. These features include: Avoid privilege escalation: Use the INHERIT [ANY] PRIVILEGES privilege to make it impossible for a lower-privileged user to take advantage of a higher-privileged user via an invoker rights unit. I covered this topic here . Code-based access control: fine-tune access to database objects inside program units by  granting roles to program units ( doc ), rather than - or in addition to - roles granted to schemas. That's the topic for this post. Note: Oracle Magazine also offers this content (both blog posts) in a single article here . Securing your database – and properly restricting access to the data and data structures within your database – ranks at the very top of the "most important things to do" list when building applications. The best way to avoid unintended access or actions is to apply the "least privilege" principle: give a...

Software is constantly evolving: bugs fixed, new features added, and better ways to do things discovered and implemented. A great example of this dynamic from PL/SQL itself is the UTL_CALL_STACK package. This package was first introduced in Oracle Database 12c Release 1, and it improves upon the functionality already provided by the following functions in the DMBS_UTILITY package: FORMAT_CALL_STACK, FORMAT_ERROR_STACK, and FORMAT_ERROR_BACKTRACE. The same thing happens in PL/SQL code that is developed by customers. The now-outdated subprograms (or other elements) of one's API cannot be removed immediately; that would break existing code. But everyone would like to make sure that any new code uses the new API. The new DEPRECATE pragma (compiler directive) in Oracle Database 12.2 will help you accomplish this transition in a smooth, error-free fashion. It provides a formal way to communicate information about deprecated elements [my change ok? SF YES] with a power that ordi...

You are writing PL/SQL code to provide secure, high performance access to your data and implement business rules. [reference: Why Use PL/SQL? ] Right? Good. And you use collections (associative arrays, nested tables, arrays) because they offer all sorts of great functionality. [reference: Collections in PL/SQL YouTube playlist ] Right? Good. So here's a quick reminder about COUNT, one of many methods available for collections (others include DELETE, FIRST, LAST, NEXT, PRIOR, TRIM, EXTEND): It works pretty much like COUNT in SQL. If the collection is empty, COUNT returns 0, not NULL. If you try to "read" an element at an undefined index value, Oracle Database raises NO_DATA_FOUND. Just like a SELECT INTO that identifies no rows. If you check to see if a collection is empty with a call to COUNT, it doesn't raise NO_DATA_FOUND. To verify what I've said, and to have a bit of fun while doing it, you can take a quiz on this topic at the Oracle Dev Gy...