oracle--export, import fields of BLOB type

A blob is a data type in Oracle that holds big data in the compressed binary form.

Data migration is not easy to handle if it involves a BLOB field because it cannot be manipulated in the usual way, such as by using Select to view the field, or by inserting the value of the field with insert.

The following records the export of BLOB fields, the import method flow.

Method: Use Utl_file to export the contents of the Blob field to a TXT document in binary form, and then import the contents of the document into the specified database table using Dbms_blob

1. Create a text document to save BLOB data

Here, a file named Test.txt is created in the home directory, which is the/home/dhl/test.txt

2. Create an Oracle Temp directory

Create or Replace  as ' /home/dhl/ ';

3. Export BLOB data

Here is an example of the Sy_qrtz_job_details data table, where Job_data is a BLOB type field

1 DECLARE2 File_handle Utl_file. File_type;3 B_lob BLOB;4 BEGIN5   SelectJob_data intoB_lob fromSy_qrtz_job_detailswhereJob_name='2wnfkfzz14yuodhnyofezbl';6 7File_handle:=Utl_file.fopen ('Utl_file_dir','Test.txt','W'); 8 Utl_file.  Put_raw (File_handle, B_lob, true); 9 Utl_file. FCLOSE (file_handle);Ten END;

4. Import the contents of the document into the specified database table

1 DECLARE2 b_file bfile;3 B_lob BLOB;4 BEGIN5   --return a into B_lob binds the column to a variable of the type of a blog, and then, whenever you assign a value to B_lob, it is equal to inserting the value into the table6   INSERT7    intosy_qrtz_job_details8     (9 Sched_name,Ten Job_name, One Job_group, A DESCRIPTION, - Job_class_name, - is_durable, the Is_nonconcurrent, - Is_update_data, - Requests_recovery, - Job_data +     ) -     VALUES +     ( A       'Rhscheduler', at       'Test6', -       'DEFAULT', -       'Test6', -       'Com.rh.core.icbc.imp.NImpStateJob', -       '1', -       '0', in       '0', -       '0', to Empty_blob () +     ) -     RETURNJob_data the    intoB_lob; *   --convert a file to bfile $B_file:=Bfilename ('Utl_file_dir','Test.txt');Panax NotoginsengDbms_lob.Open(B_file, dbms_lob.file_readonly); -   --convert content from B_file to B_lob the Dbms_lob.loadfromfile (B_lob,b_file,dbms_lob.getlength (B_file)); +Dbms_lob.Close(b_file); A   COMMIT; the END;

oracle--export, import fields of BLOB type