Hi,
Created a procedure that calls several other, smaller procedures that basically to something like this:
CREATE OR REPLACE PROCEDURE scott.procedure1 IS
my_file UTL_FILE.file_type;
my_file_name VARCHAR2 (60);
CURSOR c_data IS
SELECT
...
FROM scott.table1
where ..
;
BEGIN
SELECT 'filename_' || TO_CHAR (SYSDATE, 'yyyymmdd')||'.csv'
INTO my_file_name
FROM DUAL;
my_file := UTL_FILE.fopen ('DATADIR',my_file_name, 'w',32000);
UTL_FILE.PUT_LINE(my_file,
'column1'|| ','||
'column2'|| ','||
'column3'|| ','||
... 'column10' );
FOR cur_rec IN c_data LOOP
UTL_FILE.PUT_LINE(my_file,
'"'|| cur_rec.column1||'"'||','||
'"'|| cur_rec.column2||'"'||','||
....
'"'||cur_rec.column10||'"' );
END LOOP;
--- change the run date in table Last_run_date to reflect this run
update scott.last_run_date set table1 = to_char(sysdate,'yyyymmdd'); commit;
UTL_FILE.fclose (my_file);
END;
And there is a job scheduled to run daily calling this one, "parent" procedure. When scheduled, job fails after 7-8 seconds with ORA-29285. It's not a privilege issue since the job runs successfully when triggered manually.
Anyone have a clue what might be going on here?
Thanks in advance