Inserting .jpg pictures in Oracle...

[Plascio]

Hi All,
Does anyone know how I could store .jpg files in my Oracle 8i database. Just
know that I've got to used RAW datatype.
Any help to solve this problem, will be highly appreciated.

Thanking you,

Plascio

[Boris Milrud]

Plascio,

I believe Oracle reccommends to use BLOBs for storing large binary data.
Check on DBMS_LOB package on Oracle documentation: it has all the API's you
need.

Boris Milrud.

"Plascio" <plascio@yahoo.com> wrote:
>
>Hi All,
>Does anyone know how I could store .jpg files in my Oracle 8i database.
Just
>know that I've got to used RAW datatype.
>Any help to solve this problem, will be highly appreciated.
>
>Thanking you,
>
>Plascio

[Amro]

"Overview
--------

This program demonstrates how to select and insert RAW data via PLSQL.
It contains a number of utility functions for converting between raw
and character data types as well as converting between numeric and
hex data. Some of the functionality provided by this sample is also
available via the UTL_RAW package, but the UTL_RAW package may
not be available on all versions and platforms.

Program Notes
-------------

This sample has been tested using Oracle 8.1.6 on both Solaris and NT,
but should work without modification on other platforms and versions.

This sample assumes all objects (table and PLSQL modules) are created
in the same schema (tested using SCOTT schema) and run as the same
user (i.e. SCOTT). If any of these assumptions are violated, it may
be necessary to grant additional privileges, create synonyms, or
fully qualify object names in addition to the steps documented.

The sample consists of three sections:

1) Create the table used by the sample.
2) Create the package specification and body.
3) Execute the test procedure.

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
-------

- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - -
- - - -

-- Table required by the testrawio package

CREATE TABLE testraw (col1 NUMBER, col2 LONG RAW);

-- Create the package specification and body

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
-- raw manipulation package from SQL*Plus!

set termout on
set serveroutput on
var 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);

- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - -
- - - -

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