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