ADG_REDIRECT_DML in Oracle Active Data Guard 19c

on
0

Oracle: Working with LOBs

on

Large Objects (LOBs) are a set of datatypes that are designed to hold large amounts of data. A LOB can hold up to a maximum size ranging from 8 terabytes to 128 terabytes depending on how your database is configured. Storing data in LOBs enables you to access and manipulate the data efficiently in your application.

Load lobs to the database
  • Create table: 
    SYS@ORCL> create table orders
(order_id number(12),
order_name varchar2(80),
file_size number,
order_file blob)
tablespace users
lob (order_file) STORE AS
(
tablespace users
enable storage in row
chunk 4096
pctversion 20
nocache
nologging);  

Table created.
  • Create database directory: 
    SYS@ORCL> create directory lobdir as '/u01/app/oracle/scripts/lobs';

Directory created.

SYS@ORCL> !mkdir /u01/app/oracle/scripts/lobs
  • Prepare file for loading to the database: 
    [oracle@oralin1 lobs]$ ll
total 40
drwxr-xr-x 2 oracle oinstall 4096 Aug  1 20:49 .
drwxr-xr-x 3 oracle oinstall 4096 Aug  1 20:39 ..
-rw-r--r-- 1 oracle oinstall 9216 Aug  1 20:45 order_1.pdf
  • Load LOB file to the database: 
    SYS@ORCL> declare
 l_size number;
 l_file bfile;
 l_blob blob;
begin
 l_file := bfilename('LOBDIR','order_1.pdf');
 dbms_lob.fileopen(l_file);
 l_size := dbms_lob.getlength(l_file);
 insert into orders 
 (
  order_id,
  order_name,
  file_size,
  order_file
 )
 values
 (
  1,
  'order-1',
  l_size,
  empty_blob()
 )
 returning order_file into l_blob;
 dbms_lob.loadfromfile(l_blob,l_file,l_size);
 commit;
end;
/  

PL/SQL procedure successfully completed.
  • Check loaded file: 
    SYS@ORCL> select
 order_id,
 order_name,
 file_size,
 dbms_lob.getlength(order_file)
from 
 orders
;  

ORDER_ID  ORDER_NAME   ORDER_SIZE   DBMS_LOB.GETLENGTH(ORDER_FILE)
-------- ------------ ------------ -------------------------------
  1       Contract 1 115250          115250

Comments

Post a Comment