I have created an external table as follows:
CREATE TABLE ext_cv027_data (
e_status VARCHAR2(200),
e_ledger_name VARCHAR2(200),
e_accounting_date VARCHAR2(200),
e_currency_code VARCHAR2(200),
e_date_created VARCHAR2(200),
e_created_by VARCHAR2(200),
e_actual_flag VARCHAR2(200),
e_user_je_category_name VARCHAR2(200),
e_user_je_source_name VARCHAR2(200),
e_entered_dr VARCHAR2(200),
e_entered_cr VARCHAR2(200),
e_reference1 VARCHAR2(200),
e_reference2 VARCHAR2(200),
e_reference3 VARCHAR2(200),
e_reference4 VARCHAR2(200),
e_reference5 VARCHAR2(200),
e_reference6 VARCHAR2(200),
e_reference7 VARCHAR2(200),
e_reference8 VARCHAR2(200)
)
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY dir_gl_journals ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL
) LOCATION ( 'journals_20230322101527.csv' )
);
I read in this post that you can use the external modify statement to change location:
select * from ext_cv027_data
external modify (
location ( 'test_202111191102.txt' )
);
When I run the select * statement I get the error:
ORA-30663: Only one EXTERNAL MODIFY clause can be specified for a partitioned external or hybrid table.
30663. 00000 - "An EXTERNAL MODIFY clause can only be specified for an external or hybrid-partitioned table."
*Cause: An EXTERNAL MODIFY clause was specified when querying a table that was not an external or hybrid-partitioned table.
*Action: Remove the EXTERNAL MODIFY clause from the query.
Error at line: 2, column: 12
What could be the problem?