How to Set variabel call in toad for orcale? Like i have Query for input 3 report_dat, and 1 want create variable, so that i only input once

I have create like this

def report_date = 2;
def report_year = 2021;

and in where condition, i have set like this.

where H_CLOSING_PERIOD.BULAN = &report_date
and H_CLOSING_PERIOD.TAHUN = &report_year

But, when i run that query, it's still request input from user, like thisenter image description here

CodePudding user response：

Try replacing with the following:

variable report_date H_CLOSING_PERIOD.BULAN%TYPE; --not sure of your data types, you can adjust these
variable report_year H_CLOSING_PERIOD.TAHUN%TYPE;

SELECT 2
     , 2021
  INTO :report_date
     , :report_year
  FROM dual;

and then when you use the binds:

WHERE H_CLOSING_PERIOD.BULAN = :report_date
  AND H_CLOSING_PERIOD.TAHUN = :report_year

CodePudding user response：

In TOAD, the simplest way (from my point of view), is to use bind variables. How? Simply precede their names with a colon sign:

where H_CLOSING_PERIOD.BULAN = :report_date
  and H_CLOSING_PERIOD.TAHUN = :report_year

TOAD will prompt you to enter their values. For any subsequent execution, just hit ENTER (i.e. you don't have to enter their values, unless you want to).