I have to import an external csv file into a table in database each day. The file is encoded in 'UCS2-LE BOM'. I have found a solution using external tables and bulk insertion, but the problem is to re-use the GTT made as an external table to be filled by the new daily file, each day in a job.
Since the GTT already exists from previous session, I can't use
CREATE GLOBAL TEMPORARY TABLE ... ORGANIZATION EXTERNAL .... I can however drop the table each time and re create it but I think there has to be a way I'm not aware of.
Additionally, It is much appreciated if the importing could be done entirely without the need for GTTs and loading a file straight into the destination table. I have done that before with custom tagged hierarchial XML files (Oracle is so powerful in XML files) but couldn't find many resources and references on csv files, since.
GTT's can't be used as external tables
Error report - SQL Error: ORA-30658: attempt was made to create a temporary table with EXTERNAL organization 30658.0000 - "attempt was made to create a temporary table with EXTERNAL organization" *Cause: An attempt was made to create an External Organized Temporary table. This is not supported. *Action: Don't do that!
I found out that
organization external doesn't create the table though it says on the log it does. The table is parsed each time from the blueprint defined in
organization external on access.