Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 165731960569 for ; Wed, 11 Nov 2015 17:05:12 +0100 (CET) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTP for ; Wed, 11 Nov 2015 17:05:11 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BF55D2FD41; Wed, 11 Nov 2015 11:05:10 -0500 (EST) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id jfaNvxTzOsbP; Wed, 11 Nov 2015 11:05:10 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 239132FCA3; Wed, 11 Nov 2015 11:04:30 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 11 Nov 2015 11:03:49 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 25FE62F061 for ; Wed, 11 Nov 2015 11:03:49 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id tXddfCAuWDL6 for ; Wed, 11 Nov 2015 11:03:49 -0500 (EST) Received: from aserp1040.oracle.com (aserp1040.oracle.com [141.146.126.69]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C1B282F0A4 for ; Wed, 11 Nov 2015 11:03:48 -0500 (EST) Received: from userv0021.oracle.com (userv0021.oracle.com [156.151.31.71]) by aserp1040.oracle.com (Sentrion-MTA-4.3.2/Sentrion-MTA-4.3.2) with ESMTP id tABG3k99008043 (version=TLSv1 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Wed, 11 Nov 2015 16:03:47 GMT Received: from userv0122.oracle.com (userv0122.oracle.com [156.151.31.75]) by userv0021.oracle.com (8.13.8/8.13.8) with ESMTP id tABG3kko027304 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=FAIL); Wed, 11 Nov 2015 16:03:46 GMT Received: from abhmp0003.oracle.com (abhmp0003.oracle.com [141.146.116.9]) by userv0122.oracle.com (8.13.8/8.13.8) with ESMTP id tABG3kdr010962; Wed, 11 Nov 2015 16:03:46 GMT MIME-Version: 1.0 Message-ID: <71bbfac0-4416-42e7-a632-7d7d0ee1a936@default> Date: Wed, 11 Nov 2015 08:03:20 -0800 (PST) From: Jeff Smith To: woody.mckay@gmail.com, ORACLE-L Subject: RE: Create Job Example References: In-Reply-To: Content-Type: multipart/alternative; boundary="__1447257826074163981abhmp0003.oracle.com" X-Source-IP: userv0021.oracle.com [156.151.31.71] X-archive-position: 62229 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: jeff.d.smith@oracle.com Precedence: normal Reply-To: jeff.d.smith@oracle.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --__1447257826074163981abhmp0003.oracle.com Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Do you use SQL Developer? =C2=A0 If you do, we have a job wizard, it helps you setup the procedure via GUI b= ut also gives you the PL/SQL block. =C2=A0 The number_of_arguments bit and then the set_job_argument_value() calls are= what you=E2=80=99re interested in I believe. =C2=A0 Jeff =C2=A0 =C2=A0 BEGIN =C2=A0=C2=A0=C2=A0 DBMS_SCHEDULER.CREATE_JOB ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 job_name= =3D> '"SQLDEVDEMO"."CALLS_SP"', =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 job_type= =3D> 'STORED_PROCEDURE', =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 job_acti= on =3D> 'SQLDEVDEMO.UNTAPPD_PROC', =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 number_o= f_arguments =3D> 1, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 start_da= te =3D> NULL, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 repeat_i= nterval =3D> NULL, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 end_date= =3D> NULL, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 enabled = =3D> FALSE, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 auto_dro= p =3D> FALSE, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 comments= =3D> 'calls an sp for example'); =C2=A0 =C2=A0=C2=A0=C2=A0 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0job_name =3D> '"SQLDEVDEMO"."CALLS_SP"',=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0argument_position =3D> 1,=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0argument_value =3D> '5'); =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0DBMS_SCHEDULER.SET_ATTRIBUTE(=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0name =3D> '"SQLDEVDEMO"."CALLS_SP"',=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0attribute =3D> 'store_output', value =3D> TRUE); =C2=A0=C2=A0=C2=A0 DBMS_SCHEDULER.SET_ATTRIBUTE(=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0name =3D> '"SQLDEVDEMO"."CALLS_SP"',=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0attribute =3D> 'logging_level', value =3D> DBMS_SCHEDULER.LOGGING_OFF); =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=20 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0DBMS_SCHEDULER.enable( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 na= me =3D> '"SQLDEVDEMO"."CALLS_SP"'); END; =C2=A0 From: Woody McKay [mailto:woody.mckay@gmail.com]=20 Sent: Wednesday, November 11, 2015 10:53 AM To: ORACLE-L Subject: Create Job Example =C2=A0 Hi, =C2=A0 Does anyone have a quick example of how to create a job that calls a proced= ure and passes 4 parameters to that procedure? =C2=A0 The docs are confusing to me regarding this and haven't found a good exampl= e yet. I see the NUMBER_OF_ARGUMENTS parameter, but don't see where values = can be passed to the procedure in the job.=C2=A0 =C2=A0 This is for Apex 5 on 12.1.0.2.=C2=A0 From the Apex page (on button click),= we need to call a procedure that maintains users.=C2=A0 Someone had said t= his would best be done via a job. I appears that the procedure can not be c= alled directly from Apex due to needed security privs. =C2=A0 --=20 Sincerely, Woody --__1447257826074163981abhmp0003.oracle.com Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

Do you us= e SQL Developer?

&nbs= p;

If you do, we have a job wiz= ard, it helps you setup the procedure via GUI but also gives you the PL/SQL= block.

 <= /span>

The number_of_arguments bit and then = the set_job_argument_value() calls are what you=E2=80=99re interested in I = believe.

 =

Jeff

 

 

BEGIN

=C2=A0=C2=A0=C2=A0 DBMS_SCHEDULER.CREA= TE_JOB (

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 job_name =3D> '"= ;SQLDEVDEMO"."CALLS_SP"',

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 job_type =3D> 'STORED_PROCEDURE',

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 job_action =3D> 'SQLDEVDEMO.UNTAPPD_PROC',

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 number_of_arguments =3D> 1,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 start_date =3D> NULL,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 repeat_interval =3D> NULL,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 end_date =3D> NULL,

= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 e= nabled =3D> FALSE,

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 auto_drop = =3D> FALSE,

=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 comments =3D> = 'calls an sp for example');

 

=C2=A0=C2=A0=C2= =A0 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0job_name =3D> '"SQLDEVDEMO"."CALL= S_SP"',

=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0argument_= position =3D> 1,

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= argument_value =3D> '5');

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0

<= p class=3DMsoNormal>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=

=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0DBMS_SCHEDULER.SET_ATTRIBUTE(

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0name =3D> '"SQLDEVDEMO= "."CALLS_SP"',

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0attribute =3D> 'store_output', value =3D> TRUE);<= /span>

=C2=A0=C2=A0=C2=A0 DBMS_SCHEDULER.SET= _ATTRIBUTE(

=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0name =3D&= gt; '"SQLDEVDEMO"."CALLS_SP"',

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0attribute =3D> 'logging_level', value =3D&= gt; DBMS_SCHEDULER.LOGGING_OFF);

= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0

=C2=A0=C2=A0=C2=A0

<= span style=3D'font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F= 497D'>=C2=A0=C2=A0

=C2=A0= =C2=A0=C2=A0=C2=A0

=C2=A0= =C2=A0=C2=A0=C2=A0DBMS_SCHEDULER.enable(

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 name =3D> '"SQLDEVDEMO"."CALLS_SP"');

END;

<= p class=3DMsoNormal> 

From: Woody McKay [mailto:woody.mckay@gmail.com]
Sent: W= ednesday, November 11, 2015 10:53 AM
To: ORACLE-L
Subject:<= /b> Create Job Example

 

Hi,

 

Does anyo= ne have a quick example of how to create a job that calls a procedure and p= asses 4 parameters to that procedure?

 

The docs are = confusing to me regarding this and haven't found a good example yet. I see = the NUMBER_OF_ARGUMENTS parameter, but don't see where values can be passed= to the procedure in the job. 

 

This is for Ape= x 5 on 12.1.0.2.  From the Apex page (on button click), we need to cal= l a procedure that maintains users.  Someone had said this would best = be done via a job. I appears that the procedure can not be called directly = from Apex due to needed security privs.

=

 

--

Sincerely,

Woody

--__1447257826074163981abhmp0003.oracle.com-- -- http://www.freelists.org/webpage/oracle-l