DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: conversion of hex to raw

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    9
    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.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
  •  


Click Here to Expand Forum to Full Width