$ORACLE_HOME/webcache/examples/invalidation.sql sounds like a good idea, until:
begin
invalidate('hostname.com',9451,'password1','http://hostname.com/page')
end;
Error report:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.BASE64_ENCODE", line 51
ORA-06512: at "SCOTT.BASE64_ENCODE", line 57
ORA-06512: at "SCOTT.INVALIDATE", line 38
ORA-06512: at line 2
Uh-oh. The base64_encode function included in the script is having trouble with the password. A quick look at the code...
create or replace function base64_encode
(
p_value in varchar2
)
return varchar2 is
BASE64_KEY constant varchar2(64) :=
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
l_buffer varchar2(32767);
l_len integer := trunc(length(p_value) / 3);
...
begin
for i in 0..l_len loop
l_buffer := l_buffer || encode_chunk(substr(p_value, i * 3 + 1, 3));
end loop;
return l_buffer;
end base64_encode;
Note l_len division by 3, then using it in the for loop. Yep, classic 0/1 base offset issue. Any password with a length of 3, 6, 9 etc characters breaks the code. Fixed with a -1:
for i in 0..l_len - 1 loop
l_buffer := l_buffer || encode_chunk(substr(p_value, i * 3 + 1, 3));
end loop;
But that raises more questions. What is this base64 encoding function doing here anyway?
At some point in time it might have been required, but Oracle Database has had the standard function utl_encode.base64_encode for at least a few versions. It encodes RAW so there's a bit of friggin around with types:
select utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw('password1') ) ) as B64 from dual;
B64
-------------
cGFzc3dvcmQx
I did note the comment in invalidation.sql to the effect that:
-- this old example is replaced by 2 files, collectively known as the
-- PL/SQL invalidation API.
--
-- the 2 files are
--
-- wxvutil.sql which does similar things as what invalidate.sql did
-- wxvappl.sql which is a wrapper of wxvutil.sql
--
-- both files are located in the same directory.
Well, these files are not in the same directory (they are actually in ../toolkit), and what's the excuse for shipping broken examples anyway, even if they are old and obsolete?