I have below code which generates excel file. I have tested its wokring in my local machine and i have also create directory in oracle in my loacl machine database.
But now as i need to use this procedure in production where due to security reason i cannot create oracle directory then in this case the procedure is throwing error as
ORA-29280: invalid directory p
Is there any other way i can generate file in specific folder without creating directory in oracle ?
Create or replace PROCEDURE test_report IS
lv_dir VARCHAR2(4000);
lv_data_file VARCHAR2(4000) := 'test_report' || '_' ||
to_char(SYSDATE, 'dd-Mon-yyyy') ||
'.csv';
lv_err_file VARCHAR2(4000) := 'test_report' || '_' ||
to_char(SYSDATE, 'dd-Mon-yyyy') ||
'.csv';
lv_dfile utl_file.file_type;
lv_efile utl_file.file_type;
ln_err_cnt NUMBER;
ln_prc_cnt NUMBER;
BEGIN
lv_dir := 'C:\test\TEST_REPORT';
lv_dfile := utl_file.fopen(lv_dir, lv_data_file, 'W');
utl_file.fclose(lv_dfile);
utl_file.fclose(lv_efile);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(substr(sqlerrm,1,30));
END test_report;