CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-loader
siva kumar
siva kumar

Reputation: 1

Is it possible to have the table name as file name in oracle file loading

I have a challenge in my current project. Where I need to create a table from a file(fileone.txt).The table name should be same as filename with out extension. How can this can be achieved

I have thought about sqlloader,UTL,external tables. None of them has the above option

Expected result is : table name should be same as file name without extension

Upvotes: 0

Views: 705

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17934

Better than creating a bunch of tables, create a single table that shows all the records. Like,

CREATE TABLE so_dir_ext
  ( ... your external table columns ...)
  ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ... your directory, created with CREATE DIRECTORY command ...
    ACCESS PARAMETERS (
      ... your access oracle loader parameters
      ... optional: PREPROCESSOR directive, see below ...
    )
    LOCATION ('yourfilenameprefix*.yourfilenameext')
  )
  ;

If you need to know which file each record came from (and you cannot tell from the data), you can use a PREPROCESSOR and a shell script on your server to prepend that to each row before it's loaded. See this characteristically excellent answer from Alex Poole on how to do that here.

Another alternative, if you don't want a preprocessor, would be to still have a single external table, but change the filename for the table dynamically just before you read each file. E.g.,

FOR r IN ( SELECT filename FROM list_of_files_you_will_need_to_get_somehow ) LOOP
  EXECUTE IMMEDIATE 'alter table your_ext_table location (''' || r.filename || ''')';
  SELECT ... FROM your_ext_table...
END LOOP;

Upvotes: 2

Related Questions