convert long raw into varchar2 data type in oracle 8i

If you have access log in to SQL Plus and run this sql

CREATE OR REPLACE PACKAGE TEXTRAWIO
  IS

  FUNCTION hextonum(v_hex varchar2) return number;

  FUNCTION tidblobchar(v_object_id char) RETURN VARCHAR2;

END; -- Package Specification TEXTRAWIO

CREATE OR REPLACE PACKAGE BODY textrawio
AS

  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;

  FUNCTION tidblobchar(v_object_id char) RETURN VARCHAR2

  -- This function converts the long raw column of TIDBLOB to
  -- its character representation for manipulation.
  -- It can only be called for an object type of T.

  IS
    raw_data          LONG RAW;
    rawlen            NUMBER;
    hex_data          VARCHAR2(32760);
    char_data         VARCHAR2(32760);
    loop_counter      NUMBER;
  BEGIN
 
    -- Get the blob using the rowid passed in.
    -- Will always retrun only one row.
   
    SELECT ole_object_blob
      INTO raw_data
      FROM tidblob
     WHERE v_object_id = rowid;

    -- Convert the raw data to hex.    
    hex_data := rawtohex(raw_data);
    rawlen := length(hex_data);

    loop_counter := 1;

    -- Loop through and convert the hex to characters.
    WHILE loop_counter <= rawlen LOOP
        char_data := char_data || CHR(HEXTONUM(SUBSTRB(hex_data,loop_counter,2)));
        loop_counter := loop_counter + 2;
    END LOOP;

    RETURN char_data;
  END;

END;
/



Then your sql call will look like this

1  select  OLE_OBJECT_ID,
2        OLE_OBJECT_TYPE,
3         textrawio.tidblobchar(rowid) OLE_OBJECT_BLOB,  
4          GEN_ARG,    
5         TIME_STAMP  
6    from tidblob
7  where ole_object_type = 'T'

I added the name of the package to line 3.