Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

External table access parameter "REJECT ROWS WITH ALL NULL FIELDS" not working

angelo.stramieriFeb 10 2024 — edited Feb 10 2024

Hello everyone,

I'm currently working on creating an external table within my Oracle ADB instance to read data from a CSV file stored in a bucket. However, I've encountered a challenge with the structure of the CSV file. It contains some missing fields and even completely empty lines. My goal is to retain only the rows where at least one value is present and ignore those with no values.

After several attempts, I've realized that it seems the two options I'm considering are incompatible, specifically, the missing field values are null parameter doesn't seem to function in conjunction with the reject rows with all null fields parameter.

Could someone please assist me with this issue? Any guidance or insights would be greatly appreciated.

Thank you

A.

The CSV file

NAME,COLOR,TASTE,PRICE
Apple,Red,Sweet,0.75
Banana,,Sweet,0.50
Orange,Orange,Tangy,0.60
,Green,,1.20
Strawberry,Red,,1.00
,,,
Pineapple,Yellow,Sweet and Tangy,1.50
Watermelon,Green,Sweet,
Kiwi,Green,Tangy,
,,,
Blueberry,Blue,Sweet,0.80
Mango,,Sweet,1.25

The EXT TABLE code

drop table ext_test_csv;

begin
dbms_cloud.create_external_table(
credential_name => 'MY_CREDENTIAL',
file_uri_list => 'BUCKET/test.csv',
table_name => 'ext_test_csv',
column_list => 'name varchar(100 char),
taste varchar(100 char),
color varchar(100 char),
price varchar(100 char)',
field_list => 'name, taste, color, price',
format => json_object('characterset' value 'al32utf8',
'blankasnull' value 'true',
'delimiter' value ',',
'ignoremissingcolumns' value 'true',
'skipheaders' value '1')
);
end;
/

alter table ext_test_csv reject limit unlimited;

alter table ext_test_csv access parameters (
records delimited by detected newline
characterset al32utf8
ignore_header=1
nologfile
nobadfile
nodiscardfile
readsize=10000000
credential 'cred_fusrdev'
fields terminated by ','
nullif=blanks
missing field values are null
reject rows with all null fields
);

The output

Comments
Post Details
Added on Feb 10 2024
0 comments
94 views