Re: How to Extract job from database to create in another database
- From: Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
- To: Christopher.Taylor2@xxxxxxxxxxxx
- Date: Mon, 8 Oct 2012 15:34:58 -0300
Thanks all.
I get a solution there too:
declare
vstring varchar2(4000);
begin
dbms_job.user_export (job => 7748, mycall => vstring);
dbms_output.put_line (vstring);
end;
the result of this pl/sql block is:
dbms_job.isubmit(job=>7748,what=>'dbms_refresh.refresh(''"B731_SEMANAL"."TBNT_2"'');',next_date=>to_date('2012-10-09:01:00:00','YYYY-MM-DD:HH24:MI:SS'),interval=>'TRUNC(sysdate
+ 1) + 1/24
',no_parse=>TRUE);
Then I will replace the isubmit by submit and then submit it in the new
instance.
Great
Thanks.
Eriovaldo
On Mon, Oct 8, 2012 at 3:29 PM, <Christopher.Taylor2@xxxxxxxxxxxx> wrote:
> 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:
