Alternative
Racer I., August 15, 2017 - 9:57 am UTC
Maybe you can see what's there with :
v$diag_dir_ext
?
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.
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.
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
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