Loading ...
Sorry, an error occurred while loading the content.

58546RE: [toad] Re: Spool

Expand Messages
  • SHIVANAND BHARTI
    Jul 22, 2013
    • 0 Attachment

      This is what I was looking for.

       

      Thanks,

      Shiva

       

      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Gregory Liss
      Sent: Monday, July 22, 2013 3:00 PM
      To: toad@yahoogroups.com
      Subject: [toad] Re: Spool

       

       

      Maybe something like this:

      SET TIMING OFF
      SET SERVEROUTPUT ON
      SET TERMOUT OFF
      SET VERIFY OFF
      SET HEADING OFF
      SET ECHO OFF
      SET FEEDBACK OFF

      -- build temp file to run all from
      SPOOL c:\temp\run_all.sql

      BEGIN
      FOR counter IN 0 .. 29
      LOOP
      -- create spool file to output each item to
      DBMS_OUTPUT.put_line ( 'spool c:\temp\file_' || TO_CHAR (SYSDATE - counter, 'yymmdd') || '.txt');
      -- substitute your query here
      DBMS_OUTPUT.put_line ('select TO_DATE(''' || TO_CHAR (SYSDATE - counter) || ''') from dual;');
      -- close the spool
      DBMS_OUTPUT.put_line ('Spool off');
      END LOOP;
      END;
      -- close temp file spool
      SPOOL OFF;
      -- run temp file
      @c:\temp\run_all.sql
      /

      --- In toad@yahoogroups.com, SHIVANAND BHARTI <SBharti@...> wrote:
      >
      > To keep it simple let's say last 30 days ... sysdate, sysdate - 1 , sysdate -2,...,sysdate-29
      >
      > Thanks,
      > Shiva
      >
      > From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of gene.l.bradley
      > Sent: Monday, July 22, 2013 1:43 PM
      > To: toad@yahoogroups.com
      > Subject: Re: [toad] Spool
      >
      >
      > How are you determining what the dates are.
      >
      > You could put the dates in a temporary table
      > and then say where dateVariable in (select myDate from temp_table);
      >
      > On Mon, Jul 22, 2013 at 12:31 PM, SHIVANAND BHARTI <SBharti@...<mailto:SBharti@...>> wrote:
      >
      > Hi folks ... I have a query with bind variable which accepts date.
      > How can I run thais query for 30 different dates and get it's output in a file.
      >
      > I can write the same query 30 times with different date and run it as ascript.
      > Or write a PL/SQL block.
      >
      > But it is an one time thing and I am looking for something quick .
      > Is there an option in Toad by which I can spool out the output of a query for different input for the same bind variable ?
      >
      > For example how can I run this query for 30 different dates and spool it's output.
      >
      > SELECT :RunDate ,
      > ( SELECT COUNT(*)
      > FROM store
      > WHERE pstore_open_date =:RunDate ) store_count
      > ,( SELECT COUNT(*)
      > FROM wh
      > WHERE wh_open_date =:RunDate ) ) wh_count
      > FROM dual;
      >
      >
      > Thanks,
      > Shiva
      >
      >
      > ----------------------------------------------------------
      >
      > NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of the specific individual or individuals to which it is addressed. If you are not an intended recipient of this e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error, notify the person named above by reply e-mail and please delete it. Thank you.
      >
      >
      >
      > --
      > Gene L. Bradley Jr.
      > Software Developer I
      > Information Systems & Integration
      > Jackson State University
      > 1400 J R Lynch Street
      > P.O. Box 17750
      > Jackson, MS 39217
      >
      > ph 601.979.1042
      > fax 601.371.9146
      > email gbradley@...<mailto:gbradley@...>
      >
      >
      >
      >
      > ----------------------------------------------------------
      >
      > NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of
      > the specific individual or individuals to which it is addressed. If you are not an intended recipient of this
      > e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the
      > information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error,
      > notify the person named above by reply e-mail and please delete it. Thank you.
      >


      -------------------------------------------------------------------------------------

      NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of the specific individual or individuals to which it is addressed. If you are not an intended recipient of this e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error, notify the person named above by reply e-mail and please delete it. Thank you.

    • Show all 8 messages in this topic