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