Posts

PL/SQL 101: Nulls in PL/SQL

Image
The Oracle Database supports a concept of a null value, which means, essentially, that it has no value. The idea of having nulls in a relational database is controversial, but Oracle Database supports them and you need to know how they can impact your work in PL/SQL.

First, and most important, remember that:
Null is never equal to anything else, including null. And certainly 0.
Null is never not equal to anything else, including null.DECLARE var INTEGER; BEGIN IF var = NULL THEN ... IF NULL = NULL THEN ... IF var <> NULL THEN ... IF NULL != NULL THEN ... END;
you can rest assured that the code represented by "..." will never be executed.

Note: NULL in the above code is a literal value with a non-value of null.

The same holds true for where clause predicates in a SQL statement. The following queries will never return any rows, regardless of the contents of the employees table.

SELECT * FROM employees WHERE employee_id = NULL / SELECT * FROM employe…

JSON in Oracle Database: resources to get you started

Image
I am (finally) diving in to explore and then write about using JSON in Oracle Database 12c - with SQL (Releases 1 and 2) and - more to the point for me - with PL/SQL (based on enhancements in Release 2).

Expect to see posts on this blog, articles in Oracle Magazine, quizzes and workouts at the Oracle Dev Gym, and more, in 2018.

To that end, I have been collecting links to useful resources on this topic, and I realized that you might benefit from that list just as much as I will. So why not share it now? :-)

Before exploring Oracle-specific resources, visit json.org to learn more about JSON itself.

Oracle Documentation

Let's start with the official source of truth: our doc set.

First and foremost, we have the Database JSON Developer's Guide, which covers how to store, generate, view, manipulate, manage, search, and query JSON.

And here are some specific links you might find handy:

Basic SQL/JSON Path Expression Syntax

The basic syntax of a SQL/JSON path expression is presented. …

PL/SQL 101: Declaring variables and constants

PL/SQL is, in almost all ways, a straightforward and deceptively simple programming language. The "deception" lies in how simplicity can sometimes mask capability. It is easy to learn the basics of PL/SQL, and you can become productive very quickly.  And then you also quickly see how powerful and capable is PL/SQL.

So I offer another post on PL/SQL fundamentals, this one offering key points to remember when declaring constants and variables.

Some General Points

1. You can only have declarations in the declaration section, which is found between the IS | AS | DECLARE keyword and the BEGIN keyword (which kicks off the executable section) or END keyword if declaring elements at the package level.

/* Anonymous and nested blocks*/ DECLARE ...declarations... BEGIN /* Procedures and functions */ PROCEDURE my_proc (...) IS | AS ...declarations... BEGIN /* Package specification and body */ PACKAGE my_pkg IS | AS ...declarations... END;
So to be clear: there is an explicit &…

"Write once, run anywhere" - PL/SQL comes to yet another database!

Back in the late 80s, some big brains at Oracle realized that an operating system independent language was needed to build applications on top of Oracle Database (and our SQL implementation). Their rapidly growing customer base was writing applications in C (making calls to SQL via the OCI layer), and that was problematic. Code needed to be ported, re-tested, etc. for variations of the C compiler on different systems. And C wasn't very easy to write.

They dreamed of programs, running inside the database itself, which could be moved from operating system to operating system, and not require any changes.
"Write once, run  anywhere (there's an Oracle Database)." Thus was PL/SQL born, as a sort of step-child of Ada (the syntax of PL/SQL was taken almost directly from Ada specs, to avoid having to invent an entirely new language).

It's such a great language that:
It offers the best performance for executing SQL in Oracle Database.It provides a simple yet powerful way t…

PL/SQL 101: Writing conditional logic in PL/SQL

Image
PL/SQL offers a number of options when it comes to writing conditional logic, as in variations on "If this then that". This post reviews them, and provides examples.

You can choose from the following:
IF statement - IF, IF-THEN, IF-ELSE, IF-ELSIF-ELSECASE statement - simple and searched CASECASE expression - the expression "version" of the statement (and one of my favorite elements of PL/SQL)IF Statement
It's hard to imagine a programming language without a version of IF, and PL/SQL is no exception. 
The IF statement either runs or skips a sequence of one or more statements, depending on a condition. The IF statement can take one of three forms, but they all have this in common: they start with "IF" and then end with "END IF;".
IF THEN

The simplest IF statement: if the boolean expression evaluates to TRUE then execute the statements between THEN and END IF. Example:

BEGIN IF l_hire_date < SYSDATE THEN send_survey_request (l_empl…

Weak Ref Cursor Types: Do I ever need to declare my own? No!

This question just came in via Twitter DM, so I thought it could do with a blog answer.

Ref cursor types are the datatypes from which we declare cursor variables. A cursor variable is, well, just that: a variable pointing back to a cursor/result set.

Some really nice aspects of cursor variables:

you can associate a query with a cursor variable at runtime (useful with both static and dynamic SQL); you can pass the cursor variable as a parameter or function RETURN value. Specifically: you can pass a cursor variable back to a host language like Java for consumption. Check out the cursor variable documentation
Explore this LiveSQL script on cursors, including multiple examples of cursor variables.
OK, to get to it, then:
Before you can declare a cursor variable, you need to have a ref cursor type defined. There are two, ahem, types of types: strong and weak. 
With a strong type, you include a RETURN clause that specifies the number and datatypes of expressions returned by the query assoc…

Oracle Dev Gym 2.2 Release: faster, simpler and fun, new workouts!

We upgraded the Oracle Dev Gym site this past weekend to 2.2. Why should you care? Because the Dev Gym offers a great active learning complement to reading doc and blogs, and watching videos. We help you deepen and broaden your expertise through exercise: taking quizzes that reinforce newly gained knowledge.

Quizzes

At the heart of the Dev Gym is a library of over 2,400 quizzes on SQL, PL/SQL, Oracle Application Express, Database Design and Logic. Almost all these quizzes were first played as part of our daily and then weekly tournaments - which means they've been reviewed by experts and taken by hundreds and in some cases thousands of developers. They are usually code-based quizzes that strengthen you ability to read and understand code (rather than "try it and see" by running the code).

The Dev Gym home page offers a selection of favorite quizzes hand-picked each week by our quizmasters from this library. But that's just the tip of the iceberg. Looking to strengthen…