3

Any idea why this is prompting me for the value? I'm calling the script via batch file:

DECLARE
   precount   NUMBER;
   nowcount   NUMBER;
BEGIN
    SELECT COUNT(*) INTO precount FROM PLAN_OFFER_BK_PRE_SCRIPT WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
    SELECT COUNT(*) INTO nowcount FROM PLAN_OFFER WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
END;
/

-- NUMBER OF MEC/MVP RECORDS
SPOOL logs\MVP_MEC_UPDATED.log APPEND
PROMPT &&CLIENTID   &&precount  &&nowcount
SPOOL OFF;

Thanks!

2

The ampersand character (&) prompts for values before execution time by default. That is not how you display values of variables. Try the below instead.

set serveroutput on

SPOOL logs\MVP_MEC_UPDATED.log APPEND
DECLARE
   precount   NUMBER;
   nowcount   NUMBER;
BEGIN
    SELECT COUNT(*) INTO precount FROM PLAN_OFFER_BK_PRE_SCRIPT WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
    SELECT COUNT(*) INTO nowcount FROM PLAN_OFFER WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';

    dbms_output.put_line('precount: ' || precount || ',  nowcount:' || nowcount );
END;
/

SPOOL OFF

Passing a variable to a script in SQL*Plus. The script, let's say, 1.sql:

set serveroutput on feedback off verify off
define CLIENTID=&1

SPOOL logs\MVP_MEC_UPDATED.log APPEND
DECLARE
   precount   NUMBER;
   nowcount   NUMBER;
BEGIN
    SELECT COUNT(*) INTO precount FROM PLAN_OFFER_BK_PRE_SCRIPT WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
    SELECT COUNT(*) INTO nowcount FROM PLAN_OFFER WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';

    dbms_output.put_line('CLIENTID: ' || '&CLIENTID' || ', precount: ' || precount || ',  nowcount:' || nowcount );
END;
/

SPOOL OFF
exit

Invoking it:

sqlplus -s user/password @1.sql 101

Where 101 is the CLIENTID. Or:

sqlplus -s user/password @1.sql %CLIENTID%

Where %CLIENTID% is a variable in your batch script.

0
0

Are you prompted for &&CLIENTID &&precount and &&nowcount?

I believe that's where your problem comes from.

If you want to display the results for precount and nowcount, can you try:

set serveroutput on

DECLARE
   precount   NUMBER;
   nowcount   NUMBER;
BEGIN
    SELECT COUNT(*) INTO precount FROM PLAN_OFFER_BK_PRE_SCRIPT WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
    SELECT COUNT(*) INTO nowcount FROM PLAN_OFFER WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
dbms_output.put_line(precount);
dbms_output.put_line(nowcount);

END;
/
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.