- "An exception raised does not automatically roll back (more...)
Recently I got involved in a question on Temporal Validity Periods together with Chris Saxon, one of the askTom-answer team.
The question was along the lines of: "What if I have a single start date but two possible end dates. One of the end dates is filled automatically by a background proces (could be a job) while the other one is to signal that the end date is set manually by the user. Could you (more...)
IntroductionDesigning PL/SQL Programs
Principles and PatternsIntroducing the SOLID principles
Introducing the RCCASS principles
The Dependency Inversion Principle: a practical example
Tools and Techniques
Here is a simplified version of an ETL framework which uses SQL Types in a similar fashion to the approach described in my blog post here. The loading process is defined using an abstract non-instantiable Type like this:
create or replace type load_t force as object
( txn_date (more...)
- 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...)
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...)
Here is what I learnt today.
You can use logical operators in the RETURN clause of a function whose return type is BOOLEAN. So for example, you can write a function with the following structure:
create or replace function test_fn(p_param varchar2) return boolean as l_value varchar2(10):= 'TEST'; begin return false or p_param = l_value or length(p_param) = 5 ; end; / show errors;
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...)