Help Convert LONG/LONG RAW Data into ASCII File

[HimanB]

Dear All,
I am supposed to work on archival of Oracle based data stored in Long/Long
Raw
type of columns.
The data stored is PDF file.
This is to be archived in a utility/system called "OnDemand Server" from
IBM.
For archival the utility/system needs the file in ASCII form.
If required then this ASCII file is again to be leaded back to DB in
LONG/LONG RAW form.

Can be done?If yes how?

Regards
Himanshu

 

[martincab]

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.testrawiorawparam);


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

 

[HimanB]

Dear Martin,
Thanks for your response.
But I do not think this will help.I have PDF files stored in the LAONG RAW coulmns and not the TEXT strings.

Length of samllest Data is about 2398877.
As for Import/expot, this is also known to me but the requirement is very specific here.My client's wants an ASCII file to be submitted to its Archival server.

If you have any other idea then please let me know.

And thanks again for your valuable suggestion.

Regards
Himanshu