TAPI Generator MkII

The last few weeks I’ve made quite a few improvements to my TAPI generator which I thought I’d share. I’ve also added an Apex API generator which generates code suitable for interfacing between simple Apex applications and my TAPIs. This reduces the volume of PL/SQL required within Apex to a bare minimum.

  • Templates are now defined in a package spec, so they are easier to edit in a tool with syntax highlighting (more or less)
  • (more...)

PL/SQL context switch

Whenever you use PL/SQL in SQL statements, the Oracle engine needs to switch from doing SQL to doing PL/SQL, and switch back after it is done. Generally, this is called a “context switch”. This is an example of that:

-- A function that uses PL/SQL 
create or replace function add_one( value number ) return number is
        l_value number(10):= value;
begin
        return l_value+1;
end;
/
-- A SQL statement that uses the PL/SQL function
select sum(add_one(id))  (more...)

Getting Your Transaction SCN – USERENV(COMMITSCN)

A few days ago I was introduced (or re-introduced) to USERENV(‘COMMITSCN’) by Jonathan Lewis. This is an internal function that allows limited access to the SCN of your transaction.

I was trying to find a way to get the actual commit SCN easily as it struck me that Oracle would have it to hand somewhere and it would be unique to the change and generated very efficiently. I could not find anything to do it (more...)

My Oracle Database Developer Choice Awards 2015


Earlier this week, Santa's little helper (ie DHL) knocked on my door and delivered a package containing my trophies from the Oracle Database Developer Choice Awards 2015. I was nominated in three categories, and won awards in two of them: Application Express and ORDS.




Here's a video from the award ceremony at Oracle OpenWorld 2015:




I'd like to send a big THANK YOU to everyone who voted for me, and also to Oracle for (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...)

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.

(more...)

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 :)
github/XT_HTTP

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

import javax.net.ssl.HttpsURLConnection;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.MalformedURLException;
 (more...)

REGEXP_LIKE Behavior

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
  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 CALIBRATE_IO

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.

(more...)

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