You can allow users to enter parameters in SQLPlus queries by using accept and prompt. An example of the syntax is
startdate is the parameter value and the where condition would then include the parameter as follows
The example below I saved for use in a batch file. A user could then click the batch file which would open SQLPlus and prompt the user for the query parameters including a "Save File As" prompt, as the output was generating a csv file. The batch file icon can be changed in Windows to something other than the default, something more meaningful for the user.
The appearance might be very retro, but its a simple tool of the "cheap and cheerful" variety and the user is, after all, more interested in the output file.
If you have a batch file set to run multiple SQLPlus queries and to then perform some other action such as merge the results together into a single file, or copy the files elsewhere you need to physically exit SQLPlus or the batch file will not continue to run and execute the commands.
To exit SQLPlus automatically skip a line after your last query and type exit as in
To run multiple SQLPlus queries as part of a DOS batch file place all the queries in a separate SQL file. For Example
Save the file as runall.sql. Then use this file in your batch file
You can design the most beautiful CSS compliant web, all validated to W3C standards, only to find its almost completely unreadable or unusable in another browser. Font sizes are microscopic, menus don't appear as they may now be hidden under a layer, and the layout is all wrong. Worse, it just doesn't look as if its been professionally designed, and if you have already published it, all you can do is cringe before rolling up your sleeves and correcting it.
Browsers have different defaults. Internet Explorer seems to have more than others, so if you design and test using only IE your pages may not display properly in other browsers. As a rule you should test your site with other browsers before publishing it.
There is hack you can use for Internet Explorer to ensure it reads the right CSS and your site is cross browser compliant. This hack is achieved by simply adding !important to the css you want Internet Explorer to ignore. The following CSS creates similar font size across browsers. IE ignores the first line, whereas Firefox applies the first line.
As another rule you should try to not mix your stylesheet with inline CSS, i.e. if you define an element in your stylesheet then add some inline CSS to it, the stylesheet CSS may be completely ignored and you may end up with a result you don't want..
In the macro below I create a csv file from the worksheet named accrual. As the sheet is the data returned from an MS Query I first refresh the query before creating the csv file. The Query refreshes from cell A2 as row one contains the header row, the field identifiers required to import into a Finance System.
To use it copy the VBA into the worksheet you wish to run it on and change the details as necessary, i.e. sheet name, whether you wish to refresh the query or not, save as file name etc..
I actually use this twice in the workbook, once to create a csv file of invoices to import into the Sales Ledger, and the second time to create a reversing accrual to import into the Nominal Ledger, to accrue the revenue, as the Invoices are dated first day of the month so miss quarter end tax point for VAT. The accrual posts the revenue into prior month and reverses it out again on the first day of the next month.
You can use the result of a SQLPlus query as a variable in a windows batch file by spooling the output to a .bat file
This creates or replaces a batch file and outputs
I am then able to call that batch file from within another batch file and use the SQL query result as it is defined as a variable.
© Eriginal Ltd 2011, all rights reserved