About a year ago I wrote the post Subtleties – Part 1 (SQL and PL/SQL). I wrote there:
Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement.
One example is the COLLECT aggregate function with the DISTINCT option.
And I showed an example that was executed in 184.108.40.206 and in (more...)
A good while ago Chris Saxon, member of the AskTom answer team, asked on twitter which datatype you use when defining tables when you need a Boolean-representation. As you might know there is no Boolean datatype in SQL.
A lot of discussion followed which I'm not going to repeat.
Usually I use a VARCHAR2(1) with a check constraint for Y and N, but for a recent requirement I decided to use a NUMBER instead.
I've been using Sublime Text as my main editor for PL/SQL development for many years, but I'm now in the process of switching to Visual Studio Code
Some good reasons to use VS Code:
- Multi-platform (Windows, OS X, Linux)
- Free, open source
- Lightweight, fast
- Large ecosystem of extensions
- Built-in Git support
- Can be adapted to PL/SQL coding via a plsql language extension (syntax highlighting, go to/peek definition, go to symbol) and PL/SQL compilation (more...)
I arrived on evening before just in time for the life cooking event and later at the bar to meet lot of the people I already know and some new too.
The morning begins with (more...)
In my previous blog post I introduced the new Explicit Semantic Analysis (ESA) algorithm and gave an example of how you can build an ESA model and use it. Check out this link for that blog post.
In this blog post I will show you how you can manually create an ESA model. The reason that I'm showing you this way is that the workflow (in ODMr and it's scheduler) may not be for everyone. (more...)
It’s always a challenge when you want to build your own Oracle SQL Tools. I was asked how you could synchronize multiple cursors into a single source. The answer is quite simple, you write an Oracle object type to represent a record structure, an Oracle list of the record structure, and a stored function to return the list of the record structure.
For this example, you create the following
table_struct object type and a
Suppose that every time we add records into the T1 table we have to do some additional stuff.
One option to implement this is by using an AFTER INSERT trigger that will perform this additional stuff, but I really dislike this option (mainly because the code becomes hidden in a way, and there may be a negative impact on performance).
I prefer writing a procedure that inserts the records into T1 and performs this additional (more...)
We have gone through a merger at our company where we are trying to merge the databases. The problem now is that we have duplicate records in our tables. We can of course go through all the records by hand and check if they exist twice. Another option is to build an application to do this. But using the Oracle Database there must be a better way to do this. Any ideas?
Here’s my contribution to the OTN Appreciation Day.
Edition-Based Redefinition (EBR) is a really great feature, or more accurately a set of features, that was introduced in Oracle 11.2 and allows for online application upgrades using hot rollover. If you know me then you know that I talk (ok, preach) about it a lot, and from a long and successful experience.
It is impossible to learn EBR in 5 minutes, but perhaps you’ll understand (more...)
We are posting these blog posts today as part of the OTN Appreciation Day, a celebration for the Oracle Technology Network as suggested by Tim Hall, inspired by Debra Lilley.
The mission was not too hard: write about your favorite bit of Oracle Technology.
As a developer and a core-tech DBA and APEX enthusiast… the choice was easy! PL/SQL (apart from how it is pronounced or even written ;-)!
Not just because (more...)
We have bought an application that runs on an Oracle database. There are triggers defined on the tables, but we want to add our own code to these triggers, but we don’t have access to the source code. What is the approach we should follow to accomplish this?
There are two types of DML triggers. Statement level triggers and row level triggers. Then for these two types there are (more...)
Recently I heard someone talk about overloading in Java. What is it, is it possible in PL/SQL and if so, how would I use it?
Overloading is a technique of creating multiple programs with the same name that can be called with different sets of parameters. It is definitely possible to apply this technique in PL/SQL, in fact, Oracle does this a lot of times in their own built-in (more...)
Last year I did a presentation on table functions at KScope. One of the questions I got was: ‘If pipelined table functions provide their results faster, why would you want to use a normal table function?’ I couldn’t come up with the answer then, maybe you can help?
Erik van Roon
Let’s start with explaining a bit what table functions are. Table Functions are functions that return a collection of (more...)
Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.
To try things out, let's just start with a simple table with (more...)
PL/SQL offers the entire suite of structured programming mechanisms, such as condition checking, loops, and subroutines, as shown in the following figure. (read more
Do you need to accept payments for goods and services via your (APEX) application and would you prefer to handle the payments in the database via PL/SQL? Then this blog post is for you... :-)
Almost a decade ago (in 2007), Oracle released a whitepaper on Integrating Application Express with PayPal Payments Pro
which used PayPal's Name Value Pair (NVP) API.
In the years since then, PayPal has made available a new API which is (more...)
In this post, you will find an example of how to build and deploy a basic artificial neural network
scoring engine using PL/SQL
for recognizing handwritten digits. This post is intended for learning
purposes, in particular for Oracle practitioners who want a hands-on introduction to neural networks.Introduction
Machine learning and neural networks
in particular, are currently hot topics in data processing. Many tools
and platform are now easily available to work and experiment (more...)
This is a follow-up to an old post I did about how to backup Oracle database schemas to Amazon S3 using PL/SQL
In short, the packages provided in the Alexandria Utility Library for PL/SQL
allow you to set up a schema-level backup of files from your database to Amazon's Simple Storage Service (S3)
At the end of that article I mentioned that you should use AWS Identity and Access Management (IAM)
to create a separate (more...)