Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Change dynamically location value in external table using external modify in 19c

Cesar Tepetla CorteMar 30 2023 — edited Mar 30 2023

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?

This post has been answered by Cesar Tepetla Corte on Mar 30 2023
Jump to Answer
Comments
Post Details
Added on Mar 30 2023
9 comments
1,028 views