Sample Header Ad - 728x90

How can I blob column values to files?

0 votes
0 answers
11 views
I have a table defines as
create table CO.PRODUCTS
(
    PRODUCT_ID         NUMBER generated by default on null as identity
        constraint PRODUCTS_PK
            primary key,
    PRODUCT_NAME       VARCHAR2(255 char) not null,
    UNIT_PRICE         NUMBER(10, 2),
    PRODUCT_DETAILS    BLOB
        constraint PRODUCTS_JSON_C
            check (product_details is json),
    PRODUCT_IMAGE      BLOB,
    IMAGE_MIME_TYPE    VARCHAR2(512 char),
    IMAGE_FILENAME     VARCHAR2(512 char),
    IMAGE_CHARSET      VARCHAR2(512 char),
    IMAGE_LAST_UPDATED DATE
)
/

comment on table CO.PRODUCTS is 'Details of goods that customers can purchase'
/

comment on column CO.PRODUCTS.PRODUCT_ID is 'Auto-incrementing primary key'
/

comment on column CO.PRODUCTS.PRODUCT_NAME is 'What a product is called'
/

comment on column CO.PRODUCTS.UNIT_PRICE is 'The monetary value of one item of this product'
/

comment on column CO.PRODUCTS.PRODUCT_DETAILS is 'Further details of the product stored in JSON format'
/

comment on column CO.PRODUCTS.PRODUCT_IMAGE is 'A picture of the product'
/

comment on column CO.PRODUCTS.IMAGE_MIME_TYPE is 'The mime-type of the product image'
/

comment on column CO.PRODUCTS.IMAGE_FILENAME is 'The name of the file loaded in the image column'
/

comment on column CO.PRODUCTS.IMAGE_CHARSET is 'The character set used to encode the image'
/

comment on column CO.PRODUCTS.IMAGE_LAST_UPDATED is 'The date the image was last changed'
/
Now I want to export all non-null PRODUCT_DETAILS column values into files of .json. How can I do with sqlplus? Say...
$ pwd
/.../wherever

$ sqlplus / as sysdba

> 

> exit

$ ls -1
1.json
2.json
// 3.PRODUCT_DETAILS is null
4.json
Asked by Jin Kwon (165 rep)
Jun 2, 2025, 07:23 AM