10 Oracle plsql things you probably didn’t know

Many people enjoyed reading my last blog post “10 Oracle SQL features you probably didn’t know”. So I decided to spice it up a little more and do something similar for plsql.

I hope you like that one too.

With our further ado, let’s get started with the list.

10. The first Oracle version to feature plsql was Oracle DB version 6 (1988)

And no. Steven Feuerstein did NOT invent it.

At that time PLSQL did not have stored procedures nor did it have proper exception handling. But it already had embedded SQL.

I learned that from the great Lewis Cunningham. One of the godfathers of development with SQL and PLSQL.

Stored Procedures were added in Oracle 7 (1992). 7.3 was the version when I started to work with an Oracle Database. At that point plsql was in version 2.x. However there never was a version 3. Plsql versioning jumped to 8 when Oracle DB version 8 was introduced and plsql versioning was aligned with the db versions. So there are no plsql versions 3-7. But honestly? Nobody cares anymore that plsql does have its own versioning.

9. labels do not need to match

We can use <<labels>> in plsql. Mostly to increase readability of code. This is especially useful for loop constructs, but it also works for normal begin..end blocks.

begin
 <<mainloop>>
 loop
   <<check_some>>
   begin 
     <<dummyloop>>
     for r in (select * from dual) loop
       -- do nothing
       null;
     end loop dummyloop;
   end check_some;
   exit when 1=1;
 end loop mainloop;
end; 
/

As we can see there are several <<labels>>. And the usage of those labels at the “end” helps to distinguish which code part we are looking at [1].

But this is only as good as the programmer is!

Unfortunatly this works too:

begin
 <<mainloop>>
 loop
   <<check_some>>
   begin 
     <<dummyloop>>
     for r in (select * from dual) loop
       -- do nothing
       null;
     end loop mainloop;
   end check_sum;
   exit when 1=1;
 end loop dummyloop;
end; 
/

Here I mixed up the labels from the loops. And the “end check_sum” does not match the label at the beginning of the block. In fact the “label” at the end can be anything that is not a reserved word.

It runs identical to the previous code (still doing nothing). But it is way more confusing for the “future me” that has to maintain this mess.

8. pragma SERIALLY_REUSABLE

During the lifetime of a session, the package state (package variables, open cursors, etc.) are held in the UGA (user global area).  Subsequent calls in the same session to the same package profit from that by not needing to reinitialize the package state.

The pragma SERIALLY_REUSABLE is able to change this behaviour.

serially_reusable packages

After the work unit (server call) of a SERIALLY_REUSABLE package completes, Oracle Database does the following:

  • Closes any open cursors.
  • Frees some nonreusable memory (for example, memory for collection and long VARCHAR2 variables)
  • Returns the package instantiation to the pool of reusable instantiations kept for this package.

Essentially this means, that the package state exists only during the package call. Not for the whole session.

So far I never had the need to use this pragma. But I can imagine some very very special situations, where this might become interesting.

7. You can compile a package even while another session is running it

In general this is not possible. Assuming a package is currently running. Or to say it in technical terms: We have an active session (Session A) executing a packaged procedure.

A second session  (Session B) trying to do an “ALTER COMPILE PACKAGE (BODY)” would wait until session A finishes and will then afterwards try to compile the package. Upon success the first session A then will get a “package state has been discarded” error message as soon as it tries to run the same package once again. The second next try to run the package would succeed and will use the new package version. At that time the package state was discarded from the session and the new instantiation can be loaded.

This is all documented and well known behaviour.

We face three potential issues with this

  1. Session B needs to wait
  2. Session A might get an error
  3. Session B might wait so long that the developer decides to kill the client (closing SQL developer) thereby making everything 10 times worse, because the database compile call still is valid on the database session level – blocking all following attempts to run or compile the package.

EBR for the rescue!

Using Edition Based Redefinition (EBR) we can circumvent those issues.

Both sessions just need to use different editions. EBR allows us to store and run different code versions of the same plsql based object in the same database.

 

preparation

First create a package with a long running procedure. My example uses a procedure that runs for exactly 1 minute.


create or replace package myPck is
  procedure runMinute;
end myPck;
/

create or replace package body myPck is
   procedure runMinute is
   begin
     sys.dbms_lock.sleep(60);
   end runMinute;
end myPck;
/

 

Setup an edition DEV$ALPHA that is a child of the default edition (ORA$BASE).


create edition DEV$ALPHA;

 

You need an edition enabled schema to do this.

This is simple to do, but to explain EBR in more detail is beyond the scope of this blog post.

Example scenario

Lets run a few commands in two different sessions.

Session A resembles a USER/TESTER who currently executes the packaged function.

Session B resembles a DEVELOPER who wants to deploy a new version of the package.

Session A does this

set time on

alter session set edition=ORA$BASE;

execute myPck.runMinute;

Session B was started already and after the execute in Session A, run the following script in Session B.

alter session set edition=DEV$ALPHA;

— add a new procedure to the package

create or replace editionable package myPck …
/

create or replace editionable package body myPck …
/

— run the new procedure

exec myPck.run5secs;

Result is Session B finishes way before Session A does complete its 1 minute run.

See screenshotebr_run_sessions

q.e.d.

6. call a (pipelined) table function without the TABLE operator

It works only from 12.2 onwards. It is more of a SQL feature than a plsql one.

This will make a table function look indistinguishable from a parametrized view.

Example: split_string

First lets create a simple little table function. This one here just converts a delimited list into rows.

create or replace function split_string
 (p_str IN VARCHAR2
 ,p_delimiter IN VARCHAR2 default ','
 ) RETURN sys.odcivarchar2list PIPELINED 
IS
/** Function to split strings based upon delimiter
*
* @author Sven Weller
*
* @param p_str input string 
* @param p_delimiter delimiter string, default =, Delimiter should only be 1 char.
* @return list of strings
*
*/
 v_entry varchar2(4000);
 v_remaining_str varchar2(4000);
BEGIN
  -- input string needs to hold something to be able to split
  if p_str is not null then
     <<steps>>
    for i in 1..regexp_count(p_str,'\'||p_delimiter)+1 loop
      -- search + split
      v_entry := rtrim(regexp_substr(p_str,'[^\'||p_delimiter||']*('||p_delimiter||'|$)',1,i),p_delimiter);
      pipe row(v_entry);
    end loop steps; 
  else raise no_data_found; 
  end if; 

END split_string;
/

 

function created.

in 11g we call the function like this:

select * from TABLE(split_string('A:BB::CCC',':'));

in 12.2 we can now call it like that:

select * from split_string('A:BB::CCC',':');

As you can see the TABLE row source operator is gone. And it still works! The results of both statements are identical.

COLUMN_VALUE
A
BB
CCC

Want to test it? I made an example on livesql.com.

At the moment this is an undocumented 12.2 feature. So don’t use it for production code (yet). I quite like it. Less code is better! It might become some de-facto standard (similar to connect by level) and eventually will make it into the documentation.

5. dot notation for parameters

We can refer to parameters using the name of the module that declared them. This is useful when we need to distinguish a parameter from a column name.

example


create or replace function myFancyFunc (dummy in varchar2) return number
is
  ret number := 0;
begin
  begin
    select 1 into ret
    from dual
    where dummy = myFancyFunc.dummy
    and rownum = 1;
  exception
    when no_data_found then null;
  end;
  return ret;
end myFancyFunc;
/

Function MYFANCYFUNC compiled

select myFancyFunc('X') from dual;

1

select myFancyFunc('Y') from dual;

0

The function simply compares the value in the dummy column of the dual table to the value we input. If instead we would just compare dummy=dummy then we would get always 1 as a result. No matter what the input is. Even if we add an alias to the table and prefix the column with an alias, the non aliased “dummy” will still be interpreted as a column.

This behaviour is documented: Oracle Doc 12.1 – plsql name resolution

If a SQL statement references a name that belongs to both a column
and either a local variable or formal parameter,
then the column name takes precedence.

Interestingly we can also use labels on block level for specifying variables that are defined in this block.

set serveroutput on
<<main>>
declare
  dummy varchar2(10) := 'Y';
begin
  <<block1>>
  declare
    dummy varchar2(10) := 'X';
  begin
  <<block2>>
    declare
    dummy varchar2(10) := 'A';
    begin
      select dummy
      into dummy
      from dual
      where dummy = block1.dummy;

      dbms_output.put_line('MainBlock:'||main.dummy);
      dbms_output.put_line('Block1:'||block1.dummy);
      dbms_output.put_line('Block2:'||block2.dummy);
    end block2;
  end block1;
end main;
/

PL/SQL procedure successfully completed.

MainBlock:Y
Block1:X
Block2:X

Without dot notation the innermost variable (block2) is used – as we can see in the INTO part. And we can reference a different variable with the same name from a “higher” declaration by using the dot notation.

 

4. variable names be emojis

example

set serveroutput on
declare
  "💩"exception;
  pragma exception_init("💩",-20001);

  "⌚" timestamp := systimestamp;
  "🕑"interval day to second;
  "🎲"number;
  "💤"number := 2;
begin
  "🎲":= round(dbms_random.value(1,6));
  for "🔜"in 1.."🎲"loop
    dbms_lock.sleep("💤");
  end loop;
  "🕑":= systimestamp - "⌚" ;
  dbms_output.put_line('Slept for '|| "🕑");
exception
  when "💩"then
    dbms_output.put_line('Sorry something bad happend!');
    raise "💩";
end;
/

PL/SQL procedure successfully completed.

Slept for +00 00:00:08.049000

The source code looks a little bit different in sql developer. But trust me. I simply copy&pasted it from there to here.

emojicode

To make this work you need to use a font that supports emoijs/symbols, I used font “Segoe UI Symbol”. It is supposed to look better on windows 10[3].

If you are a hard core emoji lover then I suggest to have a look at emojicode.org

It is a emoji based programming language. Which did not make it into the esoteric programming languages list (yet).  Ook? Ook!

 

3. variables can be made mandatory (NOT NULL)

Check out the NOT NULL keyword during the variable declaration.

declare
  v_index number not null := 0;
begin
  v_index := 1;
  v_index := null;
end;
/

Error report –
ORA-06550: line 5, column 14: PLS-00382: expression is of wrong type
ORA-06550: line 5, column 3: PL/SQL: Statement ignored

The error message is a bit vague about what happened, but it is very exact where it happened (line 5, column 14). And what do we see there? A NULL expression.

The expression is of wrong type, because we added a NOT NULL constraint to the number type that was used. For more complex cases we can create our own sub types and use them. But if we just want to make sure that we do not need to consider null cases during further variable calls, then this is a possible way.

Link to plsql documentation

Currently there are no such other constraints  that we can use.  I could imagine with the potential arrival of SQL assertions, this might become a hot topic in plsql too.

2. you can “hack” dbms_output

Warning! This is dangerous. It might break some (poorly written) code that resides in the same schema. Do it at your own risk! It is also hilariously funny to do on april fools day to your fellow coworkers. I mean they shouldn’t use dbms_output anyway. That will teach them!

I start the example by showing the behaviour first. Then the code to produce this result.

behaviour


create or replace procedure doSomething is
  v_dummy dual.dummy%type;
begin
  select dummy into v_dummy from dual where 1=2;
exception
  when others then
    dbms_output.put_line(sqlerrm);
end doSomething;
/

Now we run the module a couple of times and want to see the output. We should expect a NO_DATA_FOUND error message.

set serveroutput on

execute doSomething;
execute doSomething;
execute doSomething;
execute doSomething;
execute doSomething;

Surprisingly instead of the error message we get something like this.

PL/SQL procedure successfully completed.

Wrong usage of DBMS_OUTPUT detected.

PL/SQL procedure successfully completed.

Make Databases Great Again!

PL/SQL procedure successfully completed.

ORA-01403: no data found

PL/SQL procedure successfully completed.

Make Databases Great Again!

PL/SQL procedure successfully completed.

So tell me what you want, what you really, really want

Omg! What is going on here?

Well here is the catch. We can “overload” dbms_output in out own schema. Then our package is called and not the original package from sys.

source code

create or replace package dbms_output
as
  procedure enable(BUFFER_SIZE number default null);
  procedure put_line(A in varchar2);
  procedure GET_LINE(LINE out VARCHAR2,STATUS out integer);
  procedure GET_LINES(LINES out sys.dbms_output.CHARARR,NUMLINES in out NUMBER);
  procedure GET_LINES(LINES out sys.DBMSOUTPUT_LINESARRAY,NUMLINES in out NUMBER);
end dbms_output ;
/

create or replace package body dbms_output
as
procedure enable(BUFFER_SIZE number default null) is
begin
  sys.dbms_output.enable(BUFFER_SIZE);
end;

function getRandomQuote (A in varchar2) return varchar2
is
  type quotes_t is table of varchar2(4000) index by binary_integer;
  v_quotes quotes_t;
  v_random binary_integer;
begin
  v_quotes(1) := 'You are hacked by the Chinese';
  v_quotes(2) := 'Wrong usage of DBMS_OUTPUT detected.';
  v_quotes(3) := 'System failure. Get away from keyboard';
  v_quotes(4) := 'Close all windows! NOW!';
  v_quotes(5) := 'Make Databases Great Again!';
  v_quotes(6) := A; -- sometimes return the correct text
  v_quotes(7) := A; -- sometimes return the correct text
  v_quotes(8) := 'So tell me what you want, what you really, really want';
  v_quotes(9) := 'None but ourselves can free our minds.';
  v_quotes(10) := 'Let there be light!';
  v_random := round(dbms_random.value(1,v_quotes.last));

  return v_quotes(v_random);

end getRandomQuote;

procedure put_line(A in varchar2) is
begin
  sys.dbms_output.put_line(getRandomQuote(A));
end;

procedure GET_LINE(LINE out VARCHAR2,STATUS out integer)
is
begin
  sys.dbms_output.GET_LINE(LINE,STATUS);
end;

procedure GET_LINES(LINES out sys.dbms_output.CHARARR,NUMLINES in out NUMBER)
is
begin
  sys.dbms_output.GET_LINES(LINES,NUMLINES);
end;

procedure GET_LINES(LINES out sys.DBMSOUTPUT_LINESARRAY,NUMLINES in out NUMBER)
is
begin
  sys.dbms_output.GET_LINES(LINES,NUMLINES);
end;
end dbms_output ;
/

How does it work?

Because how sql name resolution kicks in, the DBMS_OUTPUT package in our schema is used and not the public synonym for the DBMS_OUTPUT package from the sys schema.

The get_line functions then pushes the changed text to the normal buffer mechanism.

How can we avoid it?

Best is not to use DBMS_OUTPUT in real production code. It is a nice quick debugging tool. But not more than that.

Also if you prefix dbms_output always with the SYS schema, then it will call the original logic.
 

1. when others does not catch all exceptions

example

set serveroutput on 
declare
  e_cancelled exception;
  pragma exception_init(e_cancelled, -1013);
begin
  begin
    raise e_cancelled;
  exception
    when others then
      dbms_output.put_line('EXCEPTION OTHERS');
  end;
end;
/

ORA-01013: user requested cancel of current operation
ORA-06512: at line 6

We still see an exception, but not the dbms buffer output!

This needs some explanation.

There is a very limited set of exceptions that will not be captured by the WHEN OTHERS handler. We need to look closely and understand the exception itself to comprehend why this is a good thing.

Here the ORA-01013 is the “user requested cancel of current operation” exception. Essentially it means somebody pressed “CTRL+C” while running the code. In almost all environments this means: Stop doing whatever you do immediately! Or in more technical terms: It is an interrupt to the os process running your command. Same as executing “kill -2” (kill -SIGINT) in a nix environment (the-3-most-important-kill-signals-on-the-linux-unix-command-line). Even if the process is allowed to ignore the command, it shouldn’t do so by default.

ORA-01013 can sometimes also be the result of a timeout. Where the client is waiting for a response and after some time sends this as a timeout signal to the database session.

We are allowed to capture this exception and write a special handler for it.

set serveroutput on
declare
  e_cancelled exception;
  pragma exception_init(e_cancelled, -1013);
begin
  begin
    raise e_cancelled;
  exception
    when others then
      dbms_output.put_line('EXCEPTION OTHERS');
  end;
exception 
  when e_cancelled then
    dbms_output.put_line('OPERATION CANCELED');
END;
/

PL/SQL procedure successfully completed.

OPERATION CANCELED

Nothing to worry about. Just nice to know.

Please note: This example will behave differently in older outdated db versions. I think it was introduced as a fix for bug#12838063 in 11.2.0.4.

Other exceptions that are not handled include “ORA-03113: end-of-file on communication channel”.

But not “ORA-06508: PL/SQL: could not find program unit being called”. This was supposed to go through “when others” but testing on 12.2.0.1 revealed it is captured.

 

 

Footnotes


1. This is probably the only bug free code I ever wrote. It was meant to do nothing and it does that exceptionally well![2]
2. There might be room for some performance improvement. Allowing us to do nothing even faster.
3. On windows 10 💩 is supposed to look like 🔝💩.

 

 

 

10 Oracle SQL features you probably didn’t know

10 Oracle SQL features you probably didn’t know

I can not guarantee that you never heared of this. But the majority of developers doesn’t know or doesn’t seem to know. And honestly –  most of this information is fairly useless – and mostly harmless.

10. Why we have the CONCAT function

There is a concat function that can be used instead of the concat operator ||.

Nobody uses that! But there is a reason why it exists.

This is from the 8.1.7 Oracle documention

On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets.

So we do have this function, because in the past this || was not consistent over all the different platforms using different character sets. Since nobody seems to use IBM EBCDIC anymore, there is no real need to use the CONCAT function.

9. select UNIQUE

Instead of doing a SELECT DISTINCT you can do SELECT UNIQUE.

But you shouldn’t. None of them. Forget I mentioned this.

Ok here is an example.

Example

select unique e.deptno from scott.emp e;

DEPTNO
-----
30
20
10

It is also possible to do SELECT COUNT(UNIQUE …) . Not sure when that one was introduced, but it seems to work now.

Sven says: “SELECT DISTINCT|UNIQUE should be considered a bug in real production code.”

I have yet to find an example where SELECT DISTINCT is needed. More likely there is a bug in the data model or missing joins in the where clause. GROUP BY or sometimes EXISTS are the better long term alternatives.

Using SELECT DISTINCT is absolutly fine for developer ad-hoc queries.

I feel this whole concept is a bit inconsistent. 12c introduced the new approximation function APPROX_COUNT_DISTINCT, but there is no APPROX_COUNT_UNIQUE!

8. there is an ANSI datatype DATE

The ANSI datatype DATE does not hold any time information (no hours, minutes, seconds). That is what the ANSI datatype TIME is for. Oracle does not officially know these two datatypes.

However:

EXTRACT function

Purpose

EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval expression. The expr can be any expression that evaluates to a datetime or interval data type compatible with the requested field:

  • If YEAR or MONTH is requested, then expr must evaluate to an expression of data type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL YEAR TO MONTH.
  • If DAY is requested, then expr must evaluate to an expression of data type DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND.
  • If HOUR, MINUTE, or SECOND is requested, then expr must evaluate to an expression of data type TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND. DATE is not valid here, because Oracle Database treats it as ANSI DATE data type, which has no time fields.
  • If TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION, or TIMEZONE_OFFSET is requested, then expr must evaluate to an expression of data type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE.

So essentially the doc says, that EXTRACT uses an ANSI DATE as an expression input.

Proof

select extract(day from sysdate) from dual;
21

select extract(second from sysdate) from dual;
ORA-30076: invalid extract field for extract source

select extract(second from systimestamp) from dual;
4.823

The error appears, because sysdate is kind of converted into a ANSI DATE and this does not hold time information.

 

Problem is: We just can not use this ANSI date type for normal activities.

Alternative for the ANSI date: TRUNC(datetime). In 12c as a virtual column.

Problem solved. ✔

 

7. group by ()

You can group by an empty parenthesis. Or let’s call it group by the FULL SET. This is in fact useful in some very specific cases.

It gives us the ability to get a NO_DATA_FOUND error if there is no data at all. Oracle guarantees that an aggregation select without a group by will always return a record. GROUP BY () can be added if you want to change this default behaviour on purpose.

Example

Let’s say we want to count how many employees are in one specific department.

select count(e.empno) emp#
from scott.dept d
left join scott.emp e on e.deptno=d.deptno
where d.dname = 'OPERATIONS';

EMP#
----
0

Since there are no employees in operations, this returns 0. This is correct and the information I want.

More specifically this query returns one row, but the e.empno is null and therefore is not counted. Count(*) would have returned 1!

But if we use a department name that does not even exists, then this will also return 0.

select count(e.empno) emp#
from scott.dept d
left join scott.emp e on e.deptno=d.deptno
where d.dname = 'FANCY_CLOUD_SPACE';

EMP#
----
0

Not correct! In this case I prefer to get a no data found error.

Grouping on the full set gives us this option.

select count(e.empno) emp#
from scott.dept d
left join scott.emp e on e.deptno=d.deptno
where d.dname = 'FANCY_CLOUD_SPACE'
group by ();

no rows found.

Btw: the SQL Developer syntax (pre-)parser identifies that () as an syntax error. But it will execute it successfully.

I have to admit, that in this case it would be much better to group by the department. But maybe it would be possible to get the count for multiple departments using an IN list. Then the group on department level would not help much.

 

Btw: GROUP BY NULL does the same thing, and might be less confusing for some developers. I personally prefere GROUP BY () because the () also is used in GROUPING_SETS.

 

6. + vs. –

In some edge cases using a different operator (+|-) leads to disturbing results.

Example: Substracting a tiny number from 1 does not equal 1. But adding a tiny number from 1 does equal 1!

select * from dual
where 1 = 1-0.000000000000000000000000000000000000001;

No data found!

This is expected behaviour…

select * from dual
where 1 = 1+0.000000000000000000000000000000000000001;

DUMMY
-----
X

Wat? Why do we get a result?!

The solution is simple. We overstepped the maximum precision of the number datatype when doing the addition. Max precision is 38 digits. The addition would require a precision of 39. Instead the result is rounded (or truncated) to a precision of 38 digits, which happens to equal 1.  The substraction result still is in the 38 precision range and therefore exact (very slightly less than 1).

Don’t confuse precision with maximum value! Precision is the number of different digits that we can store. This can then be moved by an exponent to the left or right for very large or very small values.

 

To put this into perspective

That precision is so extremly high, that we have major troubles visualizing it. So here is a comparsion. The comparison data comes from this hugely interesting site: http://money.visualcapitalist.com/all-of-the-worlds-money-and-markets-in-one-visualization/

The complete monetary assets in the whole word is estimated about 80 trillion $ (broad money=coins, banknotes, savings accounts, time deposits, …). That is a 80,000,000,000,000  or an 8 with 13 zeros at the end or 8e13 in scientific exponential notation.

select 8e13 from dual;

Considering all the national and private debts is even higher. Here we reach about  200 trillion $.

And if we consider derivates (=high risk gambles)  then the high estimations go as far as 1.2 quadrillion $.  That is a 1 followed by 24 more digits (mostly zeros) or 1.2e24.

Since all this is expressed in dollars, we can increase the number even further by chooseing a different currency.  For example the Nepalease Rupee is worth a little less than 0.01 dollar. So all the worlds derivatees could be expressed in Nepalease Rupees using a number as big as 1.2e26.

That means using a number with a precision of 38 we can easily express all the money in the world including derivates up to the very last Nepalease Rupee. And we still have only used 2/3rds of the maximum available precision. Or to say it differently. If we duplicate the planet Earth 1,000,000,000,000 (1 trillion) times – we could still give the exact amount of money on all planets in Napalease Rupees up to the very last Rupee using the number datatype.

That’s quite impressive!

Btw. The biggest number that can be expressed using the NUMBER datatype is 999…(38 9’s) x10125 . Which is way way bigger than anything we discussed so far.

 

5. instead of IN we can use = ANY

select 'TRUE'
 from dual
 where 1 = ANY (1,2,3,4,5)
TRUE

There are several similar unusual “replacement” options.

e.g.

  • instead of != we can also use ^=.
  • NOT IN is the same as  != ALL
  • SOME is a synonym for ANY – but only in sql!
  • <=LEAST | >=GREATEST can be replaced by <|>= ALL
  • >=LEAST | <=GREATEST can be replaced by >|<= SOME

I expect that SOME people do know about these operators, since questions about them are part of the SQL expert exam. So from ALL experts who took the exam at LEAST the few who passed should know SOMEthing about ANY.

The last replacement differs how NULL values are treated. In theory there could be cases where this comes in handy. I so far never had such an encounter.

Example

with testdata
as (select 1 a, 2 b, 3 c, 4 d from dual union all
   select 5 a, 4 b, 3 c, 2 d from dual union all
   select 1 a, null b, 2 c, null d from dual union all
   select null a, null b, null c, null d from dual)
select * from testdata
where 3 >= LEAST(a,b,c,d);

 

A B C D
-------
1 2 3 4
5 4 3 2

 

with testdata
as (select 1 a, 2 b, 3 c, 4 d from dual union all
    select 5 a, 4 b, 3 c, 2 d from dual union all
    select 1 a, null b, 2 c, null d from dual union all
    select null a, null b, null c, null d from dual)
select * from testdata
where 3 >= ANY(a,b,c,d);

 

A B C D
-------
1 2 3 4
5 4 3 2
1 - 2 -

4. external table on multiple files

External tables can load multiple files at once! As long as they have the same structure of cause. So here is an example.

CREATE TABLE EXT_DUMMY
(
    "RECORDTYPE" VARCHAR2(100 BYTE), 
    "COL1" VARCHAR2(100 BYTE), 
    "COL2" VARCHAR2(100 BYTE) 
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER DEFAULT DIRECTORY "IMPORT_BAD_FILE" 
    ACCESS PARAMETERS ( 
             records delimited BY newline 
             FIELDS TERMINATED BY ';' 
             MISSING FIELD VALUES ARE NULL 
               ( RECORDTYPE CHAR
               , COL1 CHAR 
               , COL2 CHAR 
               ) 
    ) 
    LOCATION ( 'Testfile1.txt, Testfile2.txt' )
)
    reject limit 10
;

When we then do a select on the external table, we will see the data from both files.

We can also switch the external table to a different file.

alter table EXT_DUMMY location ('Testfile3.txt' )

 

For the experts: We can use the ROWID to find out which file was used to load a specific record. Special thanks go to OTN-forum member Odie_63 who found this solution. More info in this old OTN forum thread.

with ext_loc as (
      select position-1 as pos
           , name as filename
      from sys.external_location$
      where obj# = ( select object_id
                     from user_objects
                     where object_name = 'EXT_DUMMY' )
    )
select x.filename,
       t.*
from EXT_DUMMY t
join ext_loc x 
on x.pos = to_number(regexp_substr(dump(t.rowid,10,9,1),'\d+$'))
;

It’s a very clever piece of software. Essentially it extracts the filenumber from the rowid, looks up the file number in the data dictionary and combines that with our data set.

This select was done in a 10g database. In 12c we can probably use dbms_rowid to do the same, instead of regexp_substr(dump(rowid)).

3. insert default values

We can insert into a table using the “default” keyword to force default behaviour for this column.

Example

-- setup
create table swe_default_test 
(col1 varchar2(10) not null,
 col2 varchar2(10) default 'TEST' not null
 );
Table SWE_DEFAULT_TEST created.

-- test
insert into swe_default_test (col1) values ('X');
1 row inserted.

insert into swe_default_test (col1,col2) values ('X',null);
ORA-01400: cannot insert NULL into ("MYUSER"."SWE_DEFAULT_TEST"."COL2")

insert into swe_default_test (col1,col2) values ('X',default);
1 row inserted.

-- double check
select * from swe_default_test;
COL1 COL2 
---------- ----------
X TEST 
X TEST 

-- cleanup
drop table swe_default_test;
Table SWE_DEFAULT_TEST dropped.

 

In 12c we now have a new and I think better option to do the same. The default column can be defined additionally with “ON NULL”. Which has the effect, that inserting a NULL value, will lead to using the default value instead. Of cause the “default” keyword still works too.

-- setup
create table swe_default_test12c
(col1 varchar2(10) not null,
 col2 varchar2(10) default on null 'TEST' not null
 );
Table SWE_DEFAULT_TEST12C created.

-- test
insert into swe_default_test12c (col1,col2) values ('X',null);
1 row inserted.

insert into swe_default_test12c (col1,col2) values ('X',default);
1 row inserted.

-- doublecheck
select * from swe_default_test12c;
COL1 COL2 
---------- ----------
X TEST 
X TEST 

-- cleanup
drop table swe_default_test12c;
Table SWE_DEFAULT_TEST12C dropped.

As we can see both cases now work. The one using a NULL value and also useing the DEFAULT keyword.

This new 12c “default on null” feature can be used to replace the typical BEFORE ROW INSERT trigger. More info how to do this in Sequence and Audit columns with Apex 5 and 12c

 

2. (1,2) = ((1,2))

We can compare expression lists using the = operator. But the right side of the comparison needs an extra set of parenthesis.

select 'TRUE' checked from dual
 where (1,2,3,4,5) = ((1,2,3,4,5));
CHECKED
-------
TRUE

This is just a shorthand form of

1=1 and 2=2 and 3=3 and 4=4 and 5=5

If we do not use the second set of parenthesis on the right hand side of the comparison, then we get an error.

select 'TRUE' checked from dual
 where (1,2,3,4,5) = (1,2,3,4,5) ;
ORA-00920: invalid relational operator

This is documented. Although a tiny bit difficult to read in the syntax diagrams.

See: SQL Reference – Simple Comparison

syntax_compare_lists_1syntax_compare_lists_2

 

1. DUAL is a real table

Although there are some special optimizations in various tools, dual is a real table residing in the sys schema.

select owner, table_name
 from dba_tables
 where table_name = 'DUAL';
OWNER TABLE_NAME
----------------
SYS DUAL

 

In older database versions it was possible to do an insert into the DUAL table.

Warning! This is extremly dangerous. It will probably break ALL applications in your database.

insert into dual values ('Y');
select mysequence.nextval into v_id from dual;

TOO_MANY_ROWS error!

And nobody captures the TOO_MANY_ROWS exception for selects from dual.

Btw: A working safety mechanism is to add rownum = 1 to your query.

select mysequence.nextval into v_id from dual where rownum = 1;

Fortunately manipulating DUAL does not work anymore in recent versions of the database. I think 12c introduced it. Not even DBAs have the privs anymore to do this.

ORA-01031: insufficient privileges
*Cause: An attempt was made to perform a database operation without
the necessary privileges.

0. The Oracle SQL Syntax is documented!

Bam! There you have it! You wouldn’t have thought that. Well, you are not alone. So many developers seem to have no clue that this exists or where it exists.

When googeling use “oracle 12c doc” or “oracle 12.2 SQL ref” in front of your search term. It helps to find the proper pages.

12.2 doc portal

12.2 SQL introduction

12.2 SQL Language reference

 

 

Special thanks go out to Stefanie R., who gave me the idea for this blog post!

 

OTN Apprecition Day: the OTN forum

The SQL and PLSQL forum

Today is OTN Apprecitation day so I decided to write a short article about my favourite Oracle feature. It is the OTN SQL and PLSQL forum! Reading and posting on this forum made me a better developer.  I also frequently visit other forums like Database General, Apex and lately the Oracle JET, but not as intensively as the SQL and PLSQL forum.

My OTN forum handle is Sven W.

screen-shot-2016-10-11-at-23-57-04

greetings/honorable mentions

BluShadow – for moderating the forum and creating the FAQ list

Frank Kulash – for always answering in a nice and calm way insistently leading the OP to the final solution.

Billy Verrennye – for makeing me rethink old habits (like naming conventions) and for providing excellent and well thought source code examples.

William Robertson – for always beeing spot on

Odie_63 – For answering some of my questions, for example by taking apart the internal meaning of ROWIDs for external tables.

Boneist and ApexBine – for makeing their statements in a male dominated industry

noticable threads

About naming conventions in PLSQL

Coding Standards and Code Critique Request

features / ideas

PLSQL 101: Datatypes – DATE

About Ansi Joins

Introduction to regular expressions

SQL Assertions / declarative multi-row constraints

other

The 10 database commandmends

Are databases still nice and quick and simple to use like they once were?

Fun stuff from the past

Developers sometimes can be funny and sometimes they just need to boil of some steam.

Here is a collection of thread snippets from the past of the forum. Some years ago I collected memorable posts, but I don’t do this anymore. So the collection is slighty outdated now, but I tried to add a few recent quotes as well.

“OP” is used when I cite the “Original Poster” without giving the real forum handle. Otherwise usually only the first name for some of the well known members are used. Comments to the original quote from myself are in italic. Different threads are separated by a line.

Best of Forum 2007


OP> want a procedure or a block which will give a tree like structure using loops and cursors
3360> Why? Do you have a requirement to make this as slow as possible?


Dave Hemming> select ‘don”t stop me now, I”m having such a good time’ from dual

Special thanks to Dave! This is one of my favourite Queen songs.


APC> Late breaking newsflash: users are not developers!


Damorgan> Writing “working on tables” is as informative as writing “using keyboard and mouse.” See your instructor.


OP> SO CAN YOU PLEASE ASSIST ME ON THAT .

APC> On most keyboards the CAPSLOCK key is halfway down the lefthand side. Please learn to use it.


Billy> The features and flexibility and power of Oracle is NO substitution for a solid relational design.


Sentinel>

insert into table (column) values ('I have John''s shoes');

Sentinel>Of course what I’m doing with his shoes is a completely different story.
John Spencer> Since I only have one pair, I had to go to work barefoot this morning 🙂
John Spencer> Shoeless John


Damorgan> Without a context your posting is just a waste of perfectly good electrons.


OP>Is it possible to do something like this from a running program ?

Billy>Is it possible to jump from an aircraft at 5000 feet? Yes.

Billy>Of course, this has to be questioned as when it is done without a parachute, the changes of survival are very very slim. Never mind that if you’re the pilot, you are sending that plane down.

Billy>Yes, columns can be renamed dynamically from a running program. But it makes as much sense as jumping from a perfectly capable plane without a parachute.


APC> In general computing is about precision and removing ambiguity. That’s why the industry is full of pedants. Maddeningly, there is a direct correlation between pedantry and good programming.


Best of forum 2008


Billy>Be careful about making conclusions using observation only. Simple example. Observe how the WARP_SPEED hint makes the SQL go faster:

SQL> set timing on
SQL> select count(*) from all_objects;
COUNT(*)
----------
10460
Elapsed: 00:00:09.60
SQL> select /*+ WARP_SPEED */ count(*) from all_objects;
COUNT(*)
----------
10460
Elapsed: 00:00:00.50
SQL>

The empirical conclusion is that the WARP_SPEED hint made the query faster by 90%.

This conclusion (based on observation) is incorrect. The real reason why the 2nd query is faster is that it made substantially less physical I/O than the 1st query. The 1st query loaded a lot of data needed into the buffer cache. The 2nd query found that data there and had no need to perform the same expensive and slow physical I/Os that the 1st query did. Nor is there a WARP_SPEED hint.

So be very careful on making assumptions and basing conclusions solely on observation.


Sven> If you provide some example data and your insert statement as everybody suggested, we could give much better solutions without guessing all around.

Hans> But then we would only average one reply per question. And we would not get to spend as much time on the forums, getting to know each other so well.


Billy>Users make incredibly poor Oracle gods. That is what the DBA role is – godlike in Oracle and should be treated with care and respect and given only to those persons responsible for actual database administration. And no, users cannot administrate an Oracle database either.


WhiteHat>Hi all,

The Powers that Be at my work have decided to cut back on the number of different systems we have by re-writing a lot of them from scratch and combining functionality in order to reduce downtime caused by ETL processes and the like. so as a result I’m trying to implement the unified theory of everything in my stored proc and I can’t get it to work. Specifically I’m having difficulties combining quantum mechanics and general relativity into a single SQL statement. I’m getting ORA-06502: numeric or value error: String theory conversion error at line 3523

Is this possible in oracle v150.2.0.5 or will I have to upgrade to 153gR2?

being friday afternoon, my brain isn’t really in gear so I’m certain I’ve overlooked something simple. I suspect my basic architecture assumptions are incorrect but not sure. any advice?

Cheers,
WH.
Dave>It’s possible you’re trying to imply a quantum function to a relativistic variable. You’ll need to explicitly CAST it first.

Of course, I definitely think you need to show us your code. 🙂
Leo>Thats not always necessary as especially the quantum function sometimes can be decrypted itself by Oracle.

But definitely we need the code from line 3517.3 until line 3527.8


Billy>If this was ancient times, and you wrote this code to run on any of my databases, I would have handed your over to the SQL Inquisition for showing you the error of your ways.


Sarma>OP already told it is just an exercise for him on PL/SQL. In short, home-work.
Billy>Oh… I see.. You mean like attending Police College and committing, as home work, crimes like armed robbery, assault with a dangerous weapon, vehicular manslaughter, arson, and so on.
Billy>Yeah, I can see how this can teach you how to enforce the law.. NOT!


Justin>It’s generally helpful to specify the actual exception you’re getting rather than just saying “raises an exception”. Oracle error numbers and error strings are exceptionally useful debugging tools.


Michael O>Isn’t this the Oracle Support Forum where all wishes are granted?


Unknown>It is a basic problem that we face too here in forums. How do we show The Good Stuff of Oracle to a SQL-Server fanboy that cannot bare to empty his cup of SQL-Server in order to taste some Oracle?

Or dealing with a Java zealot that has been bitten badly by the J2EE religion, and sees Oracle as a mere persistence layer.. and not good for anything else?

Some people are so convinced that they are so absolutely right, they cannot even entertain the idea of something alternative.. never mind the idea that they just may be horribly wrong.


WhiteHat>[clippy]

Hi! it looks like you’re trying to use Oracle!

Do you:
( ) want to INSERT data to a table
( ) want to UPDATE existing data in a table
( ) ALTER the structure of the table
( ) search the internet for other queries

[clippy]

It’s not clear what you’re trying to do:
as we understand it it seems like:
you have a newly created table and you want to make it so there’s data in it is this correct?


OP> what if i will have thousands of record.i cant write
them all.
Dave> BANGS HEAD ON DESK
Dave> Instead of the select … from dual union select … from dual… perhaps you could use YOUR OWN TABLE.
OP> yeah i told you that i got it already so i dont need to bang head on desk……thanx neways


OP> Can you just tell me which is the best oracle performance tuning tool in the market? It should be free download.
Guido> It’s name is BRAIN (Biological Resource for All Informations Needs). If you really need to download that you should opt for another career path, I guess. 😉


OP>PL DESCRIBE U’R TABLE WORD.
Billy>Use proper English and not IM SPEAK as this is a technical forum and not some SMS teenage chat room.
padders>Please note however that it is considered acceptable to refer to someone’s ‘leet SQL skillz’.
Dave>Although it’s worth first establishing a reputation that clearly indicates that you do not think “irony” means “similar to iron”.


OP>i have run the package and it will take execution time more than 1 hour, how can i redure the execution time? any one help on this issue.
Matt>Remove all the code from the package.


shoblock> I really wish people would read the responses before they complain that they
aren’t working as desired.
APC> Aw c’mon. Next you’ll be wishing people would look stuff up in the documentation instead of straightaway posting questions here.


Laurent> of course regexp could save ink when printed


OP>i’ve try to add commit; but doesn’t work.

Dave>Glad to see you picked up on the need for a more complete explanation than “doesn’t work”.
Dave>Oh wait, you didn’t.

Someoneelse>That’s a new error in 11g:
Someoneelse>ORA-00042 DOESN’T WORK


Someonelse>IF Using_SQL_Server THEN
Someonelse> EXIT Oracle_Forums;
Someonelse>END IF;


Keith>If thats not clear, I’ll join the hitting head against the brickwall gang.


Billy> Do you fix the symptoms? Or do you fix the problem?
Padders>Erm. The problem I think. Aren’t we supposed to hit the symptoms with the lead pipe?


OP>Thread with title like “urgent help in sql plz ”
Billy>STOP!!

For that you need to fill in the “It Is Truly Urgent” form via the request link on the Oracle Forum main page. In triplicate. Submit it to the moderator. Wait for an urgency verification key to be supplied by the moderator. And only then can you post your urgent posting by attaching the urgency key to it for verification purposes.

Since you did not do it, your account is being reviewed for a possible 6 month suspension. You will also be prohibited from practicing Oracle during that time as you have illustrated the lack of common sense by posting this totally uncalled for and unwarranted “urgent” posting in this forum. And not applying common sense when using Oracle can cause serious injury to your database, cause serious damage to the scalability and performance of your applications, and may just cheese off your Oracle DBA resulting in a lead pipe being taken to your knee caps.


William>A right outer join is just a normal outer join written backwards to confuse everyone


More forum fun


John Stegeman> Last time I checked (1 minute ago), there is no “PL/SQL for SIM cards”


John Stegeman> Or even a entry in the mystical magical caverns of the registry, if none of those are set.
Ed Stevens> Please!  I’ll do anything!  I wash your car!  I’ll mow your lawn! Just don’t send me to the registry!


Someoneelse> We are under attack!
The Database General forum is being flooded with spam!
Here are some of the userids:  …
What the hell, is this a new feature of Jive?

jgarry> You want Jive to pump up social media, Jive pumps up social media.

jgarry> On other places I’ve been surprised by being blocked for too much posting.
I’m really not a robot!  It’s hard to tune that limit right, and some people may compose things beforehand.
But worse, spammers would consider it damage and route around it, with whatever they need to do to have numerous logons.
Like when I knocked some fuzzy balls off the umbrella next to my pool and little black widow spiders scattered everywhere.

Dude!> There is already a feature in place that does not allow people to post one message right after another without waiting for a while; 5 min. if I remember correctly.

BluShadow> It’s 30 seconds Dude!, not 5 minutes.

Dude!> Ah well, time is relative

KayK> all you need is a DeLorean


Dude!> How long will it take until everything implodes?
Billy>  Everything? I assume you are limiting “everything” to our solar system?
In that case, around 5 billion years from now, our sun will run out if fuel, shed its outer layers, and implodes into a white dwarf. Unfortunately it is too small to become a black hole. Which would be a kewl thing. Size some time matters.
Everything as in the universe? Guestimate is a 100 or so trillion years – depending on the theory you deem most likely (of which there are more than a few) describes the end of the universe. Implosion is just one of the theories. Perhaps an Asimov’s The Last Question end and beginning?

Dude!> I don’t worry so much about 5 billion years from now — not even history of the past 100 years is correct.


Billy> Disk space is cheaper than the effort to rebuild tables and indexes in order to reclaim space – and to support this effort as SOP.


William> So ‘QTR’ means ‘Quarter’? What is this, Twitter?


Jonathan Lewis> I got to the end to the first line (after the Hi) and thought: “we’re going to see a match_recognize() solution from Stew Ashton here”.

He was right.


“Re: What is the difference between select count(1) from tab and select count(*) from tab;”

Well after some short ramblings about performance and table sizes the gurus discussion went on to the right track.

Dave> One press on the shift key on my keyboard
William> “count(1)” is a nonstandard variation that takes more keystrokes and requires the parser to substitute “*” in place of the “1”, while making the person who wrote the query look foolish.
If you want an approximate result for a large data set quickly, have a look at the SAMPLE clause, e.g.

select count(*) * 20 from somebigtable sample(5);

Frank> Actually, on my keyboard, ‘1’ takes fewer keystrokes (depending on how you count) than ‘*’.  To type ‘1’, I just press the ‘1’ key, but to type ‘*’ I have to hold down the SHIFT key and then press the ‘8’ key.
Even though it’s that much harder to type, “COUNT (*)” is still better than “COUNT (1)”, for the reasons you mentioned.
Jonathan> You may be taking too narrow a view on the problem – although the correct view may, of course, be keyboard-dependent.  You need to step back from the 1/* dichotomy and consider the effect of parentheses:on the problem.

On my keyboard (*) requires me to do:  {shift} 980 {release}   (a total of 4 keystrokes – or 5 finger movements)

but (1) requires me to do: {shift} 9 {release} 1 {shift} 0 {release}  (a total of 5 keystrokes – or 6 finger movements)

Note also that if you are a “classical typist” your are probably going to use {left shift}, which means a large movement to the 1, unless you use a numeric keypad – in which case the 9 requires you to make a large lateral movement with your right hand (which can then stay in place until after the 8 stroke, of course).

Youngsters these days! Just don’t think things through properly!   (;)
William> Perhaps the round bracket keys are not shifted on some keyboards? I don’t think I’ve ever seen that though.
rp0428>Can you provide a specific reference to ANY of your books or blogs that cover an advanced topic such as this?

Sometimes ‘youngsters’ can benefit from seeing the explanation in context with some example code, trace files and execution plans.
Ospin> Just to inform for people with spain keyboards, this keyboards has “(” in shift+8 and “)” in shift+9, so is quit bit easy type “(8)”, so less finger movements and same results
John> To really figure this out, we probably need sql_trace for brains and bodies – when is Oracle going to wake up and put a bunch of SQL coders under a functional MRI scanner and do metabolic analysis to determine the precise effort involved?
However, i’ll say this: even if select(1) was an order of magnitude easier to type than select (*) (which it’s not), the dissonance and mental stress caused by seeing select(1) is probably enough to kill a few million brain cells of my own (not to mention people who come after me and have to read my code)…


All time classics:
Frameworkia – the NEW PLSQL development standard

Apex and Jet – a fairy tale

In the old times, when it was still of some use to wish for the thing one wanted, there lived a King named Joel R. whose daughters were all handsome, but the youngest was so beautiful that the sun himself, who has seen so much, wondered each time he shone over her because of her beauty. The name of the little girl was Apex.

Near the royal castle build with bricks of Forms there was a great dark wood where birds were twittering and many squirrels were running up the trees. In the bark of some trees there were mysterious inscriptions from the foreign county of Java. And in the wood under an old js-tree was a font; and when the day was hot, the King’s daughter used to go forth into the wood and sit by the brink of the cool font. The font was simply awesome and if the time seemed long, she would take out a golden chart, and throw it up and catch it again, and this was her favourite pastime.

On her 12th Birthday Apex looked into a codemirror and saw Apex 5.1 always wearing beautiful purple boots. Now it happened one day that her boots were strapped so tight that the chart, instead of falling back into the maiden’s little hand which had sent it aloft, dropped to the ground near the edge of the well and rolled in. The king’s daughter followed it with her mobile-UI as it sank, but the well was deep, so deep that the bottom could not be seen. Then she began to weep, and she wept and wept as if she could never be comforted. And in the midst of her weeping she heard a voice saying to her: “What ails thee, king’s daughter Apex? Thy tears would melt a heart of stone.” And when she looked to see where the voice came from, there was nothing but a Toad stretching his thick ugly head out of the water. – “I weep because my golden chart has fallen into the font.” – “Never mind, do not weep,” answered the Toad, “I can help you; but what will you give me if I fetch up your chart again?” – “Whatever you like, dear toad,” said she, “any of my wizards, dynamic actions and interactive reports, or even the golden cloud that I wear.” – “Thy wizards, thy dynamic actions and interactive reports, and thy golden cloud are not for me,” answered the Toad, “but if thou wouldst love me, and have me for thy companion and play-fellow, and let me sit by thee at the Universal Theme, and eat from thy tables, and drink from thy views, and sleep in thy little pages, if thou wouldst promise all this, then would I dive below the water and fetch thee thy golden chart again.” – “Oh yes,” Apex answered, “I will promise it all, whatever you want, if you will only get me my chart again.” But she thought to herself: What nonsense he talks! As if he could do anything but sit in the water and croak with the other frogs, or could possibly be any one’s companion.

But the Toad, as soon as he heard her promise, drew his head under the font and sank down out of sight, but after a while he came to the surface again with the chart in his mouth, and he threw it on a nearby Peake. The King’s daughter was overjoyed to see her pretty plaything again, and she required it up and ran off with it. “Stop, stop!” cried the Toad, “take me up too. I cannot run as fast as you!” But it was of no use, Apex had no listener for him, and made haste home, and very soon forgot all about the poor Toad.

The next day, when the King’s daughter was sitting at table with the King and all the court, and eating from her golden plate, there came something up the marble stairs, and then there came a knockout at the door, and a voice crying: “Apex, Apex, let me in!” And she got up and ran to see who it could be, but when she opened the door, there was the Toad sitting outside. Then she shut the door hastily and went back to her server, feeling very uneasy. King Joel noticed how quickly her heart was beating, and said: “My child, what are you afraid of? Is there a giant Page Designer standing at the door ready to carry you away?” – “Oh no,” answered she, “no Page Designer, but a horrid Toad.” – “And what does the Toad want?” asked the King. “O dear father,” answered she, “when I was sitting by the font yesterday, and playing with my golden chart, it fell into the water, and while I was crying for the loss of it, the Toad came and got it again for me on condition I would let him be my companion, but I never thought that he could leave the application server and come after me; but now there he is outside the door, and he wants to come in to me.” And then they all heard him hammering the second time and crying:

“Youngest King’s daughter,
Open to me!
By the fonts water
What promised you me?
Youngest King’s daughter
Now open to me!”

“That which thou hast promised must thou perform,” said King Joel, “so go now and require him in.” So she went and opened the door, and the Toad hopped in. Then he stopped and cried: “Lift me up to install beside you.” But she delayed doing so until the King ordered her. When once the Toad was on the chair, he wanted to get on the table, and there he sat and said: “Now push your page a little nearer, so that we may eat together.” And so she did, but everybody might see how unwilling she was, and the Toad feasted heartily, but every jquery seemed to stick in her throat. “I have had enough now,” said the Toad at last, “and as I am tired, you must deploy me onto your server, and make ready your image folder, and we will lie down and go to sleep.” Then the King’s daughter began to weep, and was afraid of the cold Toad, that nothing would satisfy him but he must sleep in her pretty clean workspace. Now the King grew angry with her, saying: “That which thou hast promised in thy time of necessity, must thou now perform.” So she picked up the Toad with her finger and thumb, carried him upstairs and put him in a corner, and when she had lain down to sleep, he came creeping up, saying: “I am tired and want sleep as much as you; take me up, or I will tell your father.” Then she felt beside herself with rage, and picking him up, she threw him with all her strength against the browser, crying: “Now will you be quiet, you horrid Toad!”

But as he fell, he ceased to be a Toad, and became all at once a prince with beautiful kind shapes. And he told her his name was Jet and how the wicked witch of ADF had bound him by her spells, and how no one but she alone could have released him. Apex soon forgot about her old pal AnyChart and only had an UI for the young and beautiful Jet. And they two would go together to his father’s kingdom. And there came to the door an interactive grid, and behind the grid was standing faithful John Snyders, the servant of the young prince Jet. Now, faithful John had suffered such care and pain when his master was turned into a Toad, that he had been obliged to wear three iron libraries over his heart, to keep it from breaking with trouble and anxiety. When the grid started to take prince Jet to his kingdom, and faithful John had helped them both in, he got up behind, and was full of joy at his master’s deliverance.

And when they had gone a part of the way, the prince heard a sound at the back of the interactive grid, as if something had broken, and he turned round and cried:

“John, the other real data service must be breaking!”

“The ORDS does not break,
‘Tis the library round my heart
That, to lessen its ache,
When I grieved for your sake,
I bound round my heart.”

Again, and yet once again there was the same sound, and the prince thought it must be some ORDS breaking, but it was the breaking of the other library from faithful John’s heart, because he was now so relieved and happy.

The End


Other fairy tales to come:

Patrick Wolf and the seven little Shakeebs