Re: Invoker Rights - Am I missing Something.

Check the params for dbms_job, I believe there is a start time and a next date parameter. Obviously if you want it to only run once you don't have to set the next date. Fool around with these params and see if it makes any difference. If you look in ORACLE_HOME/dbms/admin (on the server where oracle is installed) there is a file called dbmsjob.sql (or something like that) that has some samples of how to call the procedures in the package.

Scott.

"SMiller" <____steve.miller_at_redeye.com> wrote in message news:dsUK6.62244$PP3.4253463_at_nnrp3.clara.net...
> Close Scott....but not quite.
>
> I made your suggested code changes but the job is still sitting in the
> queue. At least the insert is commited when I force it to run now.
>
> I can perform an exec dbms_job.run(xxx); and the job will run and be
 cleared
> from the queue, but it won't happen from the call to the package.
>
> Any other ideas?
>
>
>
> "Scott Watson" <swatson_at_datachest.com> wrote in message
> news:vaUK6.16007$yw.546717_at_news20.bellglobal.com...
> > I read somewhere that after submitting a job it is always advisable to
 issue
> > a commit; I don't remember the exact reason but it had something to do
 with
> > the job not running.
> >
> > However to get you code to work as you laid it out I would do something
 like
> >
> > stmnt := 'insert into test values (1, 2, 3); commit;';
> > DBMS_JOB.submit(jobnum, stmnt);
> > commit;
> >
> >
> > HTH
> > Scott.
> >
> > "SMiller" <____steve.miller_at_redeye.com> wrote in message
> > news:HRTK6.61461$PP3.4242226_at_nnrp3.clara.net...
> > > Oracle 8.1.7 on Linux.
> > > ----------------------
> > > I have 2 users created......A and B.
> > >
> > > I perform the following in B.
> > > create table test (one number, two number, three number);
> > > grant insert, update, delete on test to public;
> > >
> > > Then, I perform the following in A.
> > >
> > > CREATE OR REPLACE PACKATE testme1
> > > authid current_user IS
> > >
> > > Procedure process;
> > >
> > > end testme1;
> > >
> > > CREATE OR REPLACE PACKAGE BODY testme1 IS
> > >
> > > procedure process is
> > > jobnum INTEGER;
> > > stmnt VARCHAR2(1000);
> > > x integer;
> > > BEGIN
> > > insert into redeyedev.test values (9, 9, 9);
> > > commit;
> > > stmnt := 'insert into test values (1, 2, 3);';
> > > DBMS_JOB.submit(jobnum, stmnt);
> > > stmnt := 'commit;';
> > > DBMS_JOB.submit(jobnum, stmnt);
> > >
> > > END process;
> > >
> > > end testme1;
> > >
> > > Finally I perform the following in B.
> > > exec a.testme1.process;
> > >
> > > Table test has 9, 9, 9 inserted as expected.
> > > BUT
> > > DBA_JOBS has the job in the jobqueue but it does not get run.
> > > Table test does not have 1, 2, 3 inserted as the job has not been
 executed.
> > >
> > > Am I missing a grant somewhere, or is the above example not possible?
> > >
> > > Is there a better way of doing this?
> > >
> > >
> > >
> > >
> >
> >
>
>
Received on Fri May 11 2001 - 12:51:54 CDT