PL/SQL Formatting : More pearls of wisdom from Bryn

glasses-272399_1280-smallAnother topic of conversation that came out of Bryn‘s session at Oracle Midlands related to PL/SQL code formatting. I’m not sure I agree 100% with his opinion, but it’s certainly making me think about my current stance.

When I said I was going to blog about a couple of the points he raised, Bryn emailed me some stuff, which I’m going to use here. So this is Bryn’s content, with some of my opinions thrown in. I (more...)

Preventing PL/SQL name clashes. You learn something new every day!

glasses-272399_1280-smallI mentioned in yesterday’s Oracle Midlands post, Bryn had an example of some syntax I had not seen in 20+ years of PL/SQL development.

You tend to name PL/SQL parameters and variables in such a way as to prevent name clashes with table columns. Let’s cause an obvious name clash…

The table DUAL has a column called DUMMY with a single row with the value ‘X’.

SQL> select * from dual;


1 row  (more...)

Oracle Week 2015: Oracle Advanced PL/SQL Presentation

As I said in my earlier post about Oracle Week 2015, this is one of the biggest database related educational (non-sale oriented) convention in Israel. None of the session is designed for selling any products (but consultant use it to showcase their abilities hoping to sell some professional services :)). Yesterday was my second daylong session in this convention this year . This time we talked about advanced features in PL/SQL and some new features of (more...)

Pragma UDF – Some Current Limitations

There are currently some limitations to when pragma UDF will speed up your calls to PL/SQL functions.

In my post introducing the new pragma UDF feature of Oracle 12c I explained how it can be used to reduce the impact of context switching when you call a PL/SQL function from SQL.

In my example I showed how running a SQL-only SELECT statement that formatted a name for display over 100,000 records took 0.03 seconds (more...)

Pragma UDF – Speeding Up your PL/SQL Functions Called From SQL

A new feature for PL/SQL was introduced in V12, pragma UDF. UDF stands for User Defined Functions. It can speed up any SQL you have that uses PL/SQL functions you created yourself.

We can create our own functions in PL/SQL and they can be called from both PL/SQL and SQL. This has been possible since V7.3 and is used extensively by some sites to extend the capabilities of the database and encapsulate business logic.


Updating Identity Columns

During my presentation "Oracle 12c for Developers" at the Sloveninan and Croatian User Groups I got the same question twice about Indentity Columns:

Is it possible to update an Identity Column?
During the presentation I show how it is not possible to insert a value for a "Generated Always Identity" column.
Let's take a look at an example:

SQL> create table t
2 (id number generated as identity
3 ,name varchar2(35)
4 );

Table created.


SQL and PL/SQL icons and stickers

Over the past couple of weeks I've been preparing my slides and presentations for Oracle Open World (2015).

One thing that occurred to me was that there was no icon or image to represent Oracle SQL and PL/SQL. I needed something that I could include in my presentations to represent these.

After a bit of Tweeting it turns out that there is no (official) icons or images for Oracle SQL and Oracle PL/SQL.

So I (more...)

Oracle-Package für HTTP/HTTPS

Sayan Malakshinov hat zuletzt zwei Artikel veröffentlicht, in denen er sein GitHub Package XT_HTTP vorstellt, mit dessen Hilfe man auf HTTP- bzw. HTTPS-Seiten zugreifen kann, ohne Zertifikate importieren zu müssen. Die aktuelle Version enthält einen ergänzenden Timeout-Parameter, eine Suchoption auf Basis von regulären Ausdrücken (PCRE) und den Support für plsqldoc (ein Hilfsmittel zur automatischen Generierung von Dokumentation; ähnlich wie javadoc). Sicher sehr nützlich für entsprechende Fragestellungen.

Der Artikel hat noch den zusätzlichen Pluspunkt, dass er (more...)

Oracle package for HTTPS/HTTP[version 0.2]

A couple days ago i created simple package for HTTPS/HTTP, but I’ve decided now to improve it:

  1. Timeout parameter – it would be better to control connection time;
  2. Simple page parsing with PCRE regular expressions – to speed up and simplify page parsing, because if you want to get big number matched expressions from CLOB with regexp_xxx oracle functions, you have to call these functions many times with different [occurance] parameters, passing/accessing to the (more...)

Very simple oracle package for HTTPS and HTTP

I don’t like to import certificates, so i cannot use httpuritype for HTTPS pages and I decided to create package which will work with https as http.
It was pretty easy with java stored procedures :)

java source: xt_http.jsp
create or replace and compile java source named xt_http as
package org.orasql.xt_http;



Often, the biggest problem with regular expressions is that those who use them sometimes don’t use them correctly. A great example occurs in the Oracle Database with the REGEXP_LIKE function. For example, some developer use the following to validate whether a string is a number but it only validates whether the first character is a number.

  lv_input  VARCHAR2(100 (more...)

Sending SMS text messages from PL/SQL

Do you need to send SMS (Short Message Service) text messages from your Oracle database using PL/SQL? This is actually quite easy to do, the only hard part is deciding on which SMS gateway to use. You need to sign up with a gateway provider to get a username and password to use the gateway, and you typically pay some cents for each message you send.

Do a google search for "sms gateway api (more...)

My nominations for the Oracle Database Developer Choice Awards 2015

I'm honored to have been nominated for the Oracle Database Developer Choice Awards 2015, in no less than three (!) categories: PL/SQL, Application Express (Apex) and Oracle REST Data Services (ORDS).

Here's a short video that explains what the Oracle Database Developer Choice Awards are all about:

If you are a regular reader of my blog, you are probably familiar with my contributions to the Oracle Database Developer community, but here is (more...)


Using Oracle’s Resource Manager requires you to understand the IO dynamics. The first step requires you to run the CALIBRATE_IO procedure from the DBMS_RESOURCE_MANAGER package.

Oracle provides some great examples about how to use the CALIBRATE_IO procedure of the DBMS_RESOURCE_MANAGER package in the Oracle Database Database PL/SQL Packages and Types Reference. The CALIBRATE_IO procedure returns the best answer when you provide a valid number of files, which you can capture by querying the V$ASM_DISK view.


Autonomous transaction to the rescue

Use an object in a query?

Using an Oracle object type’s instance in a query is a powerful capability. Unfortunately, Oracle’s SQL syntax doesn’t make it immediately obvious how to do it. Most get far enough to put it in a runtime view (a subquery in the from clause), but then they get errors like this:

SELECT	 instance.get_type()
ERROR AT line 4:
ORA-00904: "INSTANCE"."GET_TYPE": invalid identifier

The problem is how Oracle treats (more...)

PL/SQL CASE Statement

This is an alternative to if… then… else… elsif… end if when you want to use conditional statements in PL/SQL. Here is a simple example, which checks whether a given number is 1 or 2. You start with the word case. Then you add one or more conditions followed by the action to take if that condition is satisfied. Each condition is preceded by the word when. You finish with the words end case:

Rounding Amounts, the missing cent: with the MODEL clause

Rounding amounts may lead to rounding-issues, I have written how this may be resolved in a previous blogpost using some analytic functions.
Playing around a little bit, I also came up with a method to resolve the rounding difference with the MODEL clause.

To create an example, first let's create a table with only three records in it.

SQL> create table t
2 as
3 select rownum + 42 id
4 from dual
5 connect (more...)

Rounding Amounts, the missing cent

Dividing a certain amount over several rows can be quite tricky, simply rounding can lead to differences.
Let me try to explain what I mean. When you need to divide 100 by 3, the answer is 33.333333333333 (and a lot more threes).
Money only goes to cents, so if each one gets 33.33, there is a cent missing. (3 times 33.33 equals 99.99)
To solve this cent-problem, we decide that the (more...)

PL/SQL: The Good Parts

Somewhat inspired by Douglas Crockford's Javascript: The Good Parts presentation (and book), I have created a presentation called PL/SQL: The Good Parts.

This is not intended as an introduction to PL/SQL, but rather an overview of features you should consider if you want to maximize your enjoyment of programming in the Oracle Database. Also, this is not an exhaustive list of all features, it's simply the ones I personally use frequently.

Note that unlike (more...)