Tags: database, dbms_job, job, mysql, oracle, output, outputi, schedule, somebody, spool, sql, submit
how to spool the output of dbms_job in Oracle 9i R2
I am trying to schedule a job using dbms_job.submit.
could somebody help me to spool the output?
I know we cannot use spool with dbms_job.Is there any way to see the output or write to some other file? If so, pls give me steps to do that.
Leave a comment...
- 14 Comments
- I think you'll need to use UTL_FILE. Use CREATE DIRECTORY to create the output directory first, then add the UTL_FILE command(s) to you procedure.#1; Sat, 23 Feb 2008 17:01:00 GMT
I think this metalink note will be usefull to you.
I think this is the question you are looking for ...
How To Use dbms_job.submit When NEXT_DATE Is Prior To SYSDATE
Metalink note :273362.1
Venkat...#2; Sat, 23 Feb 2008 17:02:00 GMT
- Thanks Krishnam Raju..
But thts not my issue.
I want to capture the output of a procedure that I submit thru dbms_job.
Something like spool......problem is spool doesn't work with dbms_job.
Looks like I have to use UTL_file.But i am not clear how exactly you do that.
I appreciate your help.
Thanks#3; Sat, 23 Feb 2008 17:03:00 GMT
- Basically, here's the steps:
1) create the directory where your output will be written - for example:
CREATE OR REPLACE DIRECTORY DATA_FILES AS '/ora00/user_files';
2) grant read and write permission on that directory to your Oracle user executing the procedure: GRANT READ, WRITE ON DIRECTORY DATA_FILES TO SCOTT;
3) write and test your procedure: for example:
CREATE OR REPLACE PROCEDURE P_utlfile_test
p_fac_ID IN agency.agency_id%TYPE)
-- Data Variables
-- UTL_FILE variables
where agency_id = p_fac_ID;
-- Open the file
fh := UTL_FILE.fopen ('DATA_FILES', 'file_test.txt', 'w'); <-- DATA_FILES is the directory name, file_test.txt is the name of the file you're creating, w means write to the file.
UTL_FILE.put (fh, v_data);
-- pass 0 back as transid indicating no file generated
raise_application_error(-20101, 'Exception in p_utlfile_test: ' || SQLERRM);
Once it's working, run it using DBMS_JOB#4; Sat, 23 Feb 2008 17:04:00 GMT
- Harryb--U are just marvellous.
Nobody was able to give me this detailed answer..all the so called oracle experts.
U r the real man needed for forums.
Thanks for your time & patience.
I appreciate your help a lot.
This will help me to do most of my work as jobs.
Thanks again.#5; Sat, 23 Feb 2008 17:05:00 GMT
Below are my steps i did to use the utl_file with ur help.
sql>create or replace directory dir_test as '/oracle/mydir';
sql>grant read,write on directory dir_test to souj;
lines from my procedure are:
--calling the directory and opening the file--
fh := UTL_FILE.fopen ('/oracle/mydir/DIR_TEST', 'file_test.txt', 'w');
--writing to the file--
UTL_FILE.put_line(fh, 'This is a test');
--closing the file--
But when i do ls -ltr being in /oracle/mydir on OS, i dont see the directory listed.But when i do,
sql>select * from all_directories, I see the directory.So it means that I do have the access to read & write on that directory.
Whan i execute the procedure, it gives the below error:
ORA-06512: at "BMS.TESTPROC", line 44
ORA-06512: at line 1
Line 44 is the line with the invalid_filehandle exception.
what do u think is the problem?Am i doing something wrong?Should the directory be listed in OS?
Thanks for your help.#6; Sat, 23 Feb 2008 17:06:00 GMT
- You will need to create the directory at the OS level using OS commands (mkdir). The 'create directory' SQL command creates an object in the database that refers to the directory, but doesn't create the directory itself.
Harry#7; Sat, 23 Feb 2008 17:07:00 GMT
- thanks for the response...
I did create the same directory like:
Now i can see that direcory listed and I also gave r,w,x permissions:
$chmod 777 DIR_TEST
But when I execute the procedure, I am getting the same error i.e invalid file_handle.
Any thoughts?#8; Sat, 23 Feb 2008 17:08:00 GMT
- Instead of this line:
fh := UTL_FILE.fopen ('/oracle/mydir/DIR_TEST', 'file_test.txt', 'w')
fh := UTL_FILE.fopen ('DIR_TEST', 'file_test.txt', 'w')
You need the name of the Oracle directory in the fopen statement, not the path. The path is referenced in the directory.#9; Sat, 23 Feb 2008 17:09:00 GMT
- yep..I changed it and tried with just directory name...still the same error#10; Sat, 23 Feb 2008 17:10:00 GMT
- Can you post your entire procedure?#11; Sat, 23 Feb 2008 17:11:00 GMT
- CREATE OR REPLACE procedure RebuildFragIndexes(schema_owner varchar2)
pMaxHeight integer := 4;
pMaxLeafsDeleted integer := 20;
vCount integer := 0;
for c1 in ( select i.index_name
from sys.dba_indexes i, sys.dba_segments s
AND i.index_type = 'NORMAL'
and i.index_name = s.segment_name )
execute immediate 'analyze index ' || schema_owner || '.' || c1.index_name ||
' validate structure';
FOR C2 IN (select name index_name
WHERE (height > pMaxHeight)
( lf_rows > 0
and del_lf_rows > 0
and (del_lf_rows* 100 / lf_rows)>pMaxLeafsDeleted
vCount := vCount + 1;
fhandle := UTL_FILE.fopen ('DIR_TEST', 'file_test.txt', 'w');
dbms_output.put_line('File opened for writing');
UTL_FILE.put_line(fhandle, 'Rebuilding index ' || schema_owner || '.' || C2.index_name || '...');
execute immediate 'alter index ' || schema_owner|| '.'|| C2.index_name || ' rebuild' ||
' nologging compute statistics';
UTL_FILE.put_line(fhandle,'Total Indexes rebuilt for ' || schema_owner || ': ' || to_char(vCount));
vCount := 0;
/#12; Sat, 23 Feb 2008 17:12:00 GMT
- The default linesize 1023 bytes.
It would be better to add the linesize when we open the file.
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
Tamil#13; Sat, 23 Feb 2008 17:13:00 GMT
- Something I should have thought about early on - in your init.ora file, do you have the parameter UTL_FILE_DIR defined? It should be pointing to ''/oracle/mydir':
If you're using spfile, then you can do this:
alter system set utl_file_dir='/oracle/mydir' scope=spfile#14; Sat, 23 Feb 2008 17:14:00 GMT