| UTL_FILE from SUBMIT [message #370414] |
Wed, 22 September 1999 14:15  |
Mario
Messages: 10
Registered: September 1999
|
Junior Member |
|
|
HI,
does any one has tried to call utl_file from a submitted procedure?
My procedure runs well when I call it directly (interactive) but fails when I try to submit.
It's not a submit proplem, because other submit work well.
All I get is a failure for every run in USER_JOBS table and an ORA-12012 from SYS.UTL_FILE line 101, in the Oracle log.
Does anyone knows of a limitation of UTL_FILE in batch?
Thanks,
Mario
|
|
|
|
| Re: UTL_FILE from SUBMIT [message #370419 is a reply to message #370414] |
Wed, 22 September 1999 15:46  |
MikeG
Messages: 14
Registered: August 1999
|
Junior Member |
|
|
|
I am doing this without any problems. I would suggest looking in the alert log and maybe you can get more info about the error encountered.
|
|
|
|
| Re: UTL_FILE from SUBMIT [message #370426 is a reply to message #370419] |
Thu, 23 September 1999 11:25 |
Mario
Messages: 10
Registered: September 1999
|
Junior Member |
|
|
Thanks for the good news, but I still can't make it work.
I'm working on Oracle 8.0.5.0.0 on Sun Solaris.
Here a test version I'm trying (see below),
when I do EXEC MARIO.TOTO; in sqlplus it works
fine i.e. I get my file, but in batch I always get a failure.
Here's my code:
CREATE OR REPLACE PACKAGE mario AS
PROCEDURE RemoveToto;
PROCEDURE toto;
END mario; --package
/
CREATE OR REPLACE PACKAGE BODY mario AS
PROCEDURE RemoveToto
IS
CURSOR job_cur IS -- old jobs to be removed
SELECT job FROM user_jobs
WHERE what LIKE '%mario.toto%';
BEGIN
FOR job_rec IN job_cur LOOP
DBMS_JOB.REMOVE(job_rec.job);
END LOOP;
END RemoveToto;
PROCEDURE toto
IS
f UTL_FILE.FILE_TYPE := NULL;
step VARCHAR(200);
BEGIN
step := 'Step 1 : FOPEN ';
f := UTL_FILE.FOPEN('/develop/rtdas/v6.0/log', 'toto.txt', 'a');
step := 'Step 2 : PUT_LINE ';
UTL_FILE.PUT_LINE(f, 'test from toto ' || sysdate);
step := 'Step 3 : FCLOSE ';
UTL_FILE.FCLOSE(f);
step := 'Step 4 : end ';
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20000, step || ' : INVALID_PATH');
WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20000, step || ' : INVALID_MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20000, step || ' : INVALID_FILEHANDLE');
WHEN UTL_FILE.INVALID_OPERATION THEN
RAISE_APPLICATION_ERROR(-20000, step || ' : INVALID_OPERATION');
WHEN UTL_FILE.READ_ERROR THEN
RAISE_APPLICATION_ERROR(-20000, step || ' : READ_ERROR');
WHEN UTL_FILE.WRITE_ERROR THEN
RAISE_APPLICATION_ERROR(-20000, step || ' : WRITE_ERROR');
WHEN UTL_FILE.INTERNAL_ERROR THEN
RAISE_APPLICATION_ERROR(-20000, step || ' : INTERNAL_ERROR');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, step || TO_CHAR(SQLCODE) || ': ' || SQLERRM);
END toto;
END mario; -- body
/
describe mario
DECLARE
JobNo NUMBER;
BEGIN
mario.RemoveToto;
DBMS_JOB.SUBMIT
(JOB => JobNo,
WHAT => 'BEGIN mario.toto; END;',
NEXT_DATE => SYSDATE, -- run now
INTERVAL => 'SYSDATE + 1/1440'); -- run again in 1 minute
COMMIT;
END;
/
|
|
|
|
| Re: UTL_FILE from SUBMIT *** FOUND IT *** [message #370427 is a reply to message #370419] |
Thu, 23 September 1999 13:08 |
Mario
Messages: 10
Registered: September 1999
|
Junior Member |
|
|
Found the problem !
I'm not 100% sure why, but the problem is unix privilege on the directory where the file is.
As I understand, running as my own user I have group access by my OS USER, but when Oracle tries to submit it doesn't have the same OS USER.
What I'm still not so sure about is,
how come the file is owned (created) by Oracle when I execute from my account and then Oracle has no access to his own file when it runs in batch mode under my oracle user?
Thanks for your time and ideas,
Mario
|
|
|
|
| Re: UTL_FILE from SUBMIT [message #371547 is a reply to message #370414] |
Mon, 06 November 2000 00:05 |
vikrams
Messages: 23
Registered: November 2000
Location: Dubai
|
Junior Member |
|
|
Can you send me The body for UTL_FILE procedure.... i have the UTL_FILE package which have procedures and functions deaclared (FOPEN,FCLOSE,PUTF etc..)in it. when i use those functions or procedures.. oracle gives an error..... "package body "abc.UTL_FILE" does not exist"..
Please help me..
THANKS in advance..
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
