This content has been marked as final. Show 4 replies
user10903866 wrote:Do you want the user input in the SPOOL command, or do you want it in the query?
For the Following SPOOL I want the GEN_DATE to be entered by the User at the time of execution of the SQL Script. Is it possible in SPOOL Command.
set colsep , -- separate columns with a commaWhat is the data type oif gen_date?
set pagesize 1000 -- get rid of disturbing ---- between pages
set heading on -- Print column heading
set trimspool on -- remove trailing blanks. eliminating the spaces up to eol
set linesize 700 -- line size should be the sum of the column widths
WHERE GENDATE_ BETWEEN '2012-11-01 00:00:00:00000' AND '2012-11-02 00:00:00:00000'* ORDER BY GEN_DATE;
If it's a string, that's a big mistake. Information about dates belongs in DATE columns.
If it's a DATE, then don't try to compare it to strings, such as '2012-11-01 00:00:00:00000' .
spool off;One way to do that is with substitution variables:
The reason is to give the ability to user so that he can enter any range without modifying the code of the script.
Can Any one help me please.
There are security considerations. Substitution variables give devious users the power to issue any SQL command, such as DROP TABLE. Users that have SQL*Plus access already have that power, anyway.
... SET VERIFY OFF ACCEPT start_gen_date PROMPT "Starting date (e.g., 2013-02-18 23:00:00.00000): " ACCEPT end_gen_date PROMPT "Ending date (e.g., 2013-02-18 23:59:59.99999): " SPOOL spool_results.csv SELECT * FROM gui_site_journal WHERE gen_date BETWEEN '&start_gen_date' AND '&end_gen_date' ORDER BY gen_date; SPOOL OFF