From oracle-l-bounce@freelists.org Tue Mar 2 14:25:22 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i22KPLa13740 for ; Tue, 2 Mar 2004 14:25:21 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i22KPLo13735 for ; Tue, 2 Mar 2004 14:25:21 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D66923951CB; Tue, 2 Mar 2004 15:25:54 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Mar 2004 15:24:50 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from irvbhxw01.prod.quest.corp (irvbhxw01.quest.com [12.106.87.68]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 44FD53950A4 for ; Tue, 2 Mar 2004 15:24:48 -0500 (EST) Received: from irvmbxw02.prod.quest.corp ([10.1.2.203]) by irvbhxw01.prod.quest.corp with Microsoft SMTPSVC(5.0.2195.6713); Tue, 2 Mar 2004 12:28:04 -0800 X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C40093.BD9CDFC6" Subject: RE: Unix script help Date: Tue, 2 Mar 2004 12:20:01 -0800 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Unix script help Thread-Index: AcQAkjrj5xiF3JuqQmWxGJE4npq1WwAAIutw From: "Jacques Kilchoer" To: Cc: "M Rafiq" X-OriginalArrivalTime: 02 Mar 2004 20:28:04.0433 (UTC) FILETIME=[DD7CF410:01C40094] X-archive-position: 84 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Jacques.Kilchoer@quest.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l ------_=_NextPart_001_01C40093.BD9CDFC6 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable How about putting a PL/SQL block between the create and the truncate? = something like this whenever sqlerror then exit sql.sqlcode create table tangram.sw_save nologging tablespace tangram as select * from tangram.sw_use_t_h where sut_closed_dt =3D (sysdate-10); declare n pls_integer ; begin select count (*) into n from tangram.sw_save where rownum < 2 ; if n =3D 0 then raise_application_error (-20010, 'no rows copied') ; end if ; end ; / truncate table tangram.sw_use_t_h; insert into tangram.sw_use_t_h select * from tangram.sw_save; commit; drop table tangram.sw_save; > -----Original Message----- > M Rafiq > > I need help for following sql script. I want to put condition before > truncate that table creation must be successful before > truncate runs. The > condition may be to check count before and after creation or > tracking of any > ORA/warning message but I could not make up my mind. I want > to put it in > cron to run it weekly. > > Is any body can help to give me some tips/script itself? > > create table tangram.sw_save nologging > tablespace tangram > as select * from tangram.sw_use_t_h where sut_closed_dt > =3D > (sysdate-10); > > truncate table tangram.sw_use_t_h; > > insert into tangram.sw_use_t_h > select * from tangram.sw_save; > > commit; > > drop table tangram.sw_save; ------_=_NextPart_001_01C40093.BD9CDFC6 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

How about putting a PL/SQL block between the create = and the=20 truncate? something like this

whenever sqlerror then exit=20 sql.sqlcode

create table tangram.sw_save nologging
tablespace=20 tangram
as select * from tangram.sw_use_t_h where sut_closed_dt=20 =3D
(sysdate-10);

declare
   n pls_integer=20 ;
begin
   select count (*) into n
    = from=20 tangram.sw_save where rownum < 2 ;
   if n =3D = 0
  =20 then
      raise_application_error (-20010, = 'no rows=20 copied') ;
   end if ;
end ;
/

truncate table=20 tangram.sw_use_t_h;

insert into tangram.sw_use_t_h
select * = from=20 tangram.sw_save;

commit;

drop table = tangram.sw_save;

>=20 -----Original Message-----
> M Rafiq
>
> I need help = for=20 following sql script. I want to put condition before
> truncate = that table=20 creation must be successful before
> truncate runs. The
> = condition=20 may be to check count before and after creation or
> tracking of=20 any
> ORA/warning message but I could not make up my mind. I = want
>=20 to put it in
> cron to run it weekly.
>
> Is any body = can help=20 to give me some tips/script itself?
>
> create table = tangram.sw_save=20 nologging
> tablespace tangram
> as select * from = tangram.sw_use_t_h=20 where sut_closed_dt > =3D
> (sysdate-10);
>
> = truncate table=20 tangram.sw_use_t_h;
>
> insert into = tangram.sw_use_t_h
>=20 select * from tangram.sw_save;
>
> commit;
>
> = drop=20 table tangram.sw_save;

------_=_NextPart_001_01C40093.BD9CDFC6-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------