Hi
I want the user to be able to see the logfile of his job from another session.
this is my procedure
create or replace procedure get_job_log (p_job_name IN varchar2 )
is
hdl_job number;
l_job_state VARCHAR2 (20);
l_status sys.ku$_Status1010;
l_job_status sys.ku$_JobStatus1010;
l_job_logentry sys.ku$_LogEntry1010;
l_job_logline sys.ku$_LogLine1010;
begin
hdl_job := DBMS_DATAPUMP.ATTACH(
job_name => p_job_name
,job_owner => 'CLONE_USER'
) ;
DBMS_DATAPUMP.GET_STATUS(
handle => hdl_job
,mask => dbms_datapump.ku$_status_job_error + dbms_datapump.ku$_status_job_status + dbms_datapump.ku$_status_wip
--,timeout => 15
,job_state =>l_job_state
,status =>l_status);
l_job_logentry:=l_status.wip ;
for x in l_job_logentry.first .. l_job_logentry.last loop
dbms_output.put_line (l_job_logentry(x).LogText) ;
end loop;
dbms_datapump.detach(hdl_job);
end;
/
when I run it for the first time, it works... kindof...
but my problem is that if I try running it again I get:
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 902
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3407
if I close sqlplus session , open new one and run - it works. So what is the issue here? Is detach not enough? What else should I do?
my version is 11.1.0.6
UPDATE.
Looks like the above is not true. It doesn't error if it runs to the end of code... The problem seems to happen only when exception occures and .detach is not called. So this is not an issue anymore.
But I still don't get a decent performance from the .get_status function. Sometimes it just hangs and errors on timeout.
Still the question is: How can I get the LOG of the session using DBMS_DATAPUMP from another session and output it on the screen?...
Edited by: Andrei Kübar on Dec 23, 2011 12:44 PM
Edited by: Andrei Kübar on Dec 23, 2011 12:57 PM