This uses the default collation which is at its core based on a character-by-character comparison, which almost always matches what humans would consider to be the “right” order – unless decimal numbers are found, in which case this will sort strings like ‘abc-2000’ before ‘abc-30’.
Natural Sort, on the other hand, compares strings using a more complex algorithm that finds embedded decimal numbers and sorts them according to their numeric value.
create or replace type natural_sort as object (
v varchar2(32767),
order member function compare(p_other natural_sort) return integer
);
/
create or replace type body natural_sort as
order member function compare(p_other natural_sort) return integer is
function is_whitespace(a in char) return boolean is
begin
return a is not null and ascii(a) <= 32;
end is_whitespace;
function is_digit(a in char) return boolean is
begin
return a is not null and ascii(a) between 48 and 57;
end is_digit;
function compare_right(a in varchar2, b in varchar2) return number is
bias number := 0;
ia number := 0;
ib number := 0;
ca char(1 char);
cb char(1 char);
begin
-- The longest run of digits wins. That aside, the greatest
-- value wins, but we can't know that it will until we've scanned
-- both numbers to know that they have the same magnitude, so we
-- remember it in bias.
loop
ca := substr(a, ia, 1);
cb := substr(b, ib, 1);
pragma inline (is_digit, 'yes');
if not is_digit(ca) and not is_digit(cb) then
return bias;
pragma inline (is_digit, 'yes');
elsif not is_digit(ca) then
return -1;
pragma inline (is_digit, 'yes');
elsif not is_digit(cb) then
return 1;
elsif ca < cb then
if bias = 0 then
bias := -1;
end if;
elsif ca > cb then
if bias = 0 then
bias := 1;
end if;
elsif ca = 0 and cb = 0 then
return bias;
end if;
ia := ia + 1;
ib := ib + 1;
end loop;
end compare_right;
function natcompare(a in varchar2, b in varchar2) return number is
ia number := 0;
ib number := 0;
nza number;
nzb number;
ca char(1 char);
cb char(1 char);
ret number;
begin
loop
-- only count the number of zeroes leading the last number compared
nza := 0;
nzb := 0;
ca := substr(a, ia, 1);
cb := substr(b, ib, 1);
-- skip over leading spaces or zeros
pragma inline (is_whitespace, 'yes');
while is_whitespace( ca ) or ca = '0' loop
if ca = '0' then
nza := nza + 1;
else
-- only count consecutive zeroes
nza := 0;
end if;
ia := ia + 1;
ca := substr(a, ia, 1);
end loop;
pragma inline (is_whitespace, 'yes');
while is_whitespace( cb ) or cb = '0' loop
if cb = '0' then
nzb := nzb + 1;
else
-- only count consecutive zeroes
nzb := 0;
end if;
ib := ib + 1;
cb := substr(b, ib, 1);
end loop;
if ca is null and cb is null then
-- The strings compare the same. Break the tie with the
-- default comparison.
if a < b then
return -1;
elsif a > b then
return 1;
end if;
end if;
-- process run of digits
pragma inline (is_digit, 'yes');
if is_digit(ca) and is_digit(cb) then
ret := compare_right(substr(a, ia), substr(b, ib));
if ret != 0 then
return ret;
end if;
end if;
if ca < cb then
return -1;
elsif ca > cb then
return 1;
end if;
ia := ia + 1;
ib := ib + 1;
end loop;
end natcompare;
begin
if v is null and p_other.v is null then
return 0;
elsif v is null and p_other.v is not null then
return 1;
elsif v is not null and p_other.v is null then
return -1;
elsif v = p_other.v then
return 0;
end if;
return natcompare(v, p_other.v);
end compare;
end;
/
This creates a type in the schema called natural_sort which can be used in SQL and PL/SQL very simply, e.g.:
select * from my_table order by natural_sort(name);
Warning: this will be slower than the built-in sorting by Oracle, and will not get any benefit from an index on the column; therefore it’s only really suitable when sorting a relatively small number of records, e.g. for display of a small set of records to a user.
Every typical database stores important strings like place names that are then displayed in reports and charts. Most of the time these strings are relatively short (e.g. 30-40 characters) and the reports and charts look fine, but occasionally some records happen to require a much longer string (e.g. 500+ characters) and these strings might cause the reports and charts to become a bit less visually pleasing. In some cases you can get rendering errors where the title or some attribute in a chart is just too long.
Most of the time, the most important information in such a long name is at the start and/or the end of the string; to compromise and show as much as we can fit on the page, we can truncate the middle of the string and replace it with “..” to show that some text has been removed.
The simplest way to do this without a complex expression involving some combination of LENGTH, CASE and SUBSTR is with a single REGEXP_REPLACE using backreferences to retain the start and end of the string:
select REGEXP_REPLACE(
'My very very very long string abcdefghijklmnopqrstuvwxyz hello world wow this string is really very very very long.',
'^(.{50}).{3,}(.{50})$', -- get the first and last 50 characters
'\1..\2', -- replace the middle with ".."
1, 1, 'n' -- treat entire string including newlines
) from dual;
I’ve chosen to keep the first 50 and the last 50 characters in this example. The result:
My very very very long string abcdefghijklmnopqrst..rld wow this string is really very very very long.
If you want to use this trick, you can adjust the length of the resulting string to whatever maximum length you want: choose how many characters to take from the start of the string and the end of the string by changing the two numbers (e.g. 50) in the regular expression.
Testing with a shorter string, we expect it to return the string unmodified:
select REGEXP_REPLACE(
'This is a nice shorter string with <= 102 characters; the entire string should be returned unmodified.',
'^(.{50}).{3,}(.{50})$', '\1..\2', 1, 1, 'n'
) from dual;
This is a nice shorter string with <= 102 characters; the entire string should be returned unmodified.
If the incoming string is already 102 characters or shorter, no replacement is done. In every case, we have guaranteed the resulting string will never be longer than 102 characters.
We have a number of utility packages that deal with dates and timestamps and we create automated unit tests for them using utPLSQL which is an excellent unit test framework for PL/SQL. These unit tests are executed by our automated scripts whenever we merge changes into our git repository; this gives us early warning if something we’ve changed may have caused regressions.
(we were driving in a fairly remote area of our state recently and was lucky enough to happen upon a certain blue box on the side of the road – we were whisked away on a wild adventure through time and space which I might recount at a later, or perhaps earlier, time)
An issue I’ve encountered just a few times has been that some of these unit tests, extremely rarely, fail with “off-by-one” errors; e.g. a function was expected to return a string like “2 days ago” but instead it returned “3 days ago”. In another test, a function that accepted a string like “Today” returned the date “7 July 2025” but the unit test expected it to return “8 July 2025”.
declare
c_now constant timestamp with local time zone := localtimestamp;
begin
ut.expect(
util_report.get_since( c_now - numtodsinterval(2, 'day') )
).to_equal( '2 days ago' );
end;
/
FAILURE
Actual: '3 days ago' (varchar2) was expected to equal: '2 days ago' (varchar2)
These test failures were always rare, seemingly unpredictable, apparently unrelated to any recent changes, and rerunning the same test would never reproduce the failure. In fact, the automated unit test system would succeed the next time it ran, with no intervention. Also, each time it would be a different unit test that failed; sometimes two extremely similar unit tests (e.g. ones that were “opposite” of each other) would have conflicting success/failure results.
Diagnosis
It was not difficult to guess what was causing these test failures due to the nature of the failure: the expected value was always 1 unit earlier or 1 unit later than the actual value returned; where the “unit” here might be a day, a month, or a second, depending on what was being tested and the nature of the test. The unit test calculates the expected value based on the current date/time and either stores this value somewhere, or passes it directly to the relevant UT procedure; the unit test executes the test, which itself internally also retrieves the current date/time. Most of the time, the execution time is very quick and the two dates or timestamps are either identical or at most a fraction of a second apart; when a feature being tested only needs them to be the same day, or month, this will “always” be true.
Except, of course, it will not always be true; if the unit test happens to be executed right about the time the clock ticks over from one day to the next, or one month to the next, the two “TODAYs” will still be very close to each other but their day or month will be different. This difference would cause various unit tests to fail.
How did we resolve this issue? My first approach was a bit of a hack; for each unit test, instead of saying “I expect the result to be X”, I modified each unit test to say “I expect the result to be X or X+1” or something like that. It would compare the values, allow the test to succeed if it was off by one, and call it a day.
Unfortunately trying to work out what “X+1” really means in every scenario became rather complex for some of our functions; sometimes the direction might be negative, and might be a different unit than expected. Ultimately the hack was unsatisfactory because it introduced an uncertainty that a real bug might still pass the unit test, if the bug itself introduced an off-by-one error.
Solution
Instead, what we needed was for the unit tests to be run in an artificial environment where today’s date and time are fixed and known, so that our expected values can be predictable.
Now if we were using SYSDATE throughout our code to get the current date/time, we might consider using the Oracle database’s FIXED_DATE feature to set the return value of SYSDATE to a known value. In our case, however, we use CURRENT_DATE and LOCALTIMESTAMP (and sometimes SYSTIMESTAMP) throughout our codebase, and these internal functions ignore FIXED_DATE.
The approach we took was to replace the critical calls to CURRENT_DATE, LOCALTIMESTAMP and SYSTIMESTAMP with our own wrapper functions. These are only needed in the specific PL/SQL packages that need this level of unit testing and where the unit tests actually need to test what they return with respect to today’s date and time, so we didn’t replace all the references throughout our codebase.
Our wrapper functions use the context value that the utPLSQL framework sets to determine whether the current code is being executed in a unit test (including within any setup/teardown procedures in a unit test package); if the context value is not set, we return the real date/time as normal.
CREATE OR REPLACE PACKAGE util IS
-------------------------------------------------------------------------
-- Wrapper for current_date to allow unit testing
-------------------------------------------------------------------------
function current_date return date;
-------------------------------------------------------------------------
-- Wrapper for localtimestamp to allow unit testing
-------------------------------------------------------------------------
function localtimestamp return timestamp;
-------------------------------------------------------------------------
-- Wrapper for systimestamp to allow unit testing
-------------------------------------------------------------------------
function systimestamp return timestamp with time zone;
END util;
/
CREATE OR REPLACE PACKAGE BODY util IS
c_ut_owner constant varchar2(30) := 'UT';
c_test_timestamp constant timestamp := timestamp'2025-01-01 12:00:00.000';
-------------------------------------------------------------------------
-- Wrapper for current_date to allow unit testing
-------------------------------------------------------------------------
function current_date return date is
begin
-- if we're in a utPLSQL unit test, return a static known value
if sys_context(c_ut_owner || '_INFO', 'CURRENT_EXECUTABLE_NAME') is not null then
return cast(c_test_timestamp as date);
end if;
return standard.current_date;
end current_date;
-------------------------------------------------------------------------
-- Wrapper for localtimestamp to allow unit testing
-------------------------------------------------------------------------
function localtimestamp return timestamp is
begin
-- if we're in a utPLSQL unit test, return a static known value
if sys_context(c_ut_owner || '_INFO', 'CURRENT_EXECUTABLE_NAME') is not null then
return c_test_timestamp;
end if;
return standard.localtimestamp;
end localtimestamp;
-------------------------------------------------------------------------
-- Wrapper for systimestamp to allow unit testing
-------------------------------------------------------------------------
function systimestamp return timestamp with time zone is
begin
-- if we're in a utPLSQL unit test, return a static known value
if sys_context(c_ut_owner || '_INFO', 'CURRENT_EXECUTABLE_NAME') is not null then
return c_test_timestamp at time zone 'GMT';
end if;
return standard.systimestamp;
end systimestamp;
END util;
/
In the PL/SQL packages and in the unit tests for those packages, we just needed to replace all references to current_date, localtimestamp, or systimestamp with util.current_date, util.localtimestamp, and util.systimestamp. This means the unit tests will always get the same value from these functions regardless of what today’s date and time actually is.
Note that this approach did not require adding any extra setup or teardown code to the unit tests, so it was quite simple to implement.
If you are using utPLSQL and are considering using this approach, remember to set c_ut_owner to the schema owner for where you have installed utPLSQL (UT, in the sample code above).
This approach is, however, not universal; in other packages with unit tests that actually need to test the amount of time that passes between two events, we would not call these utility functions; those unit tests don’t depend on a particular date or time, but they do depend on the dates/times being different. Therefore we must take care to consider in each case whether we should use these wrapper functions.
Alternative Approaches
If our situation was more complex, and we needed some unit tests to execute with the “real” dates and times, we would use a slightly different approach; i.e. allow the unit test setup code to enable or disable this behaviour, and/or instead of using a single constant value, allow unit tests to set any particular date/time to suit the specific requirements of the unit test (e.g. by setting a private global in the UTIL package).
This would be a very rare requirement, as most of the time when you are designing an ORDS REST service you should know what query parameters your service supports. However, in the case where your users are allowed to supply an arbitrary list of additional parameters to your service, you won’t know what the keys will be for these parameters.
Since you can’t define the user-defined query parameters in your ORDS endpoint, they won’t be supplied via bind variables. Instead, in your PL/SQL handler you need to get the original query string using owa_util.get_cgi_env('QUERY_STRING'), then parse it to find the query parameters.
Here’s what I’ve used:
function query_string_map
return apex_application_global.vc_map
is
l_plist apex_t_varchar2;
l_map apex_application_global.vc_map;
begin
-- query string may be like:
-- param1=abc¶m2=def¶m3=ghi
-- or blanks may be included like:
-- param1=abc¶m2=¶m3=ghi
-- or the = symbol may be omitted:
-- param1=abc¶m2¶m3=ghi
l_plist := apex_string.split(owa_util.get_cgi_env('QUERY_STRING'), '&');
for i in 1..l_plist.count loop
declare
l_offset pls_integer;
l_key varchar2(255);
l_value varchar2(32767);
begin
l_offset := instr(l_plist(i), '=');
if l_offset > 0 then
l_key := substr(l_plist(i), 1, l_offset - 1);
l_value := substr(l_plist(i), l_offset + 1);
else
l_key := l_plist(i);
-- the value is null
end if;
-- ORDS may encode %20 as '+', but this is not detected by utl_url
l_key := replace(l_key, '+', ' ');
l_key := sys.utl_url.unescape(l_key, 'UTF-8');
if l_value is not null then
l_value := replace(l_value, '+', ' ')
l_value := sys.utl_url.unescape(l_value, 'UTF-8');
end if;
-- add the key/value to the map
l_map(l_key) := l_value;
end;
end loop;
return l_map;
end query_string_map;
This takes the query string and splits it on each occurrence of the & symbol. Each parsed part is expected to take the form key=value, key= or just key (with no = symbol). It converts any escaped URL characters and builds a map of key/value pairs and returns it.
The calling process can then use the map to process each key/value in turn, e.g.
declare
l_map apex_application_global.vc_map;
l_key varchar2(255);
begin
l_map := query_string_map;
l_key := l_map.first;
while l_key is not null loop
-- do something with the key/value
dbms_output.put_line(l_key || ' : ' || l_map(l_key));
l_key := l_map.next(l_key);
end loop;
end;
You are probably familiar with some of the data types supported by the Oracle Database for storing numeric values, but you might not be aware of the full range of types that it provides.
Some types (such as NUMBER, INTEGER) are provided for general use in SQL and PL/SQL, whereas others are only supported in PL/SQL (such as BINARY_INTEGER).
There are others (such as DECIMAL, REAL) that are provided to adhere to the SQL standard and for greater interoperability with other databases that expect these types.
Most of the numeric data types are designed for storing decimal numbers without loss of precision; whereas the binary data types (e.g. BINARY_FLOAT, BINARY_DOUBLE) are provided to conform to the IEEE754 standard for binary floating-point arithmetic. These binary types cannot store all decimal numbers exactly, but they do support some special values like “infinity” and “NaN”.
In PL/SQL you can define your own subtypes that further constrain the values that may be assigned to them, e.g. by specifying the minimum and maximum range of values, and/or by specifying that variables must be Not Null.
What do I prefer?
In my data models, I will usually use NUMBER to store numeric values, e.g. for quantities and measurements; for counts and IDs (e.g. for surrogate keys) I would use INTEGER (with the exception of IDs generated using sys_guid, these must use NUMBER).
In PL/SQL, if I need an index for an array, I will use BINARY_INTEGER (although if I’m maintaining a codebase that already uses its synonym PLS_INTEGER, I would use that for consistency). In other cases I will use INTEGER or NUMBER depending on whether I need to store integers or non-integers.
I don’t remember any occasion where I’ve needed to use FLOAT, or the binary types; and of the subtypes of BINARY_INTEGER, I’ve only used SIGNTYPE maybe once or twice. Of course, there’s nothing wrong with these types, it’s just that I haven’t encountered the need for them (yet).
What about Performance?
There are some differences in performance between these data types, but most of the time this difference will not be significant compared to other work your code is doing – see, for example, Connor on Choosing the Best Data Type. Choosing a data type that doesn’t use more storage than is required for your purpose can make a difference when the volume of data is large and when large sets of record are being processed and transmitted.
Reference Chart: Numeric Data Types
This diagram shows all the numeric data types supplied by Oracle SQL and PL/SQL, and how they relate to each other:
From smallest to largest – the maximum finite integer that can be stored by these data types is listed here. It’s interesting to see that BINARY_FLOAT can store bigger integers than INTEGER, but NUMBER can beat both of them:
BINARY_INTEGER
2.147483647 x 109
INTEGER
9.9999999999999999999999999999999999999 x 1037
BINARY_FLOAT
3.40282347 x 1038
NUMBER
9.999999999999999999999999999999999999999 x 10125
BINARY_DOUBLE
1.7976931348623157 x 10308
To put that into perspective:
If you need to store integers up to about 1 Billion (109), you can use a BINARY_INTEGER.
If you need to store Googolplex (10googol) or other ridiculously large numbers (that are nevertheless infinitely smaller than infinity), you’re “gonna need a bigger boat” – such as some version of BigDecimal with a scale represented by a BigInteger – which unfortunately has no native support in SQL or PL/SQL. Mind you, there are numbers so large that even such an implementation of BigDecimal cannot even represent the number of digits in them…
Storing SMALL Numbers
The smallest non-zero numeric value (excluding subnormal numbers) that can be stored by these data types is listed here.
BINARY_FLOAT
1.17549435 x 10-38
NUMBER
1.0 x 10-130
BINARY_DOUBLE
2.2250738585072014 x 10-308
These are VERY small quantities. For example:
The size of a Quark, the smallest known particle, is less than 10-19 metres and can easily be represented by any of these types.
You can store numbers as small as the Planck Length (1.616 × 10-35 metres) in a BINARY_FLOAT.
But to store a number like the Planck Time (5.4 × 10-44 seconds), you need a NUMBER – unless you change the units to nanoseconds, in which case it can also be stored in a BINARY_FLOAT.
I’m not aware of any specifically named numbers so small that they require a BINARY_DOUBLE; however, there are certainly use cases (e.g. scientific measurements) that need the kind of precision that this type provides.
I have been working with some code that uses JSON, sometimes fairly large documents of the stuff, and it’s often necessary to send this to the debug log (e.g. DBMS_OUTPUT) for debugging; however, the builtin functions that convert a JSON object to a string (or clob) return the JSON document in one big long line, like this:
To show this formatted, I added the following function using JSON_SERIALIZE with the “PRETTY” option, to my utility package:
function format_json (p_clob in clob) return clob is
l_clob clob;
begin
select JSON_SERIALIZE(p_clob returning clob PRETTY) into l_clob from dual;
return l_clob;
end format_json;
Note that my function takes a CLOB, not a JSON object, because sometimes I receive the data already as a CLOB and I don’t want to require conversion to JSON before passing it to my formatting function.
EDIT 28/10/2025: the initial version of this article included code that would convert the clob to a blob to pass to the json_serialize function. I don’t recall why I did this, assuming it might have been a workaround for a bug in an early version of 19c I might have encountered when passing a clob directly. In my testing it seems to work find on current versions of the database so I’ve removed the unnecessary conversion.
In building a code generator I found the need to write a number of helper methods for doing basic modifications of arrays that are indexed by integer – such as appending one array onto another, inserting, and shifting. These arrays represent an ordered sequence of strings (e.g. lines of source code).
I think these would be a useful addition to the language if they were made native – e.g. (new commands in UPPERCASE):
declare
type str_array_type is table of varchar2(32767)
index by binary_integer;
l_lines str_array_type;
l_new str_array_type;
l_idx binary_integer;
begin
.. (some code to fill the arrays here) ..
-- get a subset of lines
l_new := l_lines.SLICE(50, 59);
-- extend l_lines with l_new at the end:
l_lines.APPEND(l_new);
-- shift l_lines forwards and insert l_new
-- at the beginning:
l_lines.PREPEND(l_new);
-- insert l_new into l_lines at the given index;
-- shift any existing lines at that location
-- forward:
l_lines.INSERT(l_new, at_idx => 21);
-- remove the given range of indices from
-- l_lines, replace with whatever is in l_new:
l_lines.UPDATE(l_new,
from_idx => 120,
to_idx => 149);
-- apply the given substitution on each line
l_lines.REPLACE_ALL(
old_val => 'foo',
new_val => 'bar');
-- shift the given range of lines by the given
-- offset (raise exception if existing data
-- would get overwritten):
l_lines.SHIFT(
from_idx => 20,
to_idx => 29,
offset => 1000);
-- shift and renumber all indices in the array
-- with the given starting index and increment:
l_lines.RENUMBER(start_idx => 10, increment => 10);
-- make the array contiguous (i.e. remove gaps):
l_lines.RENUMBER;
-- loop over every line in the array that contains
-- the given string:
l_idx := l_lines.FIND_NEXT(contains => 'hello');
loop
exit when l_idx is null;
.. do something with l_lines(l_idx) ..
l_idx := l_lines.FIND_NEXT(contains => 'hello',
from_idx => l_idx);
end loop;
end;
I’ve illustrated these with a little sample package that may be viewed here:
Need to run DBMS_MVIEW.explain_mview in APEX SQL Workshop, but don’t have the MV_CAPABILITIES_TABLE? You’ll get this error:
ORA-30377: table ORDS_PUBLIC_USER.MV_CAPABILITIES_TABLE not found
You don’t need to create this table. You could create this table by running admin/utlxmv.sql (if you have it). Instead, you can get the output in an array and do whatever you want with its contents, e.g.:
declare
a sys.ExplainMVArrayType;
begin
dbms_mview.explain_mview('MY_MV',a);
dbms_output.put_line('Explain MV '
|| a(1).mvowner || '.' || a(1).mvname);
for i in 1..a.count loop
dbms_output.put_line(
rpad(a(i).capability_name, 30)
|| ' [' || case a(i).possible
when 'T' then 'TRUE'
when 'F' then 'FALSE'
else a(i).possible
end || ']'
|| case when a(i).related_num != 0 then
' ' || a(i).related_text
|| ' (' || a(i).related_num || ')'
end
|| case when a(i).msgno != 0 then
' ' || a(i).msgtxt
|| ' (' || a(i).msgno || ')'
end
);
end loop;
end;
The result will be something like this:
Now, the challenge is merely how to resolve some of those “FALSEs” …
This is an article I wrote quite some time ago and put “on ice” until I completed the client project it was related to. However, for unrelated reasons the project was significantly delayed and later rebooted with an updated set of requirements, which no longer require Oracle Workspace Manager. This means I’m unable to add a triumphant postscript saying “this was a great success and it’s being used to this day”. Instead, I’m adding this preamble to say “this was a very interesting feature of Oracle I learned but didn’t quite get to use, but hopefully someone will find it useful”.
Oracle Workspace Manager (OWM) is a feature of the Oracle database that was built back in the 8i days and installed in Oracle by default since 9i. As described by Tim Hall, it allows multiple transactionally consistent environments to exist within one database.
Confusing Terms… Don’t confuse OWM with Oracle APEX workspaces, or with Analytic Workspace Manager for Oracle OLAP.
OWM allows the developer to take a leap over the complexities involved in a number of use cases, such as:
Savepoints – a snapshot of a point in time
Workspaces – a private area for modifications that are not visible to normal users
Row History – store a history of every change for every record
Valid Time – support date/time range validity for each record
The Row History use case is similar to using Flashback Query which is a more modern feature of the database; however, since it can be enabled or disabled individually for each table, it may require less storage space to support querying back as far as the user would like in time.
The Valid Time use case allows your users to set a date/time range on each record; multiple versions of a unique row can be created with non-overlapping date ranges; updates can be done within the context of a given date/time range, which will cause rows that span the boundary of the range to be split into multiple versions. I haven’t tried this myself but it looks like a powerful feature.
Note: the default workspace for a user session is called “LIVE“, and the default savepoint is “LATEST“.
One example of using savepoints is that a user could create a savepoint, make changes, go back and view the database as of the savepoint, and can rollback all changes to a savepoint. They can also run an API command (dbms_wm.SetDiffVersions) to generate a differences report which shows all the inserts, updates, and deletes that have occurred since a savepoint.
An example of using workspaces is where a user could create one or two workspaces, each representing a different scenario. The user can jump back and forth between the scenarios and the “live” workspace (which is the default). They can edit any of these workspaces. Changes made by other users to the “live” workspace may, optionally, be automatically propagated into a workspace. Similarly to savepoints, the user can get a differences report between any workspace (including the “live” workspace). In addition, the user can create and view savepoints and additional workspaces within a workspace – OWM maintains a hierarchy of workspaces.
If the user is not happy with their scenario, they can simply delete the workspace and the live data is not affected.
If they are happy with their changes in a workspace, they can choose to do a Merge – which attempts to effect all the inserts, updates and deletes that were made in the workspace to its parent workspace (e.g. the “live” workspace if that was the source). If any change to a row would conflict with a change that another user made to the same row in the parent workspace, the merge stops and the user may be prompted to resolve the conflicts (i.e. for each row, the user can decide to refresh their workspace with the updated data in the parent, or they can force their workspace’s change onto the parent).
I suspect OWM was once known (or internally referred to) as “Long Transactions” or something like that, probably because of its user workspace and merging features. You can see the remnants of this old name in the documentation – many of the document URLs start with “long”. Also, note the title of this slide deck by an Oracle product manager: “Long Transactions with Oracle Database Workspace Manager Feature”.
The features of OWM only affect the tables that you explicitly Enable Versioning on via the API (DBMS_WM).
Limitations/Restrictions of Workspace Manager
If you are looking into using OWM, you must read the Intro to Workspace Manager in the docs. Make sure to review the restrictions that apply, such as:
Each version-enabled table must have a primary key.
If a parent table is version-enabled, each child table must also be version-enabled (but the opposite does not apply).
Referential integrity constraints MUST refer to the primary key in the parent table.
Primary key values in a parent table cannot be updated.
Only row-level triggers are supported – no per-statement triggers.
SQL MERGE statements are not allowed (attempts to do so results in “ORA-01400 cannot insert NULL into WM_VERSION”).
RETURNING clause is not supported (for INSERT or UPDATE statements).
Row-level security policies (VPD) are not enforced during workspace operations (such as Merge Workspace, and Rollback to Savepoint).
Materialized Views on a version-enabled table can only use the REFRESH COMPLETE method; e.g. no FAST or ON COMMIT.
Table names cannot be longer than 25 characters.
Column names cannot be longer than 28 characters.
Trigger names cannot be longer than 27 characters.
Some reserved words and characters apply, e.g. column names cannot start with WM$ or WM_.
Most DDL operations cannot be run directly on a version-enabled table (see below).
There are some other restrictions, so make sure to review the intro document carefully and consider the ramifications of each limitation for your particular situation.
I’ve used this simple script to do some basic checks on a table before I enable it for versioning: check_table_for_owm.sql
Database schema changes made by OWM
Internally, when you version-enable a table (e.g. MYTABLE), OWM makes a number of changes to your schema as follows:
Renames MYTABLE to MYTABLE_LT, for internal use only
Adds a number of grants on the object to WMSYS and WM_ADMIN_ROLE
Adds some extra OWM-specific columns (with names prefixed with WM_) and indexes to MYTABLE_LT
Creates the view MYTABLE, for use by your application for querying and DML
Creates an INSTEAD OF trigger (owned by WMSYS) on the view to handle DML
Creates some other triggers on any parent tables for referential integrity
Creates one or two other tables (MYTABLE_AUX, and sometimes MYTABLE_LCK) for internal use only
Creates some other views (MYTABLE_CONF, MYTABLE_DIFF, MYTABLE_HIST, MYTABLE_LOCK, MYTABLE_MW) for application use where needed
Converts any triggers and VPD policies on the table to metadata and then drops them; these are managed by OWM from then on
Based on the grants that OWM gives to WMSYS (such as ON COMMIT REFRESH and QUERY REWRITE) I think the OWM tables are materialized views.
To change the structure of a version-enabled table, e.g. adding/modifying/removing a column, constraint, or trigger, the following steps must be done:
Call dbms_wm.BeginDDL('MYTABLE');
Make the changes to a special table called MYTABLE_LTS
Call dbms_wm.CommitDDL('MYTABLE');
Note that this will fail if the table happens to have any constraints or indexes that are longer than 26 characters – so keep this in mind when naming them.
One of the most pleasing features of OWM is that it is relatively idiot-proof; if you try to perform an operation that is not supported, OWM will simply raise an exception (e.g. “ORA-20061: versioned objects have to be version disabled before being dropped“) instead of leaving you with a mess to clean up. Therefore it’s generally safe to test your scripts by simply running them and seeing what happens. For example, try doing some damage to the underlying tables or views by changing columns or dropping them – OWM will stop you. I would never do this sort of experiment in a production environment, of course!
Column comments on a table that becomes version-enabled are not migrated automatically to the view. They do remain on the renamed table (MYTABLE_LT). You can add comments to the view columns (and you don’t need to execute the BeginDDL/CommitDDL procedures for this); the same applies to the TABLE_LT table as well. Note, however, that if you remove the versioning from the table the view is dropped along with its comments, so if you later re-enable versioning you might want to re-apply the column comments as well.
To copy/synchronize the column comments from the underlying MYTABLE_LT table to the MYTABLE view, I use this script: sync_comments_lt_to_view.sql.
Implementing Workspace Manager
For guidance on how to take advantage of Oracle Workspace Manager in an APEX application, I watched this video by Dan McGhan. He demonstrates it in a javascript application but the real work is all done on the database in PL/SQL, which can be used by any application environment including APEX.
These are some of the API calls that I’ve used to build OWM features into my application:
My APEX application already uses VPD (or Row Level Security) to provide a multi-tenant environment for a wide range of users. Groups of users are assigned to one or more Security Groups; when they login they are assigned to one Security Group at a time which is set in a Global Application Context. A VPD policy has been applied to most tables like this:
function vpd_policy
(object_schema in varchar2
,object_name in varchar2
) return varchar2 is
begin
return q'[security_group_id=sys_context('CTX','SECURITY_GROUP_ID')]';
end vpd_policy;
The Application Context is associated with the APEX session’s Client Identifier (e.g. JBLOGGS:16630445499603) so each page request will be executed within the correct VPD context. A procedure is called from the Post-Authentication Procedure Name on the authentication scheme which sets the SECURITY_GROUP_ID context variable.
According to the docs, OWM can work along with VPD. However, you need to be aware of a few considerations:
Row-level security policies are not enforced during workspace operations, including MergeWorkspace and RollbackToSP.
Row-level security policies must be defined on a number of views, not just the view for the version-enabled table.
Don’t apply policies to the underlying tables created by OWM.
You can add VPD policies to a table prior to version-enabling it and these will be handled correctly by OWM when you version-enable it. However, if I need to add VPD policies after a table has been version-enabled, this is the code I run:
declare
target_table varchar2(30) := 'MYTABLE';
begin
for r in (
select view_name
from user_views
where view_name in (target_table
,target_table||'_LOCK'
,target_table||'_CONF'
,target_table||'_DIFF'
,target_table||'_HIST'
,target_table||'_MW')
) loop
begin
dbms_rls.add_policy
(object_name => r.view_name
,policy_name => 'security_policy'
,policy_function => 'security_pkg.security_policy'
,update_check => true
,static_policy => true);
exception
when others then
if sqlcode != -28101 /*policy already exists*/ then
raise;
end if;
end;
end loop;
end;
It adds the policy to the base view, as well as the five specific associated views (mytable_LOCK, mytable_CONF, mytable_DIFF, mytable_HIST and mytable_MW). Similar code may be used to alter or drop policies.
Note: with relation to VPD policies, the documentation doesn’t specifically mention the Multi-Workspace (_MW) view. As far as I can tell, however, this is required – because when you version-enable a table, a VPD policy on a non-version-enabled table will be applied by Workspace Manager to this _MW view as well as the other views.
Since a user’s changes within a workspace will be restricted to data for their Security Group, a MergeWorkspace works just fine – even though technically the merge occurs across the entire table ignoring the VPD policy, the only changes we expect are those for the user’s Security Group.
However, Savepoints are different; like workspaces, they are a snapshot of the entire table; if you issue a RollbackToSP, it will ignore any VPD policies and rollback all data to the selected savepoint. This means that you can’t use RollbackToSP to give users the ability to rollback their data to a snapshot, while isolating their changes to their VPD context. For this reason I don’t use RollbackToSP in my application.
All together – OWM, VPD and APEX
The users of my application need to be able to create scenarios for people in their Security Group to make proposed changes, see how the changes affect the reports, and merge those changes back to the “live” version. To facilitate this, I create a table to list the workspaces, and apply the VPD policy to it so that users can only view and work with workspaces for their Security Group.
create table scenarios (
scenario_id number
default to_number(sys_guid()
,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
not null
,scenario_name varchar2(200) not null
,wm_workspace varchar2(30) not null
,security_group_id number
default sys_context('CTX','SECURITY_GROUP_ID')
not null
,constraint scenario_pk primary key (scenario_id)
,constraint scenario_uk unique (wm_workspace)
);
Each scenario has an internal ID (in this case, a surrogate key generated from a GUID), a user-specified name, and a name for the workspace. I didn’t use the user-entered name for the workspace name because (a) it must be unique across the database, and (b) it is limited to 30 characters.
After a user clicks the “Create Scenario” button and enters a name, the page process calls the following procedure (within the package security_pkg associated with the context):
procedure create_scenario
(scenario_name in varchar2
,keep_refreshed in boolean := false) is
l_wm_workspace varchar2(30);
begin
-- workspace name must be unique and <=30 chars
l_wm_workspace := to_basex
(to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
,36);
-- record the new scenario
insert into scenarios (scenario_name, wm_workspace)
values (create_scenario.scenario_name, l_wm_workspace);
-- create the workspace
dbms_wm.CreateWorkspace
(workspace => l_wm_workspace
,isrefreshed => keep_refreshed
,description => scenario_name
|| ' ('
|| sys_context('CTX','SECURITY_GROUP')
|| ')'
,auto_commit => false);
-- reset the savepoint, if required
dbms_session.clear_context
(namespace => 'CTX'
,attribute => 'WM_SAVEPOINT'
,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));
-- go to the workspace on the next page view
dbms_session.set_context
(namespace => 'CTX'
,attribute => 'WM_WORKSPACE'
,value => l_wm_workspace
,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));
end create_scenario;
In my APEX application Security Attributes, I have the following:
Initialization PL/SQL Code
security_pkg.init_apex_session;
Cleanup PL/SQL Code
security_pkg.cleanup_apex_session;
The procedures called above are as follows:
procedure wm_init is
begin
dbms_wm.GotoWorkspace(nvl(sys_context('CTX','WM_WORKSPACE')
,'LIVE'));
dbms_wm.GotoSavepoint(nvl(sys_context('CTX','WM_SAVEPOINT')
,'LATEST'));
end wm_init;
procedure init_apex_session is
begin
wm_init;
end init_apex_session;
procedure cleanup_apex_session is
begin
dbms_wm.GotoWorkspace('LIVE');
dbms_wm.GotoSavepoint('LATEST');
end cleanup_apex_session;
The effect of this is that for each page request, the user’s selected workspace and/or savepoint is activated, or if they have not yet chosen a workspace or savepoint, the “live” workspace and “latest” savepoint is selected (which are the defaults). At the end of each page request, the session is reset to the “live” workspace and “latest” savepoint.
“That makes two of us.”
Create a Snapshot
Here is my code to create a snapshot, using the Workspace Manager SavePoint feature:
procedure create_snapshot
(snapshot_name in varchar2) is
l_wm_workspace varchar2(30);
l_wm_savepoint varchar2(30);
begin
-- savepoint name must be unique and <=30 chars
l_wm_savepoint := to_basex
(to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
,36);
l_wm_workspace := nvl(sys_context(CTX,'WM_WORKSPACE'), 'LIVE');
-- record the snapshot
insert into snapshots
(snapshot_name
,wm_workspace
,wm_savepoint)
values
(create_snapshot.snapshot_name
,l_wm_workspace
,l_wm_savepoint);
-- create the savepoint
dbms_wm.CreateSavepoint
(workspace => l_wm_workspace
,savepoint_name => l_wm_savepoint
,description => snapshot_name
|| ' ('
|| sys_context(CTX,'SECURITY_GROUP')
|| ')'
,auto_commit => false);
end create_snapshot;
Go to a Scenario
This sets the context for the user’s session so that subsequent page requests will load the specified Workspace. Any DML the user performs on version-enabled tables will be private to the workspace.
procedure goto_scenario (scenario_name in varchar2) is
l_wm_workspace varchar2(30);
begin
-- retrieve the workspace name for the given scenario
select s.wm_workspace into l_wm_workspace
from scenarios s
where s.scenario_name = goto_scenario.scenario_name;
-- reset the savepoint, if required
dbms_session.clear_context
(namespace => 'CTX'
,attribute => 'WM_SAVEPOINT'
,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));
-- go to the workspace on the next page view
dbms_session.set_context
(namespace => 'CTX'
,attribute => 'WM_WORKSPACE'
,value => l_wm_workspace
,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));
end goto_scenario;
View a Snapshot
This sets the context for the user’s session so that subsequent page requests will be viewing the specified Savepoint. The version-enabled tables will be read-only; any DML on them will raise an exception.
procedure goto_snapshot (snapshot_name in varchar2) is
l_wm_workspace varchar2(30);
l_wm_savepoint varchar2(30);
begin
-- retrieve the details for the given snapshot
select s.wm_workspace
,s.wm_savepoint
into l_wm_workspace
,l_wm_savepoint
from snapshots s
where s.snapshot_name = goto_snapshot.snapshot_name;
-- set the workspace and savepoint on the next page request
dbms_session.set_context
(namespace => 'CTX'
,attribute => 'WM_WORKSPACE'
,value => l_wm_workspace
,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));
dbms_session.set_context
(namespace => 'CTX'
,attribute => 'WM_SAVEPOINT'
,value => l_wm_savepoint
,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));
end goto_snapshot;
Go to Live
When the user is in a Scenario, and they wish to go back to “Live”, they can click a button which executes the following procedure. Their next page request will go to the LATEST savepoint in the LIVE workspace.
procedure goto_live is
begin
dbms_session.clear_context
(namespace => 'CTX'
,attribute => 'WM_WORKSPACE'
,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));
dbms_session.clear_context
(namespace => 'CTX'
,attribute => 'WM_SAVEPOINT'
,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));
end goto_live;
Go to Latest
When the user is viewing a Snapshot, and they wish to go back to “Latest” (so they do DML, etc.), they can click a button which executes the following procedure. This works regardless of whether they are in the Live workspace or viewing a scenario. Their next page request will go to the LATEST savepoint.
procedure goto_latest is
begin
dbms_session.clear_context
(namespace => 'CTX'
,attribute => 'WM_SAVEPOINT'
,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));
end goto_latest;
Compare two Scenarios
When the user wishes to analyze the differences between two scenarios, or between a scenario and the Live workspace, there is a two-step process:
The user selects two Scenarios (or the “Live” workspace) from some select lists; the return value of these select lists should be the wm_workspace column from the table, or 'LIVE' for the Live workspace.
The user clicks a button to execute the find_diffs1 procedure (see below).
The page has a report on the view mytable_DIFF.
procedure find_diffs1
(wm_workspace1 in varchar2
,wm_workspace2 in varchar2) is
begin
dbms_wm.SetDiffVersions
(workspace1 => wm_workspace1
,workspace2 => wm_workspace2
,onlyModified => true);
end find_diffs1;
Compare Snapshots
When the user wishes to analyze the differences between two snapshots (in any workspace), or between the latest data and a snapshot, a similar process is used:
The user selects two Snapshots (or the “Latest” snapshot for either the Live workspace or a given Scenario); the return values of these select lists should be the wm_workspace and wm_savepoint, e.g. wm_workspace||':'||wm_savepoint.
The user clicks a button to execute the find_diffs2 procedure (see below).
The page has a report on the view mytable_DIFF.
procedure find_diffs
(workspace_savepoint1 in varchar2
,workspace_savepoint2 in varchar2) is
l_sep1 number := instr(workspace_savepoint1,':');
l_sep2 number := instr(workspace_savepoint2,':');
begin
dbms_wm.SetDiffVersions
(workspace1 => substr(workspace_savepoint1, 1, l_sep1-1)
,savepoint1 => substr(workspace_savepoint1, l_sep1+1)
,workspace2 => substr(workspace_savepoint2, 1, l_sep2-1)
,savepoint2 => substr(workspace_savepoint2, l_sep2+1)
,onlyModified => true);
end find_diffs;
Merge a Scenario
In my system, I don’t allow users to create scenarios within scenarios, although this is possible with Workspace Manager. If the user wishes to merge all changes in a Scenario to Live, they click a button to execute the following procedure:
wm_error_55 exception;
wm_error_56 exception;
pragma exception_init(wm_error_55, -20055);
pragma exception_init(wm_error_56, -20056);
procedure merge_scenario is
l_wm_workspace varchar2(30);
begin
l_wm_workspace := sys_context('CTX','WM_WORKSPACE');
goto_live;
wm_init;
-- merge the workspace
dbms_wm.MergeWorkspace
(workspace => l_wm_workspace
,remove_workspace => true
,auto_commit => false);
-- delete the scenario
delete scenarios s
where s.wm_workspace = merge_scenario.scenario_id;
exception
when wm_error_55 or wm_error_56 then
-- unable to merge due to conflicts
-- go back into the workspace
dbms_session.set_context
(namespace => 'CTX'
,attribute => 'WM_WORKSPACE'
,value => l_wm_workspace
,client_id => sys_context('userenv','CLIENT_IDENTIFIER'));
-- caller should redirect user to the "resolve conflicts" page
raise e_merge_conflict;
end merge_scenario;
This will fail with an exception if there are any conflicting changes in the Live workspace, e.g.:
a record was updated in the scenario, but was deleted in Live
a record was updated or deleted in the scenario, but also updated in Live
a record was inserted in the scenario, but another record with the same PK was inserted in Live
If the workspace had been created with the isrefreshed option, the changes being made in Live will be automatically copied to the workspace so these conflicts should be minimised; however, conflicts cannot always be avoided. To give the user the ability to analyze the conflicts, you would perform the following steps:
Allow the user to select an action for each record in conflict – either “PARENT” (keep the parent, i.e. discard the change in the scenario) or “CHILD” (keep the child, i.e. discard the change made in Live).
For each record, call dbms_wm.ResolveConflicts (see example below).
The ResolveConflicts procedure takes a where clause that identifies the row (or rows) to mark as resolved. In my case, I just call it with the id for each record the user chose:
for r in (
...query on something, e.g. an APEX collection...
) loop
dbms_wm.ResolveConflicts
(workspace => sys_context('CTX','WM_WORKSPACE')
,table_name => 'MYTABLE'
,where_clause => 'id=' || r.id
,keep => r.action /*'PARENT' or 'CHILD'*/);
end loop;
If the conflicts are across multiple tables, the UI will probably be a bit more complicated. You’d have to resolve conflicts on all the affected tables before the Merge can succeed.
“Don’t let a white fence and a promotion end the world for you.”
Delete a Scenario
Deleting a scenario uses RemoveWorkspace:
procedure delete_scenario
(scenario_name in varchar2) is
l_wm_workspace varchar2(30);
begin
-- retrieve the workspace name for the given scenario
select s.wm_workspace into l_wm_workspace
from scenarios s
where s.scenario_name = delete_scenario.scenario_name;
-- get out of the workspace
goto_live;
wm_init;
-- delete the workspace
dbms_wm.RemoveWorkspace
(workspace => l_wm_workspace
,auto_commit => false);
delete scenarios s
where s.wm_workspace = l_wm_workspace;
end delete_scenario;
Delete a Snapshot
Deleting a snapshot uses DeleteSavepoint:
procedure delete_snapshot
(snapshot_name varchar2) is
l_wm_workspace varchar2(30);
l_wm_savepoint varchar2(30);
begin
-- retrieve the details for the given snapshot
select s.wm_workspace
,s.wm_savepoint
into l_wm_workspace
,l_wm_savepoint
from snapshots s
where s.snapshot_name = delete_snapshot.snapshot_name;
-- get out of the snapshot/scenario:
goto_latest;
wm_init;
-- delete the savepoint
dbms_wm.DeleteSavepoint
(workspace => nvl(l_wm_workspace,'LIVE')
,savepoint_name => l_wm_savepoint
,auto_commit => false);
delete snapshots s
where s.wm_savepoint = l_wm_savepoint;
end delete_snapshot;
Row History
One of the requirements of my application was to show a report of the entire history of edits to each record in a table. Since I’m already going to version-enable this table, it makes sense to take advantage of the Row History feature of Oracle Workspace Manager.
When you version-enable a table, OWM creates a view called MYTABLE_HIST which includes all the columns of the table, plus the following columns: WM_WORKSPACE, WM_VERSION, WM_USERNAME, WM_OPTYPE, WM_CREATETIME, and WM_RETIRETIME. By default, when you version-enable a table, OWM keeps only a minimal set of history in order to support other features in use, such as snapshots. In order to retain a complete history of changes to the record, enable versioning with the View Without Overwrite history option:
begin
dbms_wm.EnableVersioning('MYTABLE'
,hist => 'VIEW_WO_OVERWRITE');
end;
This stops OWM from overwriting the history of changes to each record, so it can be queried via the _HIST view.
Now, I wanted to expose the contents of this view to my users, and for each history record show Who did it and When. “When” is easily answered by WM_CREATETIME, which is a TIMESTAMP WITH TIME ZONE. “Who” would normally be answered by WM_USERNAME, but since we’re in APEX, this will always be 'APEX_PUBLIC_USER' which is not very useful. Therefore, I have an ordinary column in my table called DB$UPDATED_BY which is set by the following ordinary trigger:
create trigger MYTABLE$TRG
before update on MYTABLE
for each row
begin
:new.db$updated_by :=
coalesce(sys_context('APEX$SESSION','APP_USER')
,sys_context('USERENV','SESSION_USER'));
end MYTABLE$TRG;
This means my report will show the actual APEX username of who created or updated the record.
The WM_OPTYPE column in the _HIST view will be 'I' (inserted), 'U' (updated), or 'D' (deleted). I can translate this code into user-friendly values for reporting purposes, but there is one problem: when a row is deleted, the _HIST table knows the username who deleted the record, but we never recorded the APEX username. Our db$updated_by column wasn’t changed, so it simply shows whoever last updated the records before it was deleted.
To solve this, we have two options: (1) Write the application to do a quick no-change UPDATE on each row immediately before deleting it; then do some fancy footwork in the view to show this as a “Delete” operation. (2) Don’t allow the application to execute actual DELETEs – in other words, use a “soft-delete” design.
I’ve gone with option (2) which is what the users needed anyway – they needed to be able to Delete and Undelete records at will, so I’ve only granted INSERT and UPDATE on the table and the application will translate a request to “DELETE” into an update of a “deleted” flag on the table. A request to Undelete a record is simply another update to set the “deleted” flag to NULL. All reports in the application have a predicate to filter out any deleted records unless the user is viewing the “recycle bin”. In addition, if the user is viewing a deleted record, I put the form into “Readonly” mode – the only thing they can do is Undelete it (thereafter, they can modify the record if they wish).
I’ve created the following view in order to show the history of changes to a record:
create or replace view mytable_hist_vw as
select x.id, x.name, etc.
,l.wm_createtime as op_timestamp
,case
when x.wm_optype = 'U'
and x.deleted_ind = 'Y'
and lag(x.deleted_ind,1)
over (partition by x.id
order by x.wm_createtime) is null
then 'Deleted'
when x.wm_optype = 'U'
and x.deleted_ind is null
and lag(x.deleted_ind,1)
over (partition by x.id
order by x.wm_createtime) = 'Y'
then 'Undeleted'
when x.wm_optype = 'I' then 'Created'
when x.wm_optype = 'U' then 'Updated'
when x.wm_optype = 'D' then 'Deleted permanently'
end as op_desc
,case
when x.wm_optype = 'D'
then x.wm_username
else x.db$updated_by
end as op_by
from mytable_hist x
I interpret an WM_OPTYPE of 'D' as “Deleted permanently”. This might occur if we do a delete directly on the table, e.g. via a data fix run directly on the database. In this case I report the username according to the wm_username column since we won’t have the APEX user name.
If you need to send almost any message to almost any phone from your Oracle database, and you want to use straight PL/SQL, you may want to consider using my Clicksend API.
SMS (Short Message Service)
MMS (Multimedia Message Service)
Text to Voice
I have released the first beta version of my Oracle PL/SQL API for Clicksend. Read the installation instructions, API reference and download the release from here:
Sending an SMS is as simple as adding this anywhere in your code:
begin
clicksend_pkg.send_sms
(p_sender => 'TheDatabase'
,p_mobile => '+61411111111'
,p_message => 'G''day, this is your database!'
);
clicksend_pkg.push_queue;
commit;
end;
All you need to do is signup for a Clicksend account. You’ll only be charged for messages actually sent, but they do require you to pay in advance – e.g. $20 gets you about 300 messages (Australian numbers). You can get test settings so that you can try it out for free.
I’ve been using Clicksend for years now, and have been satisfied with their service and the speed and reliability of getting messages to people’s mobiles. When I encountered any issues, a chat with their support quickly resolved them, and they were quick to offer free credits when things weren’t working out as expected.
If you want to send a photo to someone’s phone via MMS (although I’m not sure what the use-case for this might be), you need to first upload the image somewhere online, because the API only accepts a URL. In my case, I would use the Amazon S3 API from the Alexandria PL/SQL Library, then pass the generated URL to the clicksend API. There is a file upload feature that ClickSend provides, I plan to add an API call to take advantage of this which will make this seamless – and provide some file conversion capabilities as well.
begin
clicksend_pkg.send_mms
(p_sender => 'TheDatabase'
,p_mobile => '+61411111111'
,p_subject => 'G''Day!'
,p_message => 'This is an MMS from your database!'
,p_media_file_url =>
'http://s3-ap-southeast-2.amazonaws.com/jk64/jk64logo.jpg'
);
clicksend_pkg.push_queue;
commit;
end;
You can send a voice message to someone (e.g. if they don’t have a mobile phone) using the Text to Voice API.
begin
clicksend_pkg.send_voice
(p_phone_no => '+61411111111'
,p_message => 'Hello. This message was sent from your database. '
|| 'Have a nice day.'
,p_voice_lang => 'en-gb' -- British English
,p_voice_gender => 'male'
,p_schedule_dt => sysdate + interval '2' minute
);
clicksend_pkg.push_queue;
commit;
end;
You have to tell the API what language the message is in. For a number of languages, you can specify the accent/dialect (e.g. American English, British English, or Aussie) and gender (male or female). You can see the full list here.
All calls to the send_sms, send_mms and send_voice procedures use Oracle AQ to make the messages transactional. It’s up to you to either COMMIT or ROLLBACK, which determines whether the message is actually sent or not. All messages go into a single queue.
You can have a message be scheduled at a particular point in time by setting the p_schedule_dt parameter.
The default installation creates a job that runs every 5 minutes to push the queue. You can also call push_queue directly in your code after calling a send_xxx procedure. This creates a job to push the queue as well, so it won’t interfere with your transaction.
All messages get logged in a table, clicksend_msg_log. The log includes a column clicksend_cost which allows you to monitor your costs. To check your account balance, call get_credit_balance.
Please try it out if you can and let me know of any issues or suggestions for improvement.
