I needed to generate a random string with an exact length consisting of numeric digits, that I could send in an SMS to a user as a temporary account “pin”.
DBMS_RANDOM.string is unsuitable for this purpose as its supported modes all include alphabetic characters. So I used
DBMS_RANDOM.value instead. I call
TRUNC afterwards to lop off the decimal portion.
select TRUNC( DBMS_RANDOM.value( POWER(10,digits-1) ,POWER(10,digits))) from (select 6 digits from dual); 482372
I've never been to KScope. Yes never.
I've always wanted to. Each year you hear of all of these stories about how much people really enjoy KScope and how much they learn.
So back in October I decided to submit 5 presentations to KScope. 4 of these presentations are solo presentations and 1 joint presentation.
This week I have received the happy news that 2 of my solo presentations have been accepted, plus my joint (more...)
Bedeutet, ich habe einen String mit 31 Zeichen der je Zeichen den Zustand 1 oder 0 einnehmen kann.
- 1 steht für aktiv
- 0 steht für inaktiv
Um dies anhand eines verständlichen Beispiels zu verifizieren, habe ich mir eine Dienstplan-Tabelle ausgedacht.
Plan degradations on upgrade are normal.
This one’s no different.
On further investigation, turned out application was setting optimizer_mode = first_rows somewhere.
First point about this is that first_rows really shouldn’t be used either as a hint or as an optimizer_mode.
What does FIRST_ROWS mean?
From 11g doco:
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
If any sort of (more...)
The Hitchhiker’s Guide to the EXPLAIN PLAN Part 41: Why has my SQL execution plan changed?—A checklist
We have today the 06.11.2015 (11-06-2015) and we have two date values 01.11.2015 and 30.11.2015.
Now I want to know how much time has past in percent since the beginning (01.11.2015):
Result: 17 %
Below is a SQL statement from a performance problem I was looking at the other day.
This is a real-world bit of SQL which has slightly simplified and sanitised but, I hope, without losing the real-worldliness of it and the points driving this article.
You don’t really need to be familiar with the data or table structures (I wasn’t) as this is a commentary on SQL structure and why sometimes a rewrite is the best (more...)
Now I want to extend the solution by an optional ZIP export.
You can not export several files at the same time with standard APEX features. For that you have to create a ZIP file including all the files you want to download.
I made an example application "Multi CSV Download as ZIP file" where you see a (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)
Over the past couple of weeks I've been preparing my slides and presentations for Oracle Open World (2015).
One thing that occurred to me was that there was no icon or image to represent Oracle SQL and PL/SQL. I needed something that I could include in my presentations to represent these.
After a bit of Tweeting it turns out that there is no (official) icons or images for Oracle SQL and Oracle PL/SQL.
So I (more...)
This example will use a simple table structure in SQL Server though the concepts are the same when using Oracle, DB2, and most other relational databases. The example will use a simple Dodeca (more...)
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14
DECLARE lv_input VARCHAR2(100 (more...)
Indexes are separate data structures that provide alternate pathways to finding data. They can and do generally speed up the processing of queries and other DML commands, like the
REPLACE INTO, and
DELETE statements. Indexes are also called fast access paths.
In the scope of the InnoDB Database Engine, the MySQL database maintains the integrity of indexes after you create them. The upside of indexes is that they can improve (more...)