Navigation
Recent Posts
- Macro to insert footer
- $this PHP Variable
- php5 extensions directory and mysql undefined function
- Windows Vista biggest disappointment of 2007
- Excel giving incorrect results
- SnagIt 8.2.0 workaround
- Find Matching Text in Excel
- Excel macro to copy to Custom Template
- UK Post Code GeoCoding
- Accept User Input in SQL*Plus
- Exit SQLPlus from Batch File
- Run Multiple SQLPlus Queries via DOS batch file
- CSS Browser Differences IE and Firefox
- Create csv import file from Excel
- Use Query Result as Variable in Batch File
Archive
- November-2009
- August-2008
- January-2008
- December-2007
- November-2007
- October-2007
- September-2007
- August-2007
- June-2007
- May-2007
- April-2007
- March-2007
- February-2007
- January-2007
Oracle SQL*Plus Last Full Month
Although Crystal Reports has a built in LastFullMonth function SQL*Plus doesn't.
If you want to use SQL*Plus for your report and want the date parameter to be dynamic you can do so by manipulating sysdate and using some of the built in date functions.
Here is how
where date_col between add_months((last_day(sysdate))+1,-2) AND add_months((last_day(sysdate))+1,-1)
Tags: Oracle |SQL*Plus |Dates |Crystal Reports
SQL*Plus Spool File
If you want to produce a csv file from a SQL select query in SQL*Plus use the following syntax.
set heading off
set feedback off
set pagesize 0
spool c:\filename.csv
select col1||','||col2 from db_table;
spool off
This will produce a csv formatted file. Straight to excel, change csv to xls but bear in mind two potential issues (a) row limit in Excel, and (b) leading zeros in number fields will be lost in excel.
The leading zeros can be resolved if you instead spool the file as a txt as you will be forced to use excels conversion wizard to open the file.