- Unlike "SOLID", "RCCASS" is awkward to say and doesn't form a neat mnemonic.
- Programmers are far less interested in (more...)
Simply put, there are no agreed design principles for PL/SQL. So it's (more...)
In a previous post I described a situation where the creation of an extension, independently of whether it’s carried out manually by a person or automatically by the database engine, can lead to the invalidation of PL/SQL objects. In this second post on that subject, I describe, with the help of an example (I love examples!) based on the extension_invalidate_pkg_remote.sql script, what can happen when the table on which the extension is created (more...)
For a procedure (or function) in a package, you can assign default values only in the package specifications without assigning them in the package body:
SQL> CREATE OR REPLACE PACKAGE test_default_pkg AS PROCEDURE set_global (p_num IN NUMBER DEFAULT 1); END; / Package TEST_DEFAULT_PKG compiled SQL> SHOW ERRORS; No errors. SQL> CREATE OR REPLACE PACKAGE BODY test_default_pkg AS PROCEDURE set_global (p_num IN NUMBER) AS BEGIN NULL; END; END; / Package body TEST_DEFAULT_PKG compiled SQL> SHOW ERRORS; (more...)
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...)
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)
A couple days ago i created simple package for HTTPS/HTTP, but I’ve decided now to improve it:
- Timeout parameter – it would be better to control connection time;
- 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...)
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
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...)
Do a google search for "sms gateway api (more...)
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:
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
3 select rownum + 42 id
4 from dual
5 connect (more...)
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...)
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...)
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();
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...)
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.
One of my pet-projects is LoggerUtil, which is a utility for Logger, which is an excellent logging tool for PL/SQL.
This post is not about Logger, but some dealings with Conditional Compilation.
With Conditional Compilation you can create a single code base to handle different functionalities depending on compiler flags.
The latest addition to LoggerUtil was a method to create a custom template. For this to work, LoggerUtil depends on a certain Logger Release (where (more...)
Virtual Columns are really cool. I like them a lot. If you've never heard of them, shame on you, learn about them.
In short: a Virtual Column is not a real column, it's an expression that looks like a column... more or less.
While using the Virtual Columns, we ran into a little oddity with them.
First of all let's start with the version of the database that I tested this on. Yes, I know (more...)
Since I have written about my pet project about a month ago, I have made some major changes to the functionality of it.
If you haven't read that blog about my pet project, here's the synopsis:
I love Logger to instrument my code, I just don't like to type in all the bits and pieces to register all the input arguments when I write a new procedure or function. To solve this problem I have (more...)
The article read most often on this blog is called "Splitting a Comma Delimited String, the RegExp way".
On this blog there are two articles about this technique, one is about splitting up a single string into multiple rows and the other is about multiple strings into multiple rows.
Links to both articles are included at the bottom of this article.
It seems like there is a need for functionality like that frequently. And just (more...)