RE: How to Extract job from database to create in another database
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <ecandrietta@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 8 Oct 2012 13:29:54 -0500
Can you use database links?
I'd create a procedure in the database you want to populate and have it link
over, pull the job code out, and then programatically switch the job number
before it submits it.
Something similar to:
declare
cursor c1 is
select my job code from database_view@databaseName
where blah1 = ...
and blah2 = ...;
new_Job_id number;
rec_c1 c1%rowtype;
begin
for rec_c1 in c1 loop
select max(job)+1 into new_Job_id from user_jobs;
dbms_job.isubmit(job=>new_job_id, what=>rec_c1.what...
end loop;
end;
/
Something like that.
I know that is a mess, but hopefully you get the general idea.
Chris
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Eriovaldo Andrietta
Sent: Monday, October 08, 2012 1:21 PM
To: ORACLE-L
Subject: Re: How to Extract job from database to create in another database
Hi,
The error I am getting is:
-00017: following statement failed with ORACLE error 1:
"BEGIN DBMS_JOB.ISUBMIT(JOB=>2,WHAT=>'delete te_bo where data"
" < sysdate - 40/1440;',NEXT_DATE=>TO_DATE('2012-09-03:21:10:28','YYYY-MM-"
"DD:HH24:MI:SS'),INTERVAL=>'sysdate + 30/1440',NO_PARSE=>TRUE); END;"
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 100
ORA-06512: at line 1
When doing the import of the job that were exported from another database , the
ID exists.
So I need to do a script to extract the code and control the ID.
Any experience with this kind of issue ?
Regards
Eriovaldo
On Mon, Oct 8, 2012 at 2:47 PM, Eriovaldo Andrietta
<ecandrietta@xxxxxxxxx>wrote:
> Hi Friends,
>
> I have some jobs create in the database.
> I need to generete the command to create the same job, with same
> parameters and instructions into anoter database.
>
> Is there anyway to do it not using exp/imp ?
>
> Does the DBMS_METADATA do it ?
>
> Any experience extracting this kind of command ?
>
> Regards
> Eriovaldo
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
