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
Popular Tags
Click tag to retrieve blogsAccept User Input in SQL*Plus
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.
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'
Exit SQLPlus from Batch 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
@C:\Queries\Query_2.sql
@C:\Queries\Query_3.sql
@C:\Queries\Query_4.sql
EXIT
Tags:SQLPlus |Oracle |DOS |bat |batch file
Run Multiple SQLPlus Queries via DOS batch file
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_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
sqlplus [login details] @C:\queries\runall.sql
EXIT
Tags:SQLPlus |bat |DOS |Oracle
Use Query Result as Variable in Batch File
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
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.