Oracle SQLPLUS – How to Use SELECT INTO Without Prompting for Variable Value

oracleplsqlselectsqlplus

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!

Best Answer

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.

Related Question