Save a PDF File from MySQL to Oracle in C# without any conversion inbetween

Question

Hi I need to transfer or we can say save PDF which is stored in BLOB type from MySQL to Oracle. I know how to do it by converting to it in byte[] an then save it in Oracle.

My query is that, is there any other option available to avoid this conversion and speed up the process? Anything like save the MySQL file in a object directly give it to Oracle query?

Answer 1

You just can't escape these passages, if you want to move BLOBs from one RDBMS to another, since your program must:

1. Read binary data from source
2. Write data into destination

If you have lots of BLOBs to convert, IMHO a better approach would be:

1. Encapsulate the conversion step in a method
2. Multithread every conversion task according to your system capabilities.

Answer 2

Oracle and MySQL are disparate systems. When you move binary data from one to other, you have to go through this conversion process.

While both MySql and ODP.Net access Blobs as Stream, in order to read from one and write to another, your program needs to speak the common tongue and that common tongue in .Net is byte[].

Now, with Oracle, you could just store the file as a BFILE which is just a filesystem file as opposed to something that is managed by the RDBMS. If you do so, then you could just dump all blobs in MySql in a directory as outlined in this answer and you skip the byte array conversion from MySql altogether.

Adding the BFILE to Oracle will then be a batch job that you can run once the MySql dump is completed:

CREATE DIRECTORY test_dir AS '/tmp'  -- replace tmp with actual my sql dump directory 

-- Inserting the BFiles
INSERT INTO my_bfile_table VALUES (..., bfilename(test_dir, 'file1.pdf'));
INSERT INTO my_bfile_table VALUES (..., bfilename(test_dir, 'file2.pdf'));
...

Note that ODP.Net doesn't let you update BFILEs, so you have to insert them via SQL Commands as shown above. You can do it via JDBC and other interfaces though.

If the files are large, you could actually gain some performance improvements due to reduced transaction log activity. There are off course some trade-offs associated with storing as BFILE (like reduced manageability, need to separately backup filesystem etc.), so you may need to consider those.

If you have the option, then explore this idea - skip storing the PDFs in database altogether (BLOB or BFILE), move them to a CDN and store just a locator (URI) to the CDN asset in your database. Not only will the database be relieved largely, the end user experience will likely improve as well. It depends on your application and its requirements of course, but it may be worth exploring.

Answer 3

I think you have not another option (storing in byte[]) while reading BLOBs from MySql. But if you have a lot of records to move, while inserting Oracle, you can use Oracle.DataAccess.Client.OracleBulkCopy in ODP.NET for performance consideration.

http://docs.oracle.com/html/E10927_01/OracleBulkCopyClass.htm

Answer 4

If I correctly understand your question, you have a PDF file in MySQL database which you want to move to Oracle DB keeping the datatype as BLOB.

The first thing you need to do is, move the file to a directory location from where Oracle can read it. and then all you need to do is load the PDF into a BLOB column of your table.

CREATE OR replace PROCEDURE Load_lob 
AS 
  id        NUMBER; 
  pdf1      BLOB; 
  locator   BFILE; 
  bfile_len NUMBER; 
  bf_desc   VARCHAR2(30); 
  bf_name   VARCHAR2(30); 
  bf_dir    VARCHAR2(30); 
  bf_typ    VARCHAR2(4); 
  ctr       INTEGER; 
  CURSOR get_id IS 
    SELECT bfile_id, 
           bfile_desc, 
           bfile_type 
    FROM   pdf_table; 
BEGIN 
    OPEN get_id; 

    LOOP 
        FETCH get_id INTO id, bf_desc, bf_typ; 

        EXIT WHEN get_id%NOTFOUND; 

        dbms_output.Put_line('ID: ' 
                             ||To_char(id)); 

        SELECT bfile_loc 
        INTO   locator 
        FROM   pdf_table 
        WHERE  bfile_id = id; 

        dbms_lob.Filegetname(locator, bf_dir, bf_name); 

        dbms_output.Put_line('Dir: ' 
                             ||bf_dir); 

        dbms_lob.Fileopen(locator, dbms_lob.file_readonly); 

        bfile_len := dbms_lob.Getlength(locator); 

        dbms_output.Put_line('ID: ' 
                             ||To_char(id) 
                             ||' length: ' 
                             ||To_char(bfile_len)); 

        SELECT temp_blob 
        INTO   pdf1 
        FROM   temp_blob; 

        bfile_len := dbms_lob.Getlength(locator); 

        dbms_lob.Loadfromfile(pdf1, locator, bfile_len, 1, 1); 

        INSERT INTO internal_graphics 
        VALUES      (id, 
                     bf_desc, 
                     pdf1, 
                     bf_typ); 

        dbms_output.Put_line(bf_desc 
                             ||' Length: ' 
                             ||To_char(bfile_len) 
                             || ' Name: ' 
                             ||bf_name 
                             ||' Dir: ' 
                             ||bf_dir 
                             ||' ' 
                             ||bf_typ); 

        dbms_lob.Fileclose(locator); 
    END LOOP; 
END; 

/