Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

DBMS_DATAPUMP; how to get the log file of a job?

Andrei KübarDec 23 2011 — edited Dec 23 2011

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

009

Do you know what makes numbers prime? Do you know how to write a PL/SQL code, then there you are!!! (Or search for the codes online). Please don't treat the forum as class room training providing institution.

*009*

Edited by: 009 on Aug 25, 2010 10:50 PM

Lokanath Giri

 declare
   Flag Boolean:=FALSE;
   begin
   for i in 2..10 LOOP
     for j in 2..i-1 LOOP
      if mod(i,j)=0 then
          DBMS_OUTPUT.PUT_LINE(i||' Not a PRIME');
          Flag:=TRUE;
          exit;
      end if;
     end loop;
        IF Flag != TRUE THEN
          DBMS_OUTPUT.PUT_LINE(i||' PRIME');
        END IF;
        Flag:=FALSE;
     end loop;
  end;
/
2 PRIME
3 PRIME
4 Not a PRIME
5 PRIME
6 Not a PRIME
7 PRIME
8 Not a PRIME
9 Not a PRIME
10 Not a PRIME

737905

Try this:

SQL> ed
Wrote file afiedt.buf
 
  1  DECLARE
  2  v_num NUMBER := &1;
  3  v_prime_flag NUMBER := 0;
  4  BEGIN
  5  FOR I IN 1..v_num LOOP
  6  FOR J IN 2..TRUNC(I/2) LOOP
  7  IF (MOD(I,J) = 0) THEN
  8  v_prime_flag := 1;
  9  EXIT;
 10  END IF;
 11  END LOOP;
 12  CASE (v_prime_flag)
 13  WHEN 0 THEN
 14  DBMS_output.put_line(I ||' is a prime Number');
 15  WHEN 1 THEN
 16  DBMS_output.put_line(I ||' is not a prime Number');
 17  END CASE;
 18  v_prime_flag := 0;
 19  END LOOP;
 20* END;
SQL> / 
Enter value for 1: 10
old   2: v_num NUMBER := &1;
new   2: v_num NUMBER := 10;
1 is a prime Number
2 is a prime Number
3 is a prime Number
4 is not a prime Number
5 is a prime Number
6 is not a prime Number
7 is a prime Number
8 is not a prime Number
9 is not a prime Number
10 is not a prime Number
 
PL/SQL procedure successfully completed.
 
SQL> / 
Enter value for 1: 20
old   2: v_num NUMBER := &1;
new   2: v_num NUMBER := 20;
1 is a prime Number
2 is a prime Number
3 is a prime Number
4 is not a prime Number
5 is a prime Number
6 is not a prime Number
7 is a prime Number
8 is not a prime Number
9 is not a prime Number
10 is not a prime Number
11 is a prime Number
12 is not a prime Number
13 is a prime Number
14 is not a prime Number
15 is not a prime Number
16 is not a prime Number
17 is a prime Number
18 is not a prime Number
19 is a prime Number
20 is not a prime Number
 
PL/SQL procedure successfully completed.
 
SQL> / 
Enter value for 1: 12
old   2: v_num NUMBER := &1;
new   2: v_num NUMBER := 12;
1 is a prime Number
2 is a prime Number
3 is a prime Number
4 is not a prime Number
5 is a prime Number
6 is not a prime Number
7 is a prime Number
8 is not a prime Number
9 is not a prime Number
10 is not a prime Number
11 is a prime Number
12 is not a prime Number
 
PL/SQL procedure successfully completed.

Lokanath Giri

a prime number (or a prime) is a natural number that has exactly two distinct natural number divisors: 1 and itself.
so 1 is not a prime number.

This is for your info.

Edited by: Lokanath Giri on २६ अगस्त, २०१० १२:४२ अपराह्न

BluShadow

Anyone for a bit of recursion? :D

create or replace function test_prime(p_prime in number) return varchar2 is
  function f_prime(p_prime in number, p_divisor in number) return number is
  begin
    return case when p_divisor >= p_prime then 0 else case when mod(p_prime, p_divisor) = 0 then 1 else 0 end + f_prime(p_prime, p_divisor+1) end;
  end;
begin
  return case when f_prime(p_prime, 2) > 0 or p_prime = 1 then 'Not ' else null end || 'Prime';
end;
/


SQL> select rownum, test_prime(rownum) as prime from dual connect by rownum <= 50;

    ROWNUM PRIME
---------- ----------
         1 Not Prime
         2 Prime
         3 Prime
         4 Not Prime
         5 Prime
         6 Not Prime
         7 Prime
         8 Not Prime
         9 Not Prime
        10 Not Prime
        11 Prime
        12 Not Prime
        13 Prime
        14 Not Prime
        15 Not Prime
        16 Not Prime
        17 Prime
        18 Not Prime
        19 Prime
        20 Not Prime
        21 Not Prime
        22 Not Prime
        23 Prime
        24 Not Prime
        25 Not Prime
        26 Not Prime
        27 Not Prime
        28 Not Prime
        29 Prime
        30 Not Prime
        31 Prime
        32 Not Prime
        33 Not Prime
        34 Not Prime
        35 Not Prime
        36 Not Prime
        37 Prime
        38 Not Prime
        39 Not Prime
        40 Not Prime
        41 Prime
        42 Not Prime
        43 Prime
        44 Not Prime
        45 Not Prime
        46 Not Prime
        47 Prime
        48 Not Prime
        49 Not Prime
        50 Not Prime

50 rows selected.

SQL>

MichaelS

SQL> with t as (    select level n
                 from dual
           connect by level <= 50)
select n,
       case
          when exists
                  (select null
                     from t t2
                    where t2.n > 1 and t2.n < t.n and t.n / t2.n = trunc (t.n / t2.n)) then 'no prime'
          else 'prime'
       end "Prime?"
  from t
/
         N Prime?  
---------- --------
         1 prime   
         2 prime   
         3 prime   
         4 no prime
         5 prime   
         6 no prime
         7 prime   
         8 no prime
         9 no prime
        10 no prime
        11 prime   
        12 no prime
        13 prime   
        14 no prime
        15 no prime
        16 no prime
        17 prime   
        18 no prime
        19 prime   
        20 no prime
        21 no prime
        22 no prime
        23 prime   
        24 no prime
        25 no prime
        26 no prime
        27 no prime
        28 no prime
        29 prime   
        30 no prime
        31 prime   
        32 no prime
        33 no prime
        34 no prime
        35 no prime
        36 no prime
        37 prime   
        38 no prime
        39 no prime
        40 no prime
        41 prime   
        42 no prime
        43 prime   
        44 no prime
        45 no prime
        46 no prime
        47 prime   
        48 no prime
        49 no prime
        50 no prime

50 rows selected.

BluShadow

Nice one Michael! ;)

Sven W.

There are many ways to create some prime numbers. Here is another one:

all output is prime

select level*level+level+41 prime
from dual
connect by level < 40;


PRIME
-----
43
47
53
61
71
83
97
113
131
151
173
197
223
251
281
313
347
383
421
461
503
547
593
641
691
743
797
853
911
971
1033
1097
1163
1231
1301
1373
1447
1523
1601

Aketi Jyuuzou

Please do not forget model clause ;-)

select *
  from dual
 model
dimension by(2 as soeji)
measures(0 as cnt)
rules(
cnt[for soeji from 2 to 25 increment 1] order by soeji=
count(*)[cv() > soeji and mod(cv(),soeji) = 0]);

SOEJI  CNT
-----  ---
    2    0
    3    0
    4    1
    5    0
    6    2
    7    0
    8    2
    9    1
   10    2
   11    0
   12    4
   13    0
   14    2
   15    2
   16    3
   17    0
   18    4
   19    0
   20    4
   21    2
   22    2
   23    0
   24    6
   25    1

789071

As for WikiPedia..i found '1' is not prime number So the Programe is
http://en.wikipedia.org/wiki/Prime_number

DECLARE

n NUMBER;

i NUMBER;

pr NUMBER;

BEGIN

FOR n IN 2 .. 100
LOOP

pr := 1;

FOR i IN 2 .. n / 2
LOOP

IF MOD(n, i) = 0 THEN

pr := 0;

END IF;

END LOOP;

IF pr = 1 THEN

DBMS_OUTPUT.PUT_LINE(n);

END IF;

END LOOP;

END;

OUT PUT:
anonymous block completed

2
3
5
7
11
13
17
19
23
29
31
37
41
43
47
53
59
61
67
71
73
79
83
89
97

Enjoy...

Edited by: Ashok on Aug 26, 2010 5:34 AM

Edited by: Ashok on 26-Aug-2010 05:44

Regards,
Ashok Pantam

Edited by: Ashok on 26-Aug-2010 20:37

BluShadow

Aketi Jyuuzou wrote:
Please do not forget model clause ;-)

No! Please! Let me forget the model clause. ;)

Sven W.

BluShadow wrote:

Aketi Jyuuzou wrote:
Please do not forget model clause ;-)

No! Please! Let me forget the model clause. ;)

I think this is a task where the model clause really shines. :)

672680

Based on Michael`s:

with t as (    select level n
                 from dual
           connect by level <= 50)           
           
select n from t where not exists
                  (select null
                     from t t2
                    where t2.n > 1 and t2.n < t.n and t.n / t2.n = trunc (t.n / t2.n));
-------
1
2
3
5
7
11
13
17
19
23
29
31
37
41
43
47

789071

Hai..user8731258

is it my program use full to u?

BluShadow

Ashok wrote:
Hai..user8731258

is it my program use full to u?

Everyone's method is useful to the OP if it generates prime numbers. Anyone would think you were asking for points. Shame on you!

1 - 15

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 20 2012

Added on Dec 23 2011

#11g, #general-database-discussions, #oracle

8 comments

2,676 views

Oracle Database Discussions

DBMS_DATAPUMP; how to get the log file of a job?

Comments

Post Details