0

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

siva kumar
  • 1
  • 2
  • 2
    What is the business problem you are trying to solve that lead to this technical solution? If you don't know the name of the file at compile time, that would mean that you'd be dynamically creating the table every time. Which would mean that every subsequent reference to the table would need to use dynamic SQL. That is going to be terrible for maintainability. So I'd seriously question the requirement and look for a different way to solve whatever business problem you actually have. – Justin Cave Sep 20 '19 at 14:28
  • the problem is I will be getting multiple files every second. so I have to load each file. Also if we create a table name with filename it will be a better ref. Obv.. at EOW these tables will be deleted if loaded successfully – siva kumar Sep 20 '19 at 14:53
  • Not sure what " better ref. Obv.. at EOW" means. If you're getting multiple files a second, that would mean creating multiple tables a second. That's not a good idea. It would make much more sense to load all the data into a single table with a single, known name and just include the name of the file as a column in the table (probably along with a load date). – Justin Cave Sep 20 '19 at 14:57
  • obviously at the end of the week* – siva kumar Sep 21 '19 at 07:03
  • Why would it be better to have tens of thousands of tables floating around? You could dynamically generate multiple external table definitions a second and then dynamically generate the code to load that data to permanent tables as well as incurring the overhead of trying to drop those tens of thousands of tables at the end of the week. But that would be a really inefficient way to design a system. – Justin Cave Sep 21 '19 at 13:37

1 Answers1

2

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;
Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
  • thanks Matthew. I will consider this option . But still my business wants to create a table name as file name. I will wait for further answers and I will also explore for a solution – siva kumar Sep 21 '19 at 07:08