Hi,
I am trying to read in an external table using Oracle Object Storage as a source.
I have followed the official documentation and installed DBMS_Cloud on my local installation of Oracle: https://oracle-base.com/articles/21c/dbms_cloud-installation
I have then followed the directions here: https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/query-external-data.html#GUID-72DC8BB6-5245-4262-A100-C35A2E553F70
I've created the same file called "channels.txt" and uploaded it to my own OCI object storage bucket.
I followed each command exactly, such as using this script to create an external table:
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
table_name =>'CHANNELS_EXT',
credential_name =>'DEF_CRED_NAME',
file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/channels.txt',
format => json_object('delimiter' value ','),
column_list => 'CHANNEL_ID NUMBER, CHANNEL_DESC VARCHAR2(20), CHANNEL_CLASS VARCHAR2(20)' );
END;
/
(I replaced the file_uri_list with my own bucket file's URL and also replaced "credential_name" with my own credential")
However, after running everything, when I try to run
SELECT count(*) FROM channels_ext;
I get this
16:37:34 line 2: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
16:37:34 ORA-29400: data cartridge error
16:37:34 KUP-00554: error encountered while parsing access parameters
16:37:34 KUP-01005: syntax error: found "identifier": expecting one of: "double-quoted-string, hexprefix, newline, single-quoted-string"
16:37:34 KUP-01008: the bad identifier was: DETECTED
16:37:34 KUP-01007: at line 1 column 22
I've tried to run validate table on it:
BEGIN
DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
table_name => 'CHANNELS_EXT' );
END;
/
Which gives me a similar kind of error:
16:39:35 line 1: ORA-20000: ORA-29400: data cartridge error
16:39:35 KUP-00554: error encountered while parsing access parameters
16:39:35 KUP-01005: syntax error: found "identifier": expecting one of: "double-quoted-string, hexprefix, newline, single-quoted-string"
16:39:35 KUP-01008: the bad identifier was: DETECTED
16:39:35 KUP-01007: at line 1 column 22
16:39:35 ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 972
16:39:35 ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1233
16:39:35 ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1878
16:39:35 ORA-06512: at line 2
And then I queried the LOGFILE_TABLE and BADFILE_TABLE, but they were always empty, no matter how many runs I try.
I'm completely stumped at this point - does anyone have any idea what is wrong? Thank you!