This can be done in many ways, depending upon the size of the
data. It can be done using OCI, PRO*C, IMPORT, SQL*Loader,
PL/SQL, SQL*Plus, and the COPY command.
here is a pl/sql sample:
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
Martin Cabrera
Oracle DBA/Programmer
