Inserting .jpg pictures in Oracle...


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Inserting .jpg pictures in Oracle...

  1. #1
    Plascio Guest

    Inserting .jpg pictures in Oracle...


    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

  2. #2
    Boris Milrud Guest

    Re: Inserting .jpg pictures in Oracle...


    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



  3. #3
    Amro Guest

    Re: Inserting .jpg pictures in Oracle...


    "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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center