-
Help! ORACLE will not release the function that works to convert a hex to a raw number. They say it is private. In their example on finding the SCN and timestamp for a dropped table using logmnr, they do not show how the conversion of hex value "c3021f50' becomes the object id of "13079'.
I have not found a way to get that number either.
Any of you know how to do this? I have to find 6 tables that a COTS package dropped.
Thanks for the help.
-
IS it useful?????
Overview
--------
This article contains a fairly advanced sample which demonstrates how to
insert and select data from a LONG RAW column using PL/SQL.
Caution
-------
The sample program in this article is provided for educational purposes only
and is NOT supported by Oracle Support Services. It has been tested
internally, however, and works as documented. We do not guarantee that it
will work for you, so be sure to test it in your environment before relying
on it.
Program
-------
-- Table required by the testrawio package
CREATE TABLE testraw (col1 NUMBER, col2 LONG RAW);
/
CREATE OR REPLACE PACKAGE testrawio AS
-- Accepts a character string, places it into a
-- long raw variable, inserts it into the db,
-- selects it out of the db, converts the result
-- back into a character string
PROCEDURE testrawio(rawparam in out varchar2);
-- Offers conversions raw and char formats
FUNCTION chartoraw(v_char varchar2) return long raw;
FUNCTION rawtochar(v_raw long raw) return varchar2;
-- Offers conversions between decimal and hex format
FUNCTION numtohex(v_hex number) return varchar2;
FUNCTION hextonum(v_hex varchar2) return number;
END;
/
CREATE OR REPLACE PACKAGE BODY testrawio
as
PROCEDURE testrawio(rawparam in out varchar2)
is
rawdata long raw;
rawlen number;
outlen number;
hex varchar2(32760);
i number;
begin
dbms_output.put_line('Value In :' ||rawparam);
-- Get the length of the variable and convert it to a long raw
rawlen := length(rawparam);
rawdata := chartoraw(rawparam);
INSERT INTO testraw VALUES (rawlen, rawdata);
COMMIT;
SELECT col1, col2 INTO outlen, rawdata FROM testraw;
-- Reset varaiable to nothing and buffer the converted long raw in it
rawparam := '';
rawparam := rawtochar(rawdata);
dbms_output.put_line('Value Out:' ||rawparam);
if outlen = length(rawparam) then
dbms_output.put_line('All bytes retrieved');
else
dbms_output.put_line('Checksum failed');
end if;
end;
FUNCTION chartoraw(v_char varchar2) return long raw
is
rawdata long raw;
rawlen number;
hex varchar2(32760);
i number;
begin
rawlen := length(v_char);
i := 1;
while i <= rawlen
loop
hex := numtohex(ascii(substrb(v_char,i,1)));
rawdata := rawdata || HEXTORAW(hex);
i := i + 1;
end loop;
return rawdata;
end;
FUNCTION rawtochar(v_raw long raw) return varchar2
is
rawlen number;
hex varchar2(32760);
rawparam varchar2(32760);
i number;
begin
hex := rawtohex(v_raw);
rawlen := length(hex);
i := 1;
while i <= rawlen
loop
rawparam := rawparam||CHR(HEXTONUM(substrb(hex,i,2)));
i := i + 2;
end loop;
return rawparam;
end;
FUNCTION numtohex(v_hex number) return varchar2
is
hex varchar2(4);
num1 number;
num2 number;
begin
num1 := trunc(v_hex/16);
num2 := v_hex-(num1*16);
if ( num1 >= 0 and num1 <= 9 ) then
hex := hex||to_char(num1);
end if;
if num1 = 10 then hex := hex||'A'; end if;
if num1 = 11 then hex := hex||'B'; end if;
if num1 = 12 then hex := hex||'C'; end if;
if num1 = 13 then hex := hex||'D'; end if;
if num1 = 14 then hex := hex||'E'; end if;
if num1 = 15 then hex := hex||'F'; end if;
if ( num2 >= 0 and num2 <= 9 ) then
hex := hex||to_char(num2);
end if;
if num2 = 10 then hex := hex||'A'; end if;
if num2 = 11 then hex := hex||'B'; end if;
if num2 = 12 then hex := hex||'C'; end if;
if num2 = 13 then hex := hex||'D'; end if;
if num2 = 14 then hex := hex||'E'; end if;
if num2 = 15 then hex := hex||'F'; end if;
return hex;
end;
FUNCTION hextonum(v_hex varchar2) return number
is
hex varchar2(4);
num number;
num1 number;
num2 number;
begin
hex := substrb(v_hex,1,1);
if ( hex >= '0' and hex <= '9' ) then
num1 := to_number(hex);
end if;
if hex = 'A' then num1 := 10; end if;
if hex = 'B' then num1 := 11; end if;
if hex = 'C' then num1 := 12; end if;
if hex = 'D' then num1 := 13; end if;
if hex = 'E' then num1 := 14; end if;
if hex = 'F' then num1 := 15; end if;
hex := substrb(v_hex,2,1);
if ( hex >= '0' and hex <= '9' ) then
num2 := to_number(hex);
end if;
if hex = 'A' then num2 := 10; end if;
if hex = 'B' then num2 := 11; end if;
if hex = 'C' then num2 := 12; end if;
if hex = 'D' then num2 := 13; end if;
if hex = 'E' then num2 := 14; end if;
if hex = 'F' then num2 := 15; end if;
num := (num1*16)+num2;
return num;
end;
end;
/
-- This is meant to test our newly created
-- long raw manipulation package from
-- SQL*Plus!
set termout on
set serveroutput on
variable rawparam varchar2(100);
begin
:rawparam := 'This is a test of an insert and select from a long raw column!';
end;
/
truncate table testraw;
execute testrawio.testrawio(:rawparam);
Sample Output:
Value In :This is a test of an insert and select from a long raw column!
Value Out:This is a test of an insert and select from a long raw column!
All bytes retrieved
Sam
Thanx
Sam
Life is a journey, not a destination!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|