What do CRC, CRC32 and MD5 stand for?

CRC - cyclical reduncancy check
crc32 - cyclical reduncancy check 32 ( more accurate for large things)
MD5 - message digest (I think)

There's a workaround in pl/sql. A bitshift is logically the same as multiplying or dividing by a power of 2. So, you just need to convert the raw to a number, do the operation, and convert back to raw. The following PL/SQL seems to work well:

function bitShiftLeft(i_raw in raw, i_bits in number) return raw is
begin
return utl_raw.cast_from_binary_integer(utl_raw.cast_to_binary_integer(i_raw) * (2**i_bits));
end;

function bitShiftRight(i_raw in raw, i_bits in number) return raw is
begin
return utl_raw.cast_from_binary_integer(floor(utl_raw.cast_to_binary_integer(i_raw) / (2**i_bits)));
end;

Note a few things:
1) This won't really work well for large raws (more than a few bytes), since the number will get too large
2) This probably isn't as fast as a true c-style bitshift. I'm not really sure what oracle does internally to handle this operation, it might not be pretty
3) Both operations seem to left-pad the response with 0's, up to 4 bytes. Not sure why that is. That might pose a problem, depending on your application.

Can I use outln_edit_pkg.generate_signature API to implement CRC?

I had read it somewhere that in a data warehouse environment where you want to detect changes between the source and target environments, using CRC to do the comparison can increase the speed by up to 10 fold.

I've created a simple test case below to simulate this. However, the comparison using CRC is actually slower. Am I using it the way it is supposed to be used? Thanks.

By the way, I've run each of the SQL statements multiple times and the result is pretty much the same each time.

-- ----------------------------------------------------------------------
-- Imitating a CRC algorithm
-- ----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION f_get_sign (p_text VARCHAR2)
RETURN RAW
DETERMINISTIC
AS
l_signature RAW(32);
l_text2 VARCHAR2(32767);

BEGIN
l_text2 := p_text;

IF l_text2 IS NULL THEN
l_text2 := '1';
END IF;

sys.outln_edit_pkg.generate_signature(l_text2, l_signature);
RETURN l_signature;
END;
/

-- ----------------------------------------------------------------------
-- Create a reasonably "large" table.
-- ----------------------------------------------------------------------
DROP TABLE x;

CREATE TABLE x NOLOGGING AS
SELECT object_id, owner, object_name, subobject_name, object_type,
f_get_sign(owner || object_name ||
subobject_name || object_type) crc_signature
FROM all_objects;

ALTER TABLE x ADD PRIMARY KEY (object_id);

ANALYZE TABLE x COMPUTE STATISTICS;

SELECT COUNT(*) FROM x;

COUNT(*)
----------
40705


-- ----------------------------------------------------------------------
-- Straight comparison
-- ----------------------------------------------------------------------
set time on
set timing on

SELECT COUNT(*), change_indicator
FROM (
SELECT (CASE WHEN a.owner <> x.owner OR
a.object_name <> x.object_name OR
a.subobject_name <> x.subobject_name OR
a.object_type <> x.object_type
THEN 'MODIFIED'
ELSE 'INTACT'
END) change_Indicator
FROM all_objects a, x
WHERE a.object_id = x.object_id
)
GROUP BY change_indicator;

COUNT(*) CHANGE_I
---------- --------
40705 INTACT

Elapsed: 00:00:03.04

-- ----------------------------------------------------------------------
-- Using CRC algorithm
-- ----------------------------------------------------------------------
SELECT COUNT(*), change_indicator
FROM (
SELECT (CASE WHEN f_get_sign(a.owner || a.object_name ||
a.subobject_name || a.object_type) =
x.crc_signature
THEN 'INTACT'
ELSE 'MODIFIED'
END) change_Indicator
FROM all_objects a, x
WHERE a.object_id = x.object_id
)
GROUP BY change_indicator;

COUNT(*) CHANGE_I
---------- --------
40705 INTACT

Elapsed: 00:00:05.00

set time off
set timing off