Re: Replication of LONG RAW data type

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 29 Aug 1998 18:21:23 GMT
Message-ID: <35f24313.7989057@192.86.155.100>

A copy of this was sent to Thomas Pohl <t.pohl_at_icvsion.de> (if that email address didn't require changing) On Fri, 28 Aug 1998 19:26:33 +0100, you wrote:

>Hi,
>
>I have a deadlock :-( the java thin driver does not support BLOBs only
>LONG RAW, but replication does not support LONG RAW.
>I must replicate or copy automatic the table with the LONG RAW field.
>
>Any better ideas then waiting for Oracle 8.1 ????
>
>greetings Thomas
>
>t.pohl_at_icvision.de

I believe the issue with the java thin driver is that it doesn't know what to do with a LOB Locator data type (as is the problem with all v7.x clients -- before the LOB datatype existed).

the solution for allowing any client, regardless of whether it understands what a LOB is or not at the api level, is to never return the lob locator to the client -- your java program can and should use LOBS.

Here is an example, say you have a table:

create table blobs
( id varchar2(255) primary key,
  theBlob blob
)
/

Every table has a primary key (and even if it doesn't, it has a rowid we could use). We will just write a small package that uses that primary key to stuff the lob locator into a pl/sql global variable. For example, given the above table we would code:

create or replace package blobs_pkg
as

    g_blob_locator blob;
    procedure set_blob_locator( p_id in blobs.id%type ); end;
/

create or replace package body blobs_pkg as

procedure set_blob_locator( p_id in blobs.id%type ) is
begin

    select theBlob into g_blob_locator

      from blobs
     where id = p_id;

end;

end blobs_pkg;
/

Now, at runtime, instead of selecting the LOB column out in your java code -- select out the primary key (if you aren't already -- you can use the rowid in place of the primary key). when you get to the point where you want to READ/WRITE the lob you have selected a row around -- you'll just call blobs_pkg.set_blob_locator and pass it that primary key. You can now use pl/sql to read/write the blob using the DBMS_LOB package and passing it the constant blobs_pkg.g_blob_locator. The psuedo code to read every row from the above table and write the lobs to a local file might look like:

   for x in ( select id from blobs ) loop

       execute  blobs_pkg.set_blob_locator( x.id ); 
       execute  :length := dbms_lob.getlength(blobs_pkg.g_blob_locator);
       
       open a file to write to.

       offSet = 1;

       while length > 0 
         amt = 4096
         execute dbms_lob.read( blobs_pkg.g_blob_locator, :amt, :offSet, :buff)
         write AMT bytes to file
         offSet = offSet + amt
         length = length - amt
       end

       close the file

   end loop

So, the code you write had to (in this example) only support the datatypes:

• VARCHAR2 (for ID in the query)
• NUMBER ( for the in/out variables length, amt and offset)
• RAW ( for the out variable buff)

It does *NOT* have to be an oracle 8 client (hence the above works in ODBC with v7 drivers and 2.x pro*c, v7 OCI and so on)...

You can read or write upto 32k at a time using the piecewise operations in the dbms_lob package and pl/sql....

If you need to WRITE a new lob to the database, your code could look something like:

   execute insert into blobs values ( 'NewKey', empty_blob() );    execute blobs_pkg.set_blob_locator( 'NewKey' );    execute dbms_lob.write( blobs_pkg.g_lob_locator, .... );  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Aug 29 1998 - 13:21:23 CDT