troubleshooting Question

SQL- PLSQL parameter - formatting

Avatar of ktylerconk
ktylerconkFlag for United States of America asked on
Oracle Database
4 Comments1 Solution956 ViewsLast Modified:
I have a PLSQL script  on R:\CAATS_CCMS\IMPAC Workarea\PLSQL Scripts\updateIMPACfactTableAutoS  like this:  

set feedback off
set verify off
set serveroutput on

DECLARE

 start_date varchar2(8) := &1;
 end_date varchar2(8) := &2;

SELECT b.merch_crdhldr,b.merch_name,
          TRUNC (b.purchase_date) purchase_date,b.merch_tran_amount,b.holder_purchase_limit_amt
    FROM ccms.impac_fdm_transaction b
    where
        to_char (b.process_start_date, 'MMDDYYYY' ) = start_date
    AND to_char (b.process_end_date, 'MMDDYYYY' )   = end_date
...

When I try to execute it with this:

set feedback off
set verify off
set serveroutput on

undef START_DATE
undef END_DATE
@"R:\CAATS_CCMS\IMPAC Workarea\PLSQL Scripts\updateIMPACfactTableAutoS" 08191999 09171999

undef START_DATE
undef END_DATE
@"R:\CAATS_CCMS\IMPAC Workarea\PLSQL Scripts\updateIMPACfactTableAutoS" 09201999 10181999

I get a message on DOS saying

ENTER VALUE for 1:

Why isn't it accepting my date parameters (i.e.08191999 and 09171999 for the first run and 09201999 and 10181999 for the second)?  I have to run this over 90 times, so have coded the statements like this to avoid having to enter dates so many times while the script is running.

Thanks!
ASKER CERTIFIED SOLUTION
Steve Wales
Senior Database Administrator

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros