From oracle-l-bounce@freelists.org Wed Oct 26 09:26:28 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9QEQSN2015721 for ; Wed, 26 Oct 2005 09:26:28 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9QEQMvX015701 for ; Wed, 26 Oct 2005 09:26:23 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 753F721083C; Wed, 26 Oct 2005 09:26:17 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13431-02; Wed, 26 Oct 2005 09:26:17 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F188821088E; Wed, 26 Oct 2005 09:26:16 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 26 Oct 2005 09:24:21 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6A0D8210985 for ; Wed, 26 Oct 2005 09:24:21 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13053-04 for ; Wed, 26 Oct 2005 09:24:21 -0500 (EST) Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.196]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C423C210629 for ; Wed, 26 Oct 2005 09:24:20 -0500 (EST) Received: by xproxy.gmail.com with SMTP id t10so350639wxc for ; Wed, 26 Oct 2005 07:24:17 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=t+bWxDs2ifYMIlH3oRPDqjY2pYz4YD2Uiq5Ebd7jQYZZGYkbs+1Uu2z8UI45VQfg7FmStozcCj0+lUadlNVYbkyKbxr0rV+LXhzTRpklZz4BGOZY8onY5qtJxiHPdua1zNKYatpelNHCxUgqusuoJpjI9s0QMMgz652Cj6Vcie0= Received: by 10.65.200.19 with SMTP id c19mr689514qbq; Wed, 26 Oct 2005 07:24:17 -0700 (PDT) Received: by 10.65.121.4 with HTTP; Wed, 26 Oct 2005 07:24:17 -0700 (PDT) Message-ID: Date: Wed, 26 Oct 2005 15:24:17 +0100 From: malcolm arnold To: t_adolph@hotmail.com Subject: Re: schedule a job every 15 mins *but* only during the office hours Cc: ORACLE-L In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Content-Disposition: inline References: X-archive-position: 27607 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: malcolmarnold@gmail.com Precedence: normal Reply-To: malcolmarnold@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham version=2.63 > Hi all, > > I'm trying to setup snapshots to run every 15 minutes during working hours, > else hourly. I'm ignoring weekends for now,... > That's a funny one. Your case statement is being evaluated as a parameter to dbms_job.submit, instead of being evaluated by the server after the job runs. You want: interval => 'case when (to_char(sysdate,''hh24'') between 8 and 20) then ''trunc(sysdate,''''MI'''') + 15/1440' -- 15 mins else ''trunc(sysdate,''''HH24'''') + 1/24'' end' (Note all the extra apostrophies...) Malcolm. -- http://www.freelists.org/webpage/oracle-l