Table Storage

### BFILE ###

1. Setup the OS Directory in Oracle

SQL> CREATE OR REPLACE DIRECTORY external_images_dir AS '/u01/app/oracle/oradata/os_images/'; 

SQL> GRANT READ ON DIRECTORY external_images_dir TO user1;

2. Create the Table Structure
SQL> CREATE TABLE product_catalog ( 
product_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
product_name VARCHAR2(100), 
sku VARCHAR2(50), 
image_file BFILE 
);

3. Inserting Data
SQL> INSERT INTO product_catalog (product_name, sku, image_file) VALUES ('Running Shoes', 'RS-990', BFILENAME('EXTERNAL_IMAGES_DIR', 'shoes_v1.jpg'));

### Create Partition Table with Day Wise Interval Partition ###

1. Create Table 
SQL> CREATE TABLE product_catalog_p (
product_id   NUMBER GENERATED BY DEFAULT AS IDENTITY,
roduct_name VARCHAR2(100),
sku VARCHAR2(50),
image_file   BFILE,
created_date DATE DEFAULT SYSDATE NOT NULL,
-- The primary key must include the partition key if you want a local index
CONSTRAINT pk_product_catalog PRIMARY KEY (product_id, created_date)
)
PARTITION BY RANGE (created_date) 
INTERVAL (NUMTODSINTERVAL(1, 'DAY')) -- Automatically creates a new partition every day
(
-- You must define at least one starting anchor partition
PARTITION p_init VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);

2. Insert data
SQL> INSERT INTO product_catalog_p (product_name, sku, image_file, created_date) VALUES ('Wireless Mouse', 'MS-CLK-01', BFILENAME('EXT_APP_IMAGES', 'mouse.jpg'), TO_DATE(sysdate, 'YYYY-MM-DD'));

3. Select data
SQL> select * from  product_catalog_p; 

### External table with ORACLE_BIGDATA loader, BLOB and daywise Partition ### 

Step 1: Define Your Database Directory Points
SQL> create or replace directory ext_app_parquet as '/u01/app/oracle/parquet_data'; 
SQL> create or replace directory ext_app_csv as '/u01/app/oracle/csv_data' ;
SQL> grant read, write on directory ext_app_parquet to user1; 
SQL> grant read, write on directory ext_app_csv to user1; 

Step 2: Create the Partitioned External Table (Parsing Parquet)
SQL> CREATE TABLE ext_parquet_catalog_raw (
product_id     NUMBER,
product_name   VARCHAR2(100),
sku  VARCHAR2(50),
image_filename VARCHAR2(255), -- Reads the image name string from Parquet
created_date   DATE
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_BIGDATA
DEFAULT DIRECTORY ext_app_parquet
ACCESS PARAMETERS (
com.oracle.bigdata.fileformat = parquet
)
)
PARTITION BY RANGE (created_date) (
-- Partition for Sept 25, 2026 data file
PARTITION p_2026_09_25 VALUES LESS THAN (TO_DATE('2026-09-26', 'YYYY-MM-DD'))
LOCATION ('2026-09-25_data.parquet'),
-- Partition for Sept 26, 2026 data file
PARTITION p_2026_09_26 VALUES LESS THAN (TO_DATE('2026-09-27', 'YYYY-MM-DD'))
LOCATION ('2026_09_26_data.parquet')
);

Step 3: Create a view with Virtual BFILE
SQL> CREATE OR REPLACE VIEW product_catalog_v AS
SELECT 
    product_id,
    product_name,
    sku,
    -- Generates a live BFILE locator mapping to your image directory
    BFILENAME('EXT_APP_IMAGES', image_filename) AS image_file,
    created_date
FROM ext_parquet_catalog_raw;

Step 4: Query the data 

1. Create the Sample CSV File
#vi /u01/app/oracle/csv_data/2026_09_25_data.csv
product_id,product_name,sku,image_filename,created_date
1,Wireless Mouse,MS-CLK-01,mouse.jpg,2026-09-25
2,Ergonomic Desk Mat,MT-PAD-09,deskmat.jpg,2026-09-25
3,Mechanical Keyboard,KB-RGB-02,keyboard.jpg,2026-09-26

2. Convert CSV to Parquet using Python
python csv_to_parquet.py 2026-09-25

3. Select query from External table  
SQL> SELECT product_name, sku, image_file FROM product_catalog_v WHERE created_date = TO_DATE('2026-09-25', 'YYYY-MM-DD');

======