Subscribe to this feed

Navigation

Recent Posts

Archive

Accept User Input in SQL*Plus

Saturday 05 May, 2007 - 17:11pm | 0 comments |

You can allow users to enter parameters in SQLPlus queries by using accept and prompt. An example of the syntax is

accept startdate prompt "Enter Start Date: "

startdate is the parameter value and the where condition would then include the parameter as follows

WHERE date >= '&startdate'

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.

The Retro SQL*Plus Screen

accept filesave prompt "Save File As: "
accept code prompt "Enter Carrier Code: "
accept startdate prompt "Enter Start Date: "
accept enddate prompt "Enter End Date: "
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET WRAP OFF
SET LINESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
SPOOL C:\reports\'&filesave'.csv;
SELECT /* The purpose of this query is to ...... Always good policy to annotate queries */
carrier_id, carrier_name, calls, minutes, turnover FROM
carriers_data
WHERE
trunc(adj_start_time) >= '&startdate'
AND trunc(adj_start_time) < '&enddate'
AND carrier_id = '&code'

Tags: SQL*Plus |Oracle |CSV

Add a comment
 | Link | Back to top | del.icio.us digg it furl reddit

Exit SQLPlus from Batch File

Saturday 05 May, 2007 - 12:57pm | 0 comments |

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

@C:\Queries\Query_1.sql
@C:\Queries\Query_2.sql
@C:\Queries\Query_3.sql
@C:\Queries\Query_4.sql

EXIT

Tags: SQLPlus |Oracle |DOS |bat |batch file

Add a comment
 | Link | Back to top | del.icio.us digg it furl reddit

Run Multiple SQLPlus Queries via DOS batch file

Saturday 05 May, 2007 - 12:51pm | 5 comments |

To run multiple SQLPlus queries as part of a DOS batch file place all the queries in a separate SQL file. For Example

@C:\Queries\Query_1.sql
@C:\Queries\Query_2.sql
@C:\Queries\Query_3.sql
@C:\Queries\Query_4.sql

Save the file as runall.sql. Then use this file in your batch file

@Echo Off
sqlplus [login details] @C:\queries\runall.sql
EXIT

Tags: SQLPlus |bat |DOS |Oracle

Add a comment
 | Link | Back to top | del.icio.us digg it furl reddit

CSS Browser Differences IE and Firefox

Friday 04 May, 2007 - 13:38pm | 0 comments |

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.


body
{
font-size: medium !important;
font-size: x-small;
}

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..

Tags: CSS |W3C |IE |Firefox |Browsers

Add a comment
 | Link | Back to top | del.icio.us digg it furl reddit

Create csv import file from Excel

Tuesday 01 May, 2007 - 12:06pm | 0 comments |

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.

Sub Accrual()
Dim wb As Workbook
Dim source As Range
Dim dest As Workbook
Sheets("accrual").Activate

'If sheet is the result of a query refresh the data
'started on A2 as row one contains the field identifiers
'Comment out if you do not need to refresh a query
ActiveSheet.Range("A2").Select
Selection.QueryTable.refresh BackgroundQuery:=False

Set source = Nothing
On Error Resume Next
Set source = Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "No Data", vbOKOnly
Exit Sub
End If

Application.ScreenUpdating = False
Set dest = Workbooks.Add(xlWBATWorksheet)
source.Copy
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

'Change Filename to what you require
dest.SaveAs Filename:="c:accrual.csv", _
FileFormat:=xlCSV, CreateBackup:=False
dest.Close SaveChanges:=False

'Return to main application page
Application.ScreenUpdating = True
Sheets("Form").Activate
End Sub

Tags: csv |Import file |Sage MMS |VBA

Add a comment
 | Link | Back to top | del.icio.us digg it furl reddit

Use Query Result as Variable in Batch File

Tuesday 01 May, 2007 - 10:47am | 0 comments |

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

SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET WRAP OFF
SET LINESIZE 1000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
SPOOL c:cdr_count.bat;
select
'@echo off' from dual
UNION ALL
select 
'SET CDR='||''||count(ddi) from cdrs where trunc(sysdate) = trunc(adj_start_time);
SPOOL OFF;

This creates or replaces a batch file and outputs

@echo off
SET CDR=643950

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.

call c:cdr_count.bat
IF %CDR% EQU 0 GOTO END

Tags: SQLPLus |Oracle |DOS |Batch files

Add a comment
 | Link | Back to top | del.icio.us digg it furl reddit
Previous Next

© Eriginal Ltd 2011, all rights reserved