Hi all Oracle SQL-PLSQL experts
I have a question if it is possible to rename a file located in a spesific folder in the operating system using SQL or PLSQL?
Files from the CRM temporarely stored in folder "(F:\CRM_GL_FILES\" for later import to our ERP-system IFS Applications rel 8 running on Oracle 11g database, OS Windows 2012.
These CRM-system files has an dynamic file name with a data/time included in the file name so that a file name is never equal do an other.
I starts with a fixed prefix (Ident14) followed by data/time. Example; Ident14_110820141800.csv
The program to load data into IFS Applications does not handle dymanic file names, so to import the data a simple windows .bat script runs at 1900 every evening to rename the files:
rename F:\CRM_GL_FILES\Ident14* ELWIN_BILAGSFIL_slk14.csv
Is it any possibility to rename a file in a OS folder using PLSQL or SQL so that the rename can execute from the IFS Oracle database?
I have looked into the standard package/procedure SYS.utl_file.frename, but could not find any way how insert a dynamic file name into the procedure parameters;
Example using utl_file.frename;
DECLARE
SRC_LOCATION VARCHAR2(32767);
SRC_FILENAME VARCHAR2(32767);
DEST_LOCATION VARCHAR2(32767);
DEST_FILENAME VARCHAR2(32767);
OVERWRITE BOOLEAN;
BEGIN
SRC_LOCATION := 'CRM_GL_FILES';
SRC_FILENAME := '' ---hov to insert dynamic file name???
DEST_LOCATION := ''CRM_GL_FILES';
DEST_FILENAME := ELWIN_BILAGSFIL_slk14.csv';
OVERWRITE := NULL;
SYS.UTL_FILE.FRENAME ( SRC_LOCATION, SRC_FILENAME, DEST_LOCATION, DEST_FILENAME, OVERWRITE );
COMMIT;
All suggestions are welcome.
Thanks and regards
Gunnar B Melby
System manager
Energiselskapet Buskerud
Drammen, Norway