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.
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.
Time has always been an important dimension for data in any database with topics like when was data created, when are records valid, how did records evolve over time, can we compare with yesteryear or even travel through time and data. The Oracle Database 12c release added a number of features in this area of time and history. The powerful Flashback mechanism is enhanced in many ways, such as to allow history to be constructed, (more...)
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.
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:
ERROR AT line 4:
ORA-00904: "INSTANCE"."GET_TYPE": invalid identifier
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 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...)
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.
Oracle Database can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 9999 CE (Common Era, or ‘AD’). Unless BCE (‘BC’ in the format mask) is specifically used, CE date entries are the default.
I never believe 100% anything I read, so I’ll try that. I’ll set (more...)
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...)
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...)
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.
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...)