Skip to Main Content
  • Questions
  • How to change the file name dynamically for an external table creation (ex ABC_YYYYMMDDHH24MISS)

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, vinesh.

Asked: November 17, 2021 - 12:08 pm UTC

Last updated: November 22, 2021 - 2:07 am UTC

Version: 19c

Viewed 100+ times

You Asked

Team,

The version of oracle we use is :" Oracle Database 19c Enterprise Edition Release 19.0.0.0.0".


Lets consider our basic scott.emp table for this scenario limited to few columns.

The external table creation script goes something like this :



CREATE TABLE employee (
  empno  NUMBER(4,0),
  ename  VARCHAR2(10),
  job   VARCHAR2(9),
  mgr   NUMBER(4,0)
)
  ORGANIZATION EXTERNAL ( TYPE oracle_loader
    DEFAULT DIRECTORY scott ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
        SKIP 1
      FIELDS TERMINATED BY "," MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS
    ) LOCATION ( 'EMPLOYEE_20211116203018.csv' )
  ) REJECT LIMIT UNLIMITED;
   The file name passed over here is : 'EMPLOYEE_20211116203018.csv'


If we see the file name structure it is some thing like EMPLOYEE_YYYYMMDDHH24MISS

YYYY--2021,MM-11,DD-16,HH24:MI:SS --203018

if the file name had only extension of only YYYYMMDD , this could have been handled using dynamic sql like including execute immediate



select 'EMPLOYEE_'||to_char(sysdate,'YYYYMMDD')||'.csv' AS tablename from dual
CREATE TABLE employee (
  empno  NUMBER(4,0),
  ename  VARCHAR2(10),
  job   VARCHAR2(9),
  mgr   NUMBER(4,0)
)
  ORGANIZATION EXTERNAL ( TYPE oracle_loader
    DEFAULT DIRECTORY scott ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
        SKIP 1
      FIELDS TERMINATED BY "," MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS
    ) LOCATION ( tablename )
  ) REJECT LIMIT UNLIMITED;


But unfortunately the file extension is including hh24miss along with YYYYMMDD extension.

The timestamp is not a fixed value , as it may vary atleast in minutes and seconds.

Any code snippet to handle timestamp as well in the external creation script is most welcome and appreciated.

Regards,

Satyam Reddy.

and Connor said...

If you want to point an existing external table at a new file, you can alter the location:

create or replace directory tmp as '/tmp';
declare
  f utl_file.file_type;
  dt date := sysdate;
  file_name varchar2(100);
begin
  file_name := 'test_' || to_char ( dt, 'YYYYMMDDHH24MI' ) || '.txt';
  f := utl_file.fopen ( 'TMP', file_name, 'w' );
  utl_file.put_line ( f, 'first file' );
  utl_file.fclose ( f );
  dbms_output.put_line ( 'first file ' || file_name );
  file_name := 'test_' || to_char ( dt + 1, 'YYYYMMDDHH24MI' ) || '.txt';
  f := utl_file.fopen ( 'TMP', file_name, 'w' );
  utl_file.put_line ( f, 'second file' );
  utl_file.fclose ( f );
  dbms_output.put_line ( 'second file ' || file_name );
end;
/

first file test_202111191102.txt
second file test_202111201102.txt

create table t (
  c1 varchar2(20)
) organization external (
  default directory tmp
  location ( 'test_202111191102.txt' )
);

select * from t;

C1           
first file  

alter table t
  location ( 'test_202111201102.txt' );
  
select * from t;

C1            
second file 


Though as you're on 19c, you can change the file location "on-the-fly" in the query with the modify external clause:

select * from t
  external modify (
    location ( 'test_202111191102.txt' )
  );
  
C1           
first file  

select * from t
  external modify (
    location ( 'test_202111201102.txt' )
  );

C1            
second file  


Rating

  (4 ratings)

Comments

how to change the file name dynamically for an externa table

vinesh, November 19, 2021 - 12:45 pm UTC

Hi Chris,

Thanks for taking your time for helping me out !

The problem I face over here is I do not have the control of the timestamp (hh24miss ) .

I have the control of over YYYYMMDD part .

As part of your Frename , I could not trace the file name as the timestamp(hh24miss ) may change, as we get the data from the client side(File generation part) .

Could you pls help me out in this regard.

Overall to say in brief : The file is a csv file which is uploaded by our client on to our sftp. The file extension has ABC_YYYYMMDDHHMISS. I have control over the file extension till ABC_YYYYMMDD as the file generation take place every day , but we are not sure at what time the file generation happens.

I need this file to be handled in our external table .

So that I can load the data from this external table to our project related tables.

Thanks as always !!

What is question

Alterpinion, November 19, 2021 - 1:30 pm UTC

The initial question is how to use the unknown filename. About a known filename athor wrote:
if the file name had only extension of only YYYYMMDD , this could have been handled using dynamic sql like including execute immediate

To handle unknown filenames, can be used the preprocessor or the mask * in filename.

-- files in directory oracle_tmp
select *
from external(
 (filename varchar2(1000 char))
     type oracle_loader
     default directory oracle_tmp
     access parameters (
       records delimited by newline
       nobadfile nologfile nodiscardfile
       preprocessor server_usr_bin:'ls' -- /usr/bin/ls
       fields (filename char(1000))
  )
  location ('.')
  reject limit unlimited
)
where filename like to_char(sysdate,'YYYYMMDD"______.csv"');

FILENAME              
------------------
20211119010203.csv
20211119040506.csv

-- or by mask (content of the both files)
col filemask new_val filemask
select to_char(sysdate,'YYYYMMDD"??????.csv"') filemask from dual;

FILEMASK          
------------------
20211119??????.csv

select *
from external(
 (line varchar2(20 char))
     type oracle_loader
     default directory oracle_tmp
     access parameters (
       records delimited by newline
       nobadfile nologfile nodiscardfile
       fields (line char(20))
  )
  location ('&&filemask')
  reject limit unlimited
);


LINE                
--------------------
123                   -- first  20211119010203.csv
456                   -- second 20211119040506.csv
789                   --        20211119040506.csv



Unfortunately, location does not accept a variable or expression. Otherwise, it would be possible to select files with the join external(/usr/bin/ls) with the external(corresponding contents).

How to change the file name dynamically for an external table creation (ex ABC_YYYYMMDDHH24MISS)

vinesh, November 20, 2021 - 6:49 pm UTC

This is in solution provided by Alterpinion.

-- files in directory oracle_tmp
select *
from external(
(filename varchar2(1000 char))
type oracle_loader
default directory oracle_tmp
access parameters (
records delimited by newline
nobadfile nologfile nodiscardfile
preprocessor server_usr_bin:'ls' -- /usr/bin/ls
fields (filename char(1000))
)
location ('.')
reject limit unlimited
)
where filename like to_char(sysdate,'YYYYMMDD"______.csv"');

The Directory_name WHERE OUR .csv file resides is : SCOTT
The Directory path is : /u02/SCOTT/

Me trying with the below code :

select *
from external(
(filename varchar2(1000 char))
type oracle_loader
default directory SCOTT
access parameters (
records delimited by newline
nobadfile nologfile nodiscardfile
preprocessor SCOTT:'ls' -- /usr/bin/ls
fields (filename char(1000))
)
location ('.')
reject limit unlimited
)
where filename like to_char(sysdate,'YYYYMMDD"______.csv"');
Below is the error message I get .

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "minussign": expecting one of: "badfile, byteordermark, characterset, column, compression, credential, data, delimited, discardfile, dnfs_enable, dnfs_disable, disable_directory_link_check, escape, field, fields, fixed, io_options, ignore_blank_lines, ignore_header, load, logfile, language, nodiscardfile, nobadfile, nologfile, date_cache, dnfs_readbuffers, preprocessor, readsize, string, skip, territory, variable, validate_table_data, xmltag"
KUP-01007: at line 3 column 36
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
Any suggestions most welcome and really appreciated !!



Regards,
Vinesh




Connor McDonald
November 22, 2021 - 2:07 am UTC

Here is an example

[oracle@db192 tmp]$ echo "/usr/bin/ls -1 /u01/app" > /tmp/list_files.sh
[oracle@db192 tmp]$ chmod 700 /tmp/list_files.sh

So I have an executable shell file which will list out files in /u01/app

Now I need to reference that in my external table, which also needs a valid location file (even if I do not use it),

[oracle@db192 tmp]$ touch dummy.txt

Now a couple of directories (customise as you see fit)

create or replace directory tmp as '/tmp';
create or replace directory bin as '/tmp';

SQL> select *
  2  from external(
  3  (filename varchar2(1000 char))
  4  type oracle_loader
  5  default directory tmp
  6  access parameters (
  7  records delimited by newline
  8  nobadfile nologfile nodiscardfile
  9  preprocessor bin:'list_files.sh'
 10  fields (filename char(1000))
 11  )
 12  location ('dummy.txt')
 13  reject limit unlimited
 14 );

FILENAME
-------------------------------------------
oracle
oraInventory


Once you can list a directory, you can easily get the name of the files you want to load.

Alternative approach using preprocessor

Johan Snyman, November 22, 2021 - 6:03 am UTC

You can also use the pre-processor approach to find the files dynamically based on file name patterns and then "cat" their contents. This could in the end be simpler than dealing with changing file names all the time.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.