It’s a really bad thing to do, but that’s ok because “we put comments in that say it’s bad”.
PROCEDURE insert_stats IS
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
INSERT INTO stats_table ...
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL; --yeah this is a bad thing to do, bla bla bla...
END insert_stats;
The idea is that we want to gather some stats about user behaviour, but we are not allowed to interrupt the user’s important work with any unexpected error that might be raised due to the gathering of those stats.
This post is not about why that’s a bad thing – others have made very good points about this practice elsewhere.
What I want to write about is the exception handler here – does it really protect the caller from exceptions raised by this procedure? The answer is, no. Why?
SQL> CREATE PROCEDURE test_handler AS
PRAGMA AUTONOMOUS_TRANSACTION;
n NUMBER;
BEGIN
INSERT INTO stats_table (id) VALUES (0);
dbms_output.put_line('inserted=' || SQL%ROWCOUNT);
n := 1 / 0; -- fail...
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('handled: ' || SUBSTR(SQLERRM,1,4000));
--"silly thing to do but at least we're safe, right?"
END test_handler;
/
Procedure created.
SQL> BEGIN test_handler; END;
/
inserted=1
handled: ORA-01476: divisor is equal to zero
BEGIN test_handler; END;
*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
The divide-by-zero is just there to simulate an exception that is raised after the insert succeeds, but before (or during) the COMMIT. As soon as the transaction is started, the procedure must raise ORA-06519 unless a COMMIT or ROLLBACK succeeds. Lesson to learn? An autonomous transaction will raise ORA-06519 to the caller, and it will not be caught by “WHEN OTHERS”. To get around this we could put a ROLLBACK in the exception handler.
Jeffrey Kemp
4 August 2008
PL/SQL /
I used to assume that whenever a TOO_MANY_ROWS exception is raised, the target bind variables would be left untouched. Until today I’ve never written any code that relies on the bind variables being in any particular state when a TMR exception is raised, so was surprised.
For example, given the code below, I would expect the dbms_output to indicate that v is null:
CREATE PROCEDURE proc (v OUT NUMBER) IS
BEGIN
SELECT 1 INTO v FROM all_objects;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line
('TOO MANY ROWS: v='
|| v);
END
/
DECLARE
v NUMBER;
BEGIN
proc(v);
dbms_output.put_line('AFTER: v=' || v);
END
/
TOO MANY ROWS: v=1
AFTER: v=1
What appears to happen is that the out bind variables will be assigned values from the first row returned from the query; then when a second row is found, the TOO_MANY_ROWS exception is raised.
According to the documentation (emphasis added):
“By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row.”
(Oracle Database PL/SQL User’s Guide and Reference (10gR2): SELECT INTO Statement)
So it appears my original stance (don’t assume anything about the variables’ state after TOO_MANY_ROWS is raised) was correct. Lesson learned: beware of performing a SELECT INTO directly on the OUT parameters of your procedure!
Jeffrey Kemp
10 March 2008
PL/SQL /
Let’s just call it Legacy because I’m not going to say what the source is. It is a single-user desktop application that after a little investigation (i.e. searching the online forum for the app) was found to be storing its data in tables readable by MS Access. I wanted to get at this data, analyze it, maybe do some smart things to it, and then present it via Apex.
Step 1: Get the data into Oracle.
Simple matter of exporting from MS Access via ODBC. At least, it was simple once I replaced the Oracle ODBC drivers with the latest download from OTN. Before that I was getting a number of annoying TNS errors.
Step 2: Transform the data.
Most of the tables are easy-to-understand normalized relational tables. One of them, however, has a column that came through as a CLOB containing strange values like this:
{\rtf1\ansi\deff0\deftab254
{\fonttbl{\f0\fnil\fcharset0 Arial;}
{\f1\fnil\fcharset0 Verdana;}}{\colortbl\red0\green0\blue0;\red255\green0\blue0;\red0\green128\blue0;\red0\green0\blue255;\red255\green255\blue0;\red255\green0\blue255;\red128\green0\blue128;\red128\green0\blue0;\red0\green255\blue0;\red0\green255\blue255;\red0\green128\blue128;\red0\green0\blue128;\red255\green255\blue255;\red192\green192\blue192;\red128\green128\blue128;\red255\green255\blue255;}
\paperw12240\paperh15840\margl1880\margr1880\margt1440\margb1440
{\*\pnseclvl1\pnucrm\pnstart1\pnhang\pnindent720
{\pntxtb}{\pntxta{.}}}
...
Now based on my knowledge of the application I knew that this column was used to store small pieces of text (typically 8 to 30 short lines), with some amount of formatting (e.g. fonts, alignment, etc.). Again the online forum came in useful in that a side comment from one of the developers (regarding a small bug undocumented anti-feature) revealed that they stored the formatted text as RTF – Rich Text Format. Should have known from the opening 6 bytes in the data.
Somewhere in these oceans of rtf codes were swimming the plain text I craved. So Googled RTF, skimmed this old RTF specification, and ended up with this admittedly poorly-performing PL/SQL, which for the 651 rows in this table, each with an RTF of average 3KB, works just well enough for my purposes. As it turned out the only RTF codes I was interested in were \fcharset and \*, both of which I used to ignore bits of text I didn’t want in my output. Oh and \par, which denotes the end of a paragraph. I can run this script once a month on the freshly exported data and apply the full weight of Oracle’s analytic capabilities on it.
The code below exemplifies the use of a pipelined function. This is not a good idea, by the way, if you want to use it in regular queries, e.g. a view. In my case, however, I only wanted to call this from within PL/SQL, and then only once a month. Because of the way parameters work with functions like this, I had to call it with dynamic SQL (execute immediate).
That was kind of fun, but I’d rather not have to deal with RTF ever again, thank you.
create or replace package myutil_rtf is
type t_v4000_table is table of varchar2(4000);
function extract_text (p_recid in number)
return t_v4000_table pipelined deterministic;
end;
/
create or replace package body myutil_rtf is
function extract_text (p_recid in number)
return t_v4000_table pipelined deterministic is
l_ch varchar2(1);
l_ctrl varchar2(4000);
l_line varchar2(4000);
l_rtf clob;
--don't output any text between
--\fcharset and closing }
l_fcharset boolean := false;
--increments for each enclosed pair of { }
--within a discard section
l_discard number;
begin
select rtf_clob into l_rtf
from rtf_table where recid = p_recid;
for i in 1..dbms_lob.getlength(l_rtf) loop
l_ch := substr(l_rtf,i,1);
if l_ch = '}' then
if l_fcharset then
--closing } found; re-enable output
l_fcharset := false;
l_line := null;
end if;
if l_discard > 0 then
l_discard := l_discard - 1;
if l_discard = 0 then
l_discard := null;
end if;
end if;
elsif l_ch = '{' then
if l_discard is not null then
l_discard := l_discard + 1;
end if;
elsif l_ch = '\' then
--controls start with a backslash
l_ctrl := '\';
elsif l_ctrl is not null then
--controls are always ended by some
--non-alphanumeric character
if instr('abcdefghijklmnopqrstuvwxyz'
|| '0123456789',lower(l_ch)) > 0 then
l_ctrl := l_ctrl || lower(l_ch);
else
if l_ctrl = '\par' then
pipe row (l_line);
l_line := null;
elsif substr(l_ctrl,1,9) = '\fcharset' then
l_fcharset := true;
elsif l_ctrl || l_ch = '\*' then
--{\* ... } means you can ignore
--anything between the { }
if l_discard is null then
l_discard := 1;
end if;
end if;
l_ctrl := null;
end if;
elsif l_ch not in (chr(10), chr(13), '{')
and not l_fcharset and l_discard is null then
l_line := l_line || l_ch;
end if;
end loop;
if l_line is not null and not l_fcharset then
pipe row (l_line);
end if;
return;
end extract_text;
end myutil_rtf;
/
To extract the text from the table with recid=1:
select column_value line_of_text
,rownum line_number
from table(myutil_rtf.extract_text(1));
I was annoyed that to get to my main PL/SQL page I have to type in a full URL like this:
http://host:7777/mydad/home
(e.g. “mydad” could be “apex” for Oracle Apex)
If I was using Apache HTTP Server I’d do something this article suggests. But I’m using Oracle’s Embedded PL/SQL Gateway.
A. I got rid of the “:7777” by changing the HTTP port to 80, e.g.:
SQL> exec dbms_xdb.sethttpport(80);
Now, I can get to it without the port number:
http://host/mydad/home
B. Now I want to remove the need to remember to type “home”. To do this, I just tell the DAD what the default page is:
SQL> exec dbms_epg.set_dad_attribute('MYDAD','default-page','home');
Now, the url is a bit simpler:
http://host/mydad
The URL is now rewritten automatically to point to “mydad/home”.
C. Finally, I want to remove the need to specify the DAD. To do this is a little more complicated. I’ll create an XDB resource that will override the default XDB navigator that comes up.
-
- Log into Enterprise Manager
-
- Open the “Administration” tab and select “Resources” under “XML Database”
-
- Click “Create” and set the fields as follows:
Owner = SYS
Name = index.html
Location = /
Type = XML Database Resource File
Source = Specify the file contents
Contents =
<html><head><meta http-equiv="REFRESH" content="0; URL=http://host/mydad"></head><body><a href="http://host/mydad">Home</a></body></html>
- Click “Ok”
(you’ll need to change “host” and “mydad” to appropriate values in the sample Contents above)
Now, the url is simply:
http://host
This causes it to load the index.html page from the XML database, which redirects to the DAD, the default page for which is “home”.
I came across an inexplicable error when bulk collecting into a PL/SQL table with the NOT NULL constraint the other day. What was confusing was that the code had been passing tests for quite some time.
In the end the only thing that had changed was that a VARCHAR2 which should have been non-null happened to be NULL for one particular row in the table.
Thanks to Connor for the simple test case, listed below.
If you know what might be the cause or reason behind this error, and why it doesn’t occur for dates, I’d be interested.
This was reproduced on Oracle 10.2.0.1.0.
SQL> declare
type t is table of number not null index by pls_integer;
r t;
begin
select case when rownum < 20 then rownum else null end
bulk collect into r from all_Objects
where rownum <= 20;
end;
/
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
ORA-06512: at line 5
SQL> declare
type t is table of varchar2(80) not null index by pls_integer;
r t;
begin
select case when rownum < 20 then rownum else null end
bulk collect into r from all_Objects
where rownum <= 20;
end;
/
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
ORA-06512: at line 5
SQL> declare
type t is table of date not null index by pls_integer;
r t;
begin
select case when rownum < 20 then sysdate else null end
bulk collect into r from all_Objects
where rownum <= 20;
end;
/
PL/SQL procedure successfully completed.
This is a list of all the table types I’ve found in Oracle-supplied packages, e.g. OWA_UTIL in 10g supplies the type:
TYPE datetype IS TABLE OF varchar2(10) INDEX BY binary_integer;
I find them handy for quick one-off scripts.
Oracle_supplied_table_types
Jeffrey Kemp
27 November 2006
PL/SQL /
Came across this in a form (6i) to be run on a 9i db. Not only is this code about 33 lines of code too long and issues any number of unnecessary database queries, its name is quite unrelated to its intended function. Needless to say it was easily replaced with a single call to INSTR.
PROCEDURE alpha_check
(ref_in IN VARCHAR2
,ref_out OUT VARCHAR2) IS
-- Procedure included to distinguish
-- ref_in between ID or reference.
l_alpha_char VARCHAR2 (1);
l_alpha_pos NUMBER;
l_found_pos NUMBER;
l_search_string VARCHAR2 (100) := ' ';
CURSOR cur_get_next_alpha(N NUMBER) IS
SELECT SUBSTR(l_search_string,N,1)
FROM dual;
CURSOR cur_check_for_alpha(C VARCHAR2)IS
SELECT INSTRB(ref_in,C, 1)
FROM dual;
BEGIN
IF ref_in IS NULL THEN
ref_out := 'X';
RETURN;
END IF;
FOR I IN 1..LENGTH(l_search_string) LOOP
OPEN cur_get_next_alpha(I);
FETCH cur_get_next_alpha
INTO l_alpha_char;
CLOSE cur_get_next_alpha;
FOR J IN 1..LENGTH(ref_in) LOOP
OPEN cur_check_for_alpha(l_alpha_char);
FETCH cur_check_for_alpha
INTO l_found_pos;
CLOSE cur_check_for_alpha;
IF l_found_pos > 0 THEN
ref_out := 'N';
RETURN;
END IF;
END LOOP;
END LOOP;
ref_out := 'Y';
EXCEPTION
WHEN OTHERS THEN
pc_ref_out := 'X';
END;
Looks like it may have been copied from the same source as “As bad as it gets”.
PL/SQL User’s Guide and Reference (10.2): “4 Using PL/SQL Control Structures – Using the NULL Statement”
“…Note that the use of the NULL statement might raise an unreachable code warning if warnings are enabled.”
Example 4-23 Using NULL as a Placeholder When Creating a Subprogram
CREATE OR REPLACE PROCEDURE award_bonus (emp_id NUMBER, bonus NUMBER) AS
BEGIN — executable part starts here
NULL; — use NULL as placeholder, raises “unreachable code” if warnings enabled
END award_bonus;
/
Indeed, when I compile the above in 10.2 with PL/SQL warnings on, I get PLW-06002 as expected (due to bug 3680132 I get “Message 6002 not found; No message file for product=plsql, facility=PLW” but at least I can look it up in the reference).
“PLW-06002: Unreachable code”
“Cause: Static program analysis determined that some code on the specified line would never be reached during execution.”
I agree that a PL/SQL warning would be desirable in the case where a procedure has nothing but a NULL in it (probably a stub). Correct me if I’m wrong, but if I were to call award_bonus, surely the NULL is “executed” – therefore, it is reachable! A more appropriate warning would be something like “function/procedure does nothing”, or “get back to work you silly mug, you’ve forgotten to finish the code”. Maybe they just couldn’t be bothered making up another warning code.
Jeffrey Kemp
11 February 2006
PL/SQL /
PL/SQL User’s Guide and Reference (9.2 and 10.2): “NULL Statement”
“The NULL statement and Boolean value NULL are unrelated.”
I can understand that this is to draw a distinction between “NULL” as a procedural statement and “NULL” as a literal. But why is NULL specifically identified as Boolean? So, the NULL statement is somehow related to NULL strings, NULL numbers, and NULL dates?
(I won’t even mention the problem with calling NULL a value, something which is prevalent throughout the literature, including the SQL standard.)
The application I’m working on involves a number of views that provide an interface between two databases that have quite different models. The views implement inserts, updates and deletes via Instead-of triggers.
On one of these views, the update trigger needs to actually do an update and an insert on the source table: it must update the existing row to mark it as “closed”, and then insert a new row to take its place. In this way the view hides the fact that the table keeps a history of changes.
Now, a logical constraint is that there can only be one “open” record for a given entity. Therefore, there can be zero or more “closed” records, and there must be a single “open” record. This constraint, however, has not been implemented on the database (naughty naughty) because our code is perfect and never makes a mistake 😉
This worked perfectly well until our thorough Java guy decided to do some load testing, in which a process would hammer away inserting, updating and deleting the same row over and over again, in multiple concurrent sessions.
This had the result that we ended up seeing lots of “open” records for the entity, which violates the logical constraint described above. “That’s not allowed” I said, and soon realised that the fact that multiple sessions are updating the same row means that even a trigger-based constraint check will not solve the problem, since the triggers cannot see updates from other sessions.
So, we need to have a lock of some sort, so that when one session wants to update an entity, other sessions cannot start updating it as well until the first one is finished. I can’t just lock the row I want to update, because after I’ve updated it, I still need the lock while I insert the new row.
The solution is to use a package supplied with Oracle calls DBMS_LOCK, with which I can request “user-named locks”. This package gives a lot of options but I’m only interested in simple Exclusive locks, so I’ll also create some wrapper procedures.
Easy, right? Well, yeah once you get past a few gotchas.
Try #1:
-- wrapper function to Request a lock; returns a lock handle
FUNCTION request_lock (lock_name IN VARCHAR2) RETURN VARCHAR2 IS
lock_status NUMBER;
lock_handle VARCHAR2(128);
BEGIN
DBMS_LOCK.ALLOCATE_UNIQUE (
lockname => lock_name,
lockhandle => lock_handle,
expiration_secs => 864000); -- 10 days
lock_status := DBMS_LOCK.REQUEST(
lockhandle => lock_handle,
lockmode => DBMS_LOCK.X_MODE, -- eXclusive
timeout => DBMS_LOCK.MAXWAIT, -- wait forever
release_on_commit => TRUE);
IF lock_status > 0 THEN
RAISE_APPLICATION_ERROR(-20000,'request_lock failed: ' || lock_status);
END IF;
RETURN lock_handle;
END request_lock;
-- wrapper to release a lock; call with the lock handle obtained previously
PROCEDURE release_lock (lock_handle IN VARCHAR2) IS
lock_status NUMBER;
BEGIN
lock_status := DBMS_LOCK.RELEASE(
lockhandle => lock_handle);
IF lock_status > 0 THEN
RAISE_APPLICATION_ERROR(-20000,'release_lock failed: ' || lock_status);
END IF;
END release_lock;
--example calling code
...
DECLARE
lock_handle VARCHAR2(128);
BEGIN
lock_handle := request_lock('MYLOCK1');
-- do some stuff
release_lock(lock_handle);
END;
...
The above code “worked” ok (although I hadn’t yet tested to see if it was actually getting the lock) until I started calling it from triggers on the views, when I started getting “ORA-04092: cannot commit in a trigger”. What the? I wasn’t doing any commits in my triggers. I couldn’t find any references to the DBMS_LOCK functions doing commits in the 10g documentation, but I thought, ok let’s make these wrappers Autonomous, so they can commit in peace:
Try #2:
FUNCTION request_lock (lock_name IN VARCHAR2) RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
lock_status NUMBER;
lock_handle VARCHAR2(128);
BEGIN
DBMS_LOCK.ALLOCATE_UNIQUE (
lockname => lock_name,
lockhandle => lock_handle,
expiration_secs => 864000); -- 10 days
lock_status := DBMS_LOCK.REQUEST(
lockhandle => lock_handle,
lockmode => DBMS_LOCK.X_MODE, -- eXclusive
timeout => DBMS_LOCK.MAXWAIT, -- wait forever
release_on_commit => TRUE);
IF lock_status > 0 THEN
RAISE_APPLICATION_ERROR(-20000,'request_lock failed: ' || lock_status);
END IF;
RETURN lock_handle;
END request_lock;
PROCEDURE release_lock (lock_handle IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
lock_status NUMBER;
BEGIN
lock_status := DBMS_LOCK.RELEASE(
lockhandle => lock_handle);
IF lock_status > 0 THEN
RAISE_APPLICATION_ERROR(-20000,'release_lock failed: ' || lock_status);
END IF;
END release_lock;
No good: I got “ORA-06519: active autonomous transaction detected and rolled back”! This didn’t make much sense at the time, but I put a COMMIT in anyway. But of course, this wouldn’t work with the triggers. Anyway, I started experimenting and soon realised that even if I got the wrappers to work, the locks weren’t being made anyway, and I was able to request locks multiple times, which I expected to be impossible if the wrapper was working correctly.
Finally, I logged in as a user with some DBA privileges and read the specification of the SYS.DBMS_LOCK package. The one thing that jumped out at me was a little comment under ALLOCATE_UNIQUE:
“This routine will always do a commit.”
There was my answer. It was the ALLOCATE_UNIQUE that was doing a commit, not REQUEST or RELEASE. And, because I was setting release_on_commit => TRUE in the call to REQUEST, whenever I committed the lock was being released, straight away.
So I changed release_on_commit => FALSE, and moved ALLOCATE_UNIQUE to a separate function (set up as an Autonomous Transaction). Having read a bit more I realised I could make this API nicer to the programmers by allowing the release_lock to accept the lock name instead of requiring them to manage the lock handle. I also improved the error messages.
Try #3:
-- internal function to get a lock handle
-- (private for use by request_lock and release_lock)
FUNCTION get_handle (lock_name IN VARCHAR2) RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
lock_handle VARCHAR2(128);
BEGIN
DBMS_LOCK.ALLOCATE_UNIQUE (
lockname => lock_name,
lockhandle => lock_handle,
expiration_secs => 864000); -- 10 days
RETURN lock_handle;
END get_handle;
PROCEDURE request_lock (lock_name IN VARCHAR2) IS
lock_status NUMBER;
BEGIN
lock_status := DBMS_LOCK.REQUEST(
lockhandle => get_handle(lock_name),
lockmode => DBMS_LOCK.X_MODE, -- eXclusive
timeout => DBMS_LOCK.MAXWAIT, -- wait forever
release_on_commit => FALSE);
CASE lock_status
WHEN 0 THEN NULL;
WHEN 2 THEN RAISE_APPLICATION_ERROR(-20000,'deadlock detected');
WHEN 4 THEN RAISE_APPLICATION_ERROR(-20000,'lock already obtained');
ELSE RAISE_APPLICATION_ERROR(-20000,'request lock failed: ' || lock_status);
END CASE;
END request_lock;
-- wrapper to release a lock
PROCEDURE release_lock (lock_name IN VARCHAR2) IS
lock_status NUMBER;
BEGIN
lock_status := DBMS_LOCK.RELEASE(
lockhandle => get_handle(lock_name));
IF lock_status > 0 THEN
RAISE_APPLICATION_ERROR(-20000,'release lock failed: ' || lock_status);
END IF;
END release_lock;
--example calling code
...
BEGIN
request_lock('MYLOCK1');
-- do some stuff
release_lock('MYLOCK1');
END;
...
This worked perfectly, even from triggers. I even tried it out with multiple sessions:
Session 1:
exec request_lock('JEFF');
Session 2:
exec request_lock('JEFF');
At this point, session 2 just hung – it was waiting for Session 1 to release the lock.
Session 1:
exec release_lock('JEFF');
Now session 2 returned and had the lock. The same thing happened if I just closed down session 1.
If I changed request_lock to set timeout to some value (e.g. 3 seconds), session 2 would wait for that amount of time, then raise my error message “request lock failed – 1” (1 is the status code when a lock request times out).
I tried it with a deadlock situation, with timeout = MAXWAIT:
Session 1:
exec request_lock('MYLOCKA');
Session 2:
exec request_lock('MYLOCKB');
Session 1:
exec request_lock('MYLOCKB');
At this point session 1 hangs as it waits for session 2 to release the lock.
Session 2:
exec request_lock('MYLOCKA');
This is a deadlock. Both sessions are waiting for a lock held by the other session. After a few seconds Oracle automatically detects the deadlock and picks one session (at random, apparently) and it returns the lock status 2 (my code raises the error message “deadlock detected”). The other session stayed hung, however – it was still waiting, but there was no deadlock.
I tried the above scenario again, but with timeout = 5 seconds. If I executed the request_locks quick enough, I got “deadlock detected” on one session, and the other session eventually timed out.
So, everything works as expected, and the Java guys can hammer away at my views with impunity.
—
UPDATE for Oracle 12.1 and later:
The get_handle function no longer needs to be autonomous, since DBMS_LOCK now provides an autonomous variant of ALLOCATE_UNIQUE:
FUNCTION get_handle (lock_name IN VARCHAR2) RETURN VARCHAR2 IS
lock_handle VARCHAR2(128);
BEGIN
DBMS_LOCK.ALLOCATE_UNIQUE_AUTONOMOUS (
lockname => lock_name,
lockhandle => lock_handle,
expiration_secs => 864000); -- 10 days
RETURN lock_handle;
END get_handle;
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_LOCK.html#GUID-0D6C0622-05CA-4259-8059-330D512E1574
(thanks to Kim Berg Hansen for the update)
