Can't Write to Oracle External Table

Question

I have a very simple Oracle External table defined from which I am able to select data:

CREATE TABLE brianl.billdata_daily2
(
    account   VARCHAR2 (30)
)
ORGANIZATION EXTERNAL
    (TYPE oracle_loader
          DEFAULT DIRECTORY cifs_dir
          LOCATION ('mymeter_daily_bills.csv'));

I put some sample rows into it and am able to successfully retrieve them with:

SELECT *
  FROM brianl.billdata_daily2;

Unfortunately, when I attempt to write to them I get an error:

INSERT INTO brianl.billdata_daily2 (account)
    SELECT ('A')
      FROM DUAL;

ORA-30657: operation not supported on external organized table

I have read, write, execute permissions on the cifs_dir directory, for arguments sake I added Oracle java file I/O permissions for the file system directory, but I still get this error. Does anyone have an idea how to solve this?

Answer 1

Of course is possible:

Create a view over external table:

create view v_billdata_daily2 as select * from billdata_daily2;

Create a trigger instead of insert (as example) on that view, like this:

create or replace trigger tg_billdata_daily2_i instead of insert on v_billdata_daily2
declare
    handler_ utl_file.file_type;
begin
    handler_ := utl_file.fopen('CIFS_DIR', 'mymeter_daily_bills.csv', 'a');
    utl_file.put_line(handler_, :new.account);
    utl_file.fclose(handler_);
end;
/

insert into the view as a table:

insert into v_billdata_daily2 values('It seams impossible');

Answer 2

You can write (or create) an external table that is in DATAPUMP format. You cannot create an external table in ORACLE_LOADER format. You cannot perform DML on either formats.

Answer 3

Oracle allows read-only access to data in external tables, it means no DML operations (UPDATE, INSERT, or DELETE) are possible.

However, you can create new table using CTAS and perform DML in newly created tables.

References:

External Table

Using External Tables to Load and Unload Data

Upvotes: 2