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

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

Autonomous transaction to the rescue

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

ORA-22926 when using getClobVal to convert XMLType to CLOB

I ran into a problem the other day when moving some code from one database to another (both XE 11g). The code in question needs to convert an XMLType to a CLOB to do some (hacky) string manipulation on it, and then turn it back to an XMLType.

Here's the original code:

  l_xml := apex_web_service.make_request(...);
 
  -- little hack to remove bad empty namespace from result
  l_clob := l_xml.getClobVal();
(more...)

Longer names coming to Oracle?

Have you ever, like me, been silently finger-counting the number of characters in a table name, column name or procedure name that you are about to create in the Oracle Database, to make sure it is short enough? It appears those days will soon (?) be at an end...

The current limit on names (identifiers such as table names, column names, procedure names, parameter names, etc) in Oracle SQL and PL/SQL is 30 characters. (more...)

A function gets called twice if the result_cache is used

Recently I showed simple example how result_cache works with non-deterministic functions and observed strange behaviour: a function gets fired once in the normal query, but twice with the result_cache hint.
Moreover, only third and subsequent query executions return same cached results as second query execution.
I didn’t want to investigate such behavior, just because 1) we should not cache the results of non-deterministic functions and 2) it doesn’t matter if we use deterministic functions.
But (more...)

Why is it not possible to raise an exception when handling no_data_needed ?

It seems impossible to raise an exception when handling no_data_needed.

See

create or replace function demo return sys.odciNumberList pipelined
as
begin
  pipe row(1);
  pipe row(1/0);
exception when others then
  dbms_output.put_line('in exception handler, sqlcode: ' || sqlcode);
  raise program_error;
end;
/

sokrates@11.2 > select * from table(demo());
ERROR:
ORA-06501: PL/SQL: program error
ORA-06512: at "SOKRATES.DEMO", line 8
ORA-01476: divisor is equal to zero



no rows selected

in exception handler, sqlcode: -1476

As (more...)

NYOUG Training Event: "High Performance PL/SQL" on Nov 7 2014

If you want to be able to create contemporary high-performing PL/SQL code, welcome to my training event in NYC (hosted by NYOUG) on November 7, 2014:

http://nyoug.org/event/nyoug-fall-2014-training-event-high-performance-plsql/?instance_id=258

I think, it is as important as ever to follow best practices for server-side development. There are lots of options that could get the job done "now", but very few that would survive for months/years. Also, there is a constant pressure from various "alternative" solutions to (more...)

My new book

It seems to me that I talked about my most recent book everywhere except for my own blog... So, let me introduce you a one and only performance tuning book dedicated to PL/SQL:


This book took me much longer to write than I initially anticipated - and to be fair, that's the reason for staying quiet at the blog. I just didn't have time/energy to write everywhere :-) Although, it was fun writing - (more...)

5 minutes: Grant Role to Package

The EOUC (EMEA Oracle User Group Community) hosts a special session on sunday during Oracle Open World. This session contains twelve things about Oracle 12c. Each of these 12 things is hosted by a different speaker, so this means that each section is only five minutes long (Strictly guarded by Debra Lilley).
Thankfully I was chosen to be one of the speaker during this special session, very excited and also very scared. (more...)

Time to resurrect blogging!

Sorry for staying quiet for some time, but for some good reasons. Some of these reasons will be explained in later posts, but for now I would like to share the story I wrote upon request of Steven Feuerstein, who asked to tell how PL/SQL impacted our life. Here is my story:

-----------------
I started to work with databases already in college, but more from the data modelling/business analysis angle. When I moved to (more...)

★ Database as a Storage (DBaaS) vs. Thick Database

A recent addition to my Oracle PL/SQL library is the book Oracle PL/SQL Performance Tuning Tips & Techniques by Michael Rosenblum and Dr. Paul Dorsey.

I agree with Steven Feuerstein’s review that “if you write PL/SQL or are responsible for tuning the PL/SQL code written by someone else, this book will give you a broader, deeper set of tools with which to achieve PL/SQL success”.

In the foreword of the book, Bryn Llewellyn writes:

The (more...)

Avoid UTL_FILE_DIR Security Weakness – Use Oracle Directories Instead

Integrigy:

The UTL_FILE database package is used to read from and write to operating system directories and files. By default, PUBLIC is granted execute permission on UTL_FILE. Therefore, any database account may read from and write to files in the directories specified in the UTL_FILE_DIR database initialization parameter [...] Security considerations with UTL_FILE can be mitigated by removing all directories from UTL_FILE_DIR and using the Directory functionality instead.


© Eddie Awad's Blog, (more...)

New in Oracle 12c: Querying an Associative Array in PL/SQL Programs

I was aware that up to Oracle 11g, a PL/SQL program wasn’t allowed use an associative array in a SQL statement. This is what happens when I try to do it.

SQL> drop table test_array purge;

Table dropped.

SQL> create table test_array as
  2  select level num_col from dual
  3  connect by level <= 10;

Table created.

SQL> select * from test_array;

   NUM_COL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10                                                                      

10  (more...)

Are You Using BULK COLLECT and FORALL for Bulk Processing Yet?

Steven Feuerstein was dismayed when he found in a PL/SQL procedure a cursor FOR loop that contained an INSERT and an UPDATE statements.

That is a classic anti-pattern, a general pattern of coding that should be avoided. It should be avoided because the inserts and updates are changing the tables on a row-by-row basis, which maximizes the number of context switches (between SQL and PL/SQL) and consequently greatly slows the performance of the code. Fortunately, (more...)

PL/SQL oddity

sokrates@12.1 > create procedure p is begin null; end this_does_not_compile;
  2  /

Warning: Procedure created with compilation errors.

sokrates@12.1 > show errors
Errors for PROCEDURE P:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/32	 PLS-00113: END identifier 'THIS_DOES_NOT_COMPILE' must match 'P'
	 at line 1, column 11

sokrates@12.1 > REM expexted
sokrates@12.1 > create function f return number is begin return null; end this_does_not_compile;
  2  /

Warning: Function created with compilation errors.

sokrates@12.1 >  (more...)