Skip to Main Content
  • Questions
  • Check if a file exists on the server for processing with UTL_FILE

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, karthikeyan.

Asked: August 14, 2017 - 6:52 pm UTC

Last updated: September 12, 2017 - 7:40 am UTC

Version: R12

Viewed 10K+ times! This question is

You Asked

I am looking for a way to check if a file exists on the server and pass the file name on to UTL file in Oracle EBS.

File name: DDMMYYY_Data.dat.

We will receive one DDMMYYY_Data.dat file every month and it got placed in a directory. once the file got process into table through UTL we will archive the file.

So there will be only one file in the directory at initial. The file name is with date, so based on the date the file name will change every month.


Is there anyway in PLSQL to check is the file is exist in the directory or not and then pass the file name into UTL file?

eg:
UTLFILE.FOPEN('direcoryname','ddmmyyy_data.dat','r'); -- ddmmyyy_data.dat file name will change every month, so how to pass the file name into UTL because it is not fixed.

I want to register this plsql package as concurrent program and submit in Oracle SRS window.

and Connor said...

I'm going to suggest a different approach.

Check out this article by Adrian Billington. It uses an external table to list the files in the nominated directory.

http://www.oracle-developer.net/display.php?id=513

This way you can *know* exactly what files are in the directory, and process them however you like.


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alternative

Racer I., August 15, 2017 - 9:57 am UTC

Maybe you can see what's there with :

v$diag_dir_ext

?
Connor McDonald
August 16, 2017 - 1:02 pm UTC

(To my knowledge) that is limited to files under the diagnostic dest

External table

karthikeyan lakshman, August 15, 2017 - 9:02 pm UTC

Thanks Connor.

I tried with external table but i am facing an issue.

I created a directory as 'RETAIL_INCOME' with a path of '/home/GRM/non_retail/Control_file'

I created a directory as 'RETAIL_ARCHIVE' with a path of '/home/GRM/non_retail/Control_Archive'

And list_files.bat file contain as
@echo off
dir /home/GRM/traces

And text.txt file placed in the below path '/home/GRM/non_retail/Control_Archive' with value as 'host echo > /home/fclvappi001881/GRM/non_retail'

CREATE TABLE files_xt
(file_date VARCHAR2(50)
, file_time VARCHAR2(50)
, file_size VARCHAR2(50)
, file_name VARCHAR2(255)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY RETAIL_INCOME
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
LOAD WHEN file_size != '<DIR>'
PREPROCESSOR RETAIL_ARCHIVE: 'list_files.bat'
FIELDS TERMINATED BY WHITESPACE
)
LOCATION ('text.txt')
);

I am getting below error as

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04004: error while reading file /home/GRM/non_retail/Control_file/text.txt
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.




Connor McDonald
August 16, 2017 - 12:57 pm UTC

That looks like Unix

'/home/GRM/non_retail/Control_file'

and this looks Windows

@echo off
dir /home/GRM/traces

So which is it ? If it is Unix, then it would be something like:

#!/bin/ksh
/bin/ls -1 /home/GRM/traces

External table

karthikeyan lakshman, August 15, 2017 - 9:03 pm UTC

Thanks Connor.

I tried with external table but i am facing an issue.

I created a directory as 'RETAIL_INCOME' with a path of '/home/GRM/non_retail/Control_file'

I created a directory as 'RETAIL_ARCHIVE' with a path of '/home/GRM/non_retail/Control_Archive'

And list_files.bat file contain as
@echo off
dir /home/GRM/traces

And text.txt file placed in the below path '/home/GRM/non_retail/Control_Archive' with value as 'host echo > /home/fclvappi001881/GRM/non_retail'

CREATE TABLE files_xt
(file_date VARCHAR2(50)
, file_time VARCHAR2(50)
, file_size VARCHAR2(50)
, file_name VARCHAR2(255)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY RETAIL_INCOME
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
LOAD WHEN file_size != '<DIR>'
PREPROCESSOR RETAIL_ARCHIVE: 'list_files.bat'
FIELDS TERMINATED BY WHITESPACE
)
LOCATION ('text.txt')
);

I am getting below error as

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04004: error while reading file /home/GRM/non_retail/Control_file/text.txt
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.




Calling a external table in Oracle Concurrent program

karthikeyan lakshman, August 16, 2017 - 3:32 pm UTC

It's a Unix, I can able to get the file name successfully.

But I have 2 issues,
1. When I am giving full permission (chmod 777) to the location folder its working fine other wise I am getting error. But moving the code into live environment I can't able to give full permission to the location folder due to security issue. Please advise.

2.
I need to register this job as Oracle concurrent program and need to call every month. please advise.
Connor McDonald
August 17, 2017 - 1:14 am UTC

You dont need 777, but the *oracle* account must be able to traverse and read the directory (and its files).

Permission needed for concurrent program

A reader, September 07, 2017 - 6:32 pm UTC

Hi Connor,

Could you please explain more detail what *oracle* account which you are referring to.

If I go to unix directory all the folders having the same owner as 'applmgr' and the folders default permission '0755'.

Thanks,
Karthikeyan
Connor McDonald
September 12, 2017 - 7:40 am UTC

The account under which the oracle *database* is running

Permission issue

A reader, September 11, 2017 - 2:11 pm UTC

Please help me out on the previous post

More to Explore

Design

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