RE: Re: Spool
SHIVANAND BHARTI <[email protected]> Mon, 22 Jul 2013 20:46:19 +0000
| Newsgroups | gmane.comp.db.oracle.toad.free |
|---|---|
| Message-ID | <B75962E88A72F3438A4EC07F37C0375B0D897D@NCEXDAG02.familydollar.com> |
--_000_B75962E88A72F3438A4EC07F37C0375B0D897DNCEXDAG02familydo_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable This is what I was looking for. Thanks, Shiva From: [email protected] [mailto:[email protected]] On Behalf Of Grego= ry Liss Sent: Monday, July 22, 2013 3:00 PM To: [email protected] 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 [email protected]<mailto:toad%40yahoogroups.com>, SHIVANAND BHART= I <SBharti@...<mailto:SBharti@...>> wrote: > > To keep it simple let's say last 30 days ... sysdate, sysdate - 1 , sysda= te -2,...,sysdate-29 > > Thanks, > Shiva > > From: [email protected]<mailto:toad%40yahoogroups.com> [mailto:toad@ya= hoogroups.com<mailto:toad%40yahoogroups.com>] On Behalf Of gene.l.bradley > Sent: Monday, July 22, 2013 1:43 PM > To: [email protected]<mailto:toad%40yahoogroups.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:SB= harti@...<mailto:SBharti@...%3cmailto: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 asc= ript. > 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 =3D:RunDate ) store_count > ,( SELECT COUNT(*) > FROM wh > WHERE wh_open_date =3D:RunDate ) ) wh_count > FROM dual; > > > Thanks, > Shiva > > > ---------------------------------------------------------- > > NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL informatio= n and is intended only for the use of the specific individual or individual= s 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 c= opying of this e-mail or the information contained herein or attached heret= o is strictly prohibited. If you receive this e-mail in error, notify the p= erson 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@<mailto:gbradley@...%3cmailto:gbradley= @>...> > > > > > ---------------------------------------------------------- > > NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL informatio= n and is intended only for the use of > the specific individual or individuals to which it is addressed. If you a= re 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. I= f 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 y= ou. --_000_B75962E88A72F3438A4EC07F37C0375B0D897DNCEXDAG02familydo_ Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: 7bit <head> <style type="text/css"> <!-- /* start of attachment style */ .ygrp-photo-title{ clear: both; font-size: smaller; height: 15px; overflow: hidden; text-align: center; width: 75px; } div.ygrp-photo{ background-position: center; background-repeat: no-repeat; background-color: white; border: 1px solid black; height: 62px; width: 62px; } div.photo-title a, div.photo-title a:active, div.photo-title a:hover, div.photo-title a:visited { text-decoration: none; } div.attach-table div.attach-row { clear: both; } div.attach-table div.attach-row div { float: left; /* margin: 2px;*/ } p { clear: both; padding: 15px 0 3px 0; overflow: hidden; } div.ygrp-file { width: 30px; valign: middle; } div.attach-table div.attach-row div div a { text-decoration: none; } div.attach-table div.attach-row div div span { font-weight: normal; } div.ygrp-file-title { font-weight: bold; } /* end of attachment style */ --> </style> </head> <html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"> <head> <meta http-equiv="Content-Type" content="text/html; charset=us-ascii"> <meta name="Generator" content="Microsoft Word 14 (filtered medium)"> <!--[if !mso]><style>v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} </style><![endif]--><style><!-- /* Font Definitions */ @font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0;} @font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} @font-face {font-family:Tahoma; panose-1:2 11 6 4 3 5 4 4 2 4;} @font-face {font-family:Verdana; panose-1:2 11 6 4 3 5 4 4 2 4;} @font-face {font-family:Consolas; panose-1:2 11 6 9 2 2 4 3 2 4;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:12.0pt; font-family:"Times New Roman","serif";} a:link, span.MsoHyperlink {mso-style-priority:99; color:blue; text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:purple; text-decoration:underline;} p {mso-style-priority:99; mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:12.0pt; font-family:"Times New Roman","serif";} code {mso-style-priority:99; font-family:"Courier New";} pre {mso-style-priority:99; mso-style-link:"HTML Preformatted Char"; margin:0in; margin-bottom:.0001pt; font-size:10.0pt; font-family:"Courier New";} tt {mso-style-priority:99; font-family:"Courier New";} span.cat {mso-style-name:cat;} span.ct {mso-style-name:ct;} span.HTMLPreformattedChar {mso-style-name:"HTML Preformatted Char"; mso-style-priority:99; mso-style-link:"HTML Preformatted"; font-family:"Consolas","serif";} p.attach, li.attach, div.attach {mso-style-name:attach; mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:9.0pt; font-family:"Arial","sans-serif";} p.bold, li.bold, div.bold {mso-style-name:bold; mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:10.0pt; font-family:"Arial","sans-serif"; font-weight:bold;} p.green, li.green, div.green {mso-style-name:green; mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:12.0pt; font-family:"Times New Roman","serif"; color:#628C2A;} p.replbq, li.replbq, div.replbq {mso-style-name:replbq; margin:3.0pt; font-size:12.0pt; font-family:"Times New Roman","serif";} p.ad, li.ad, div.ad {mso-style-name:ad; mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:12.0pt; font-family:"Times New Roman","serif";} p.underline, li.underline, div.underline {mso-style-name:underline; mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:12.0pt; font-family:"Times New Roman","serif";} span.yshortcuts {mso-style-name:yshortcuts;} p.ad1, li.ad1, div.ad1 {mso-style-name:ad1; mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:12.0pt; font-family:"Times New Roman","serif";} p.ad2, li.ad2, div.ad2 {mso-style-name:ad2; mso-margin-top-alt:auto; margin-right:0in; margin-bottom:7.5pt; margin-left:0in; font-size:12.0pt; font-family:"Times New Roman","serif";} p.underline1, li.underline1, div.underline1 {mso-style-name:underline1; mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:12.0pt; font-family:"Times New Roman","serif"; text-decoration:underline;} span.yshortcuts1 {mso-style-name:yshortcuts1; font-family:"Verdana","sans-serif"; font-weight:bold;} span.yshortcuts2 {mso-style-name:yshortcuts2; font-family:"Verdana","sans-serif"; font-weight:normal;} span.EmailStyle36 {mso-style-type:personal-reply; font-family:"Calibri","sans-serif"; color:#1F497D;} .MsoChpDefault {mso-style-type:export-only; font-size:10.0pt;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in;} div.WordSection1 {page:WordSection1;} /* List Definitions */ @list l0 {mso-list-id:1778254468; mso-list-template-ids:1714864076;} @list l0:level1 {mso-level-number-format:bullet; mso-level-text:\F0B7; mso-level-tab-stop:.5in; mso-level-number-position:left; text-indent:-.25in; mso-ansi-font-size:10.0pt; font-family:Symbol;} @list l0:level2 {mso-level-number-format:bullet; mso-level-text:o; mso-level-tab-stop:1.0in; mso-level-number-position:left; text-indent:-.25in; mso-ansi-font-size:10.0pt; font-family:"Courier New"; mso-bidi-font-family:"Times New Roman";} @list l0:level3 {mso-level-number-format:bullet; mso-level-text:\F0A7; mso-level-tab-stop:1.5in; mso-level-number-position:left; text-indent:-.25in; mso-ansi-font-size:10.0pt; font-family:Wingdings;} @list l0:level4 {mso-level-number-format:bullet; mso-level-text:\F0A7; mso-level-tab-stop:2.0in; mso-level-number-position:left; text-indent:-.25in; mso-ansi-font-size:10.0pt; font-family:Wingdings;} @list l0:level5 {mso-level-number-format:bullet; mso-level-text:\F0A7; mso-level-tab-stop:2.5in; mso-level-number-position:left; text-indent:-.25in; mso-ansi-font-size:10.0pt; font-family:Wingdings;} @list l0:level6 {mso-level-number-format:bullet; mso-level-text:\F0A7; mso-level-tab-stop:3.0in; mso-level-number-position:left; text-indent:-.25in; mso-ansi-font-size:10.0pt; font-family:Wingdings;} @list l0:level7 {mso-level-number-format:bullet; mso-level-text:\F0A7; mso-level-tab-stop:3.5in; mso-level-number-position:left; text-indent:-.25in; mso-ansi-font-size:10.0pt; font-family:Wingdings;} @list l0:level8 {mso-level-number-format:bullet; mso-level-text:\F0A7; mso-level-tab-stop:4.0in; mso-level-number-position:left; text-indent:-.25in; mso-ansi-font-size:10.0pt; font-family:Wingdings;} @list l0:level9 {mso-level-number-format:bullet; mso-level-text:\F0A7; mso-level-tab-stop:4.5in; mso-level-number-position:left; text-indent:-.25in; mso-ansi-font-size:10.0pt; font-family:Wingdings;} ol {margin-bottom:0in;} ul {margin-bottom:0in;} --></style><!--[if gte mso 9]><xml> <o:shapedefaults v:ext="edit" spidmax="1026" /> </xml><![endif]--><!--[if gte mso 9]><xml> <o:shapelayout v:ext="edit"> <o:idmap v:ext="edit" data="1" /> </o:shapelayout></xml><![endif]--> </head> <body bgcolor="white" lang="EN-US" link="blue" vlink="purple"> <div class="WordSection1"> <p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">This is what I was looking for.<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <div> <p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Thanks,<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Shiva<o:p></o:p></span></p> </div> <p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <div> <div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in"> <p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> [email protected] [mailto:[email protected]] <b>On Behalf Of </b>Gregory Liss<br> <b>Sent:</b> Monday, July 22, 2013 3:00 PM<br> <b>To:</b> [email protected]<br> <b>Subject:</b> [toad] Re: Spool<o:p></o:p></span></p> </div> </div> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"> <o:p></o:p></p> <div id="ygrp-mlmsg"> <div id="ygrp-msg"> <div id="ygrp-text"> <p style="margin-bottom:12.0pt">Maybe something like this:<br> <br> SET TIMING OFF<br> SET SERVEROUTPUT ON<br> SET TERMOUT OFF<br> SET VERIFY OFF<br> SET HEADING OFF<br> SET ECHO OFF<br> SET FEEDBACK OFF<br> <br> -- build temp file to run all from <br> SPOOL c:\temp\run_all.sql<br> <br> BEGIN<br> FOR counter IN 0 .. 29<br> LOOP<br> -- create spool file to output each item to <br> DBMS_OUTPUT.put_line ( 'spool c:\temp\file_' || TO_CHAR (SYSDATE - counter, 'yymmdd') || '.txt');<br> -- substitute your query here<br> DBMS_OUTPUT.put_line ('select TO_DATE(''' || TO_CHAR (SYSDATE - counter) || ''') from dual;');<br> -- close the spool<br> DBMS_OUTPUT.put_line ('Spool off');<br> END LOOP;<br> END;<br> -- close temp file spool<br> SPOOL OFF;<br> -- run temp file<br> @c:\temp\run_all.sql<br> /<br> <br> --- In <a href="mailto:toad%40yahoogroups.com">[email protected]</a>, SHIVANAND BHARTI <<a href="mailto:SBharti@...">SBharti@...</a>> wrote:<br> ><br> > To keep it simple let's say last 30 days ... sysdate, sysdate - 1 , sysdate -2,...,sysdate-29<br> > <br> > Thanks,<br> > Shiva<br> > <br> > From: <a href="mailto:toad%40yahoogroups.com">[email protected]</a> [mailto:<a href="mailto:toad%40yahoogroups.com">[email protected]</a>] On Behalf Of gene.l.bradley<br> > Sent: Monday, July 22, 2013 1:43 PM<br> > To: <a href="mailto:toad%40yahoogroups.com">[email protected]</a><br> > Subject: Re: [toad] Spool<br> > <br> > <br> > How are you determining what the dates are.<br> > <br> > You could put the dates in a temporary table<br> > and then say where dateVariable in (select myDate from temp_table);<br> > <br> > On Mon, Jul 22, 2013 at 12:31 PM, SHIVANAND BHARTI <<a href="mailto:SBharti@...%3cmailto:SBharti@...">SBharti@...<mailto:SBharti@...</a>>> wrote:<br> > <br> > Hi folks ... I have a query with bind variable which accepts date.<br> > How can I run thais query for 30 different dates and get it's output in a file.<br> > <br> > I can write the same query 30 times with different date and run it as ascript.<br> > Or write a PL/SQL block.<br> > <br> > But it is an one time thing and I am looking for something quick .<br> > 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 ?<br> > <br> > For example how can I run this query for 30 different dates and spool it's output.<br> > <br> > SELECT :RunDate ,<br> > ( SELECT COUNT(*)<br> > FROM store<br> > WHERE pstore_open_date =:RunDate ) store_count<br> > ,( SELECT COUNT(*)<br> > FROM wh<br> > WHERE wh_open_date =:RunDate ) ) wh_count<br> > FROM dual;<br> > <br> > <br> > Thanks,<br> > Shiva<br> > <br> > <br> > ----------------------------------------------------------<br> > <br> > 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.<br> > <br> > <br> > <br> > --<br> > Gene L. Bradley Jr.<br> > Software Developer I<br> > Information Systems & Integration<br> > Jackson State University<br> > 1400 J R Lynch Street<br> > P.O. Box 17750<br> > Jackson, MS 39217<br> > <br> > ph 601.979.1042<br> > fax 601.371.9146<br> > email <a href="mailto:gbradley@...%3cmailto:gbradley@">gbradley@...<mailto:gbradley@</a>...><br> > <br> > <br> > <br> > <br> > ----------------------------------------------------------<br> > <br> > NOTE: This e-mail message contains PRIVILEGED and CONFIDENTIAL information and is intended only for the use of<br> > the specific individual or individuals to which it is addressed. If you are not an intended recipient of this<br> > e-mail, you are hereby notified that any unauthorized use, dissemination or copying of this e-mail or the<br> > information contained herein or attached hereto is strictly prohibited. If you receive this e-mail in error,<br> > notify the person named above by reply e-mail and please delete it. Thank you.<br> ><o:p></o:p></p> </div> <div> <p class="MsoNormal"><span style="color:white"><o:p></o:p></span></p> </div> </div> <br clear=all> ------------------------------------------------------------------------------------- <p> 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. <!-- |**|begin egp html banner|**| --> <br> <br> <!-- |**|end egp html banner|**| --> <div width="1" style="color: white; clear: both;"/>__._,_.___</div> <!-- Start Recommendations --> <!-- End Recommendations --> <!-- |**|begin egp html banner|**| --> <img src="http://geo.yahoo.com/serv?s=97476590/grpId=1103546/grpspId=1705115368/msgId=58546/stime=1374526063" width="1" height="1"> <br> <!-- |**|end egp html banner|**| --> <!-- |**|begin egp html banner|**| --> <br> <div style="font-family: verdana; font-size: 77%; border-top: 1px solid #666; padding: 5px 0;" > Your email settings: Individual Email|Traditional <br> <a href="http://groups.yahoo.com/group/toad/join;_ylc=X3oDMTJmOHBrZWM5BF9TAzk3NDc2NTkwBGdycElkAzExMDM1NDYEZ3Jwc3BJZAMxNzA1MTE1MzY4BHNlYwNmdHIEc2xrA3N0bmdzBHN0aW1lAzEzNzQ1MjYwNjM-">Change settings via the Web</a> (Yahoo! ID required) <br> Change settings via email: <a href="mailto:[email protected]?subject=Email Delivery: Digest">Switch delivery to Daily Digest</a> | <a href = "mailto:[email protected]?subject=Change Delivery Format: Fully Featured">Switch to Fully Featured</a> <br> <a href="http://groups.yahoo.com/group/toad;_ylc=X3oDMTJkODllN3FoBF9TAzk3NDc2NTkwBGdycElkAzExMDM1NDYEZ3Jwc3BJZAMxNzA1MTE1MzY4BHNlYwNmdHIEc2xrA2hwZgRzdGltZQMxMzc0NTI2MDYz"> Visit Your Group </a> | <a href="http://docs.yahoo.com/info/terms/"> Yahoo! Groups Terms of Use </a> | <a href="mailto:[email protected]?subject=Unsubscribe"> Unsubscribe </a> <br> </div> <br> <!-- |**|end egp html banner|**| --> <div style="color: white; clear: both;"/>__,_._,___</div> </body> </html> --_000_B75962E88A72F3438A4EC07F37C0375B0D897DNCEXDAG02familydo_--