ADW: dbms_cloud.copy_data and record delimiter

To import via dbms_cloud.copy_data a windows file that include window CR+LF at the end of each record, specify the record delimiter as follow:

BEGIN
DBMS_CLOUD.COPY_DATA
(table_name      => '<table>'
,credential_name => '<credential_name>'
,file_uri_list   => 'https://<restpoint>/<file>'
,schema_name     => '<schema>'
,format           => json_object('recorddelimiter'      value '''\\r\\n''')
);
END;
/

 

An invalid specification of this delimiter would return the error below:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 801
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 782
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "xxx": expecting one of: "double-quoted-string, hexprefix, newline, single-quoted-string"
KUP-01007: at line 2 column 2
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 757
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 819
ORA-06512: at line 2
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s