Showing posts from 2017

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

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

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.


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…

ODC Appreciation Day: Appreciating the Community

Thanks, Tim Hall, for launching the annual OTN Appreciation Day, now renamed to ODC Appreciation Day, since the Oracle Technology Network has been recently re-shaped into Oracle Developer Community!

Many "outside" Oracle technologists (not employed by Oracle) are publishing posts today about their favorite Oracle technologies. I have seen posts about index-organized tables, PL/SQL, SQL, pipelined table functions, SQL Developer, PL/SQL collections in SQL, and much, much more.
I could write a similar blog about my all-time favorite technology, PL/SQL, the best database programming language the world has ever seen.
But you all know that about me, and hopefully about PL/SQL, too.
And it seems a little, I don't know, self-serving for an Oracle employee to toot a horn about Oracle technology (OK, not self-serving: it is, after all, my job).
But since OTN has been renamed into Oracle Developer Community, I will take advantage of Tim's initiative to celebrate:
The Oracle Deve…

Go "native" with Booleans in PL/SQL

This post was inspired by a Twitter conversation doing the Twitter version of shaking heads over the kind of code developers write with Booleans.
Keep it simple and native and intuitive: Booleans are TRUE or FALSE (ok, also maybe NULL). So you don't have to write code like "IF my_boolean = TRUE". Suppose that I needed to implement a function IS_A_FACT so that I can compile and run the following block:

BEGIN IF is_a_fact ('Steven says: The sun revolves around the earth.') THEN DBMS_OUTPUT.put_line ('Fact!'); ELSE DBMS_OUTPUT.put_line ('Opinion!'); END IF; END;
Here are four different ways of getting the job done. They all work. Which would you prefer?

1. Lay it all out there, Steven

FUNCTION is_a_fact (statement_in IN VARCHAR2) RETURN BOOLEAN AUTHID DEFINER IS l_is_a_fact BOOLEAN; BEGIN IF statement_in LIKE 'Steven says:%' THEN l_is_a_fact := TRUE; ELSE l_is_a_fact := FALSE; END IF; …

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…

Surgical strike on spaghetti code with CONTINUE statement

It can be incredibly painful to make changes to an existing program that has the markings of spaghetti code. And isn't it positively terrifying to make those changes when you don't have a regression test for the program that you can run afterward to ensure that no bugs were inadvertently introduced?

Yet that is what we are often called upon to do.

When faced with this situation, the smart thing to do is to make the smallest, most isolated change possible, thereby minimizing the ripple effect.

Suppose the code you have to modify looks like this:

PROCEDURE someone_elses_mess /* || Author: Long-Gone Consultant || Maintained by: Terrified Employee */ IS BEGIN ... lots and lots of convoluted code FOR index IN 1 .. my_collection.COUNT LOOP ... hard-to-understand logic here ... more of the same here END LOOP; END;
and you need to add some code between "hard-to-understand logic here" and "more of the same here." If a certain co…

About the Date Literal in Oracle Database

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 '2017-08-26'
2. Timestamp liter…

Do you REALLY need that SQL to be dynamic?

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. Sometimes the misuse of dynamic…

Referencing package-level variables inside the package body

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):

Notice the line in blue and bold. I reference the package level variable, qualif…