oracle 绑定变量导致错误,Oracle绑定变量错误声明

我想在Oracle中使用绑定变量运行此查询。需要知道为什么会提示以下错误。我相信我的语法有错误，不能明白它是什么？Oracle绑定变量错误声明

variable start number;

variable end number;

exec :start := 1;

exec :end := 2;

select * from (

select

ob.offer_bank_id

, ob.promo_period_id

, ob.offer_bank_nm

, obst.offer_bank_status_type_dsc

, ob.effective_start_dt

, ob.effective_end_dt

, obt.offer_bank_type_dsc

, obt.offer_bank_type_cd

, pp.promo_period_nm

, SUM(CASE WHEN a.offer_id IS NOT NULL THEN 1 ELSE 0 END) as total_count

, SUM(CASE WHEN a.offer_status_type_cd = 'ED' THEN 1 ELSE 0 END) as editing_count

, SUM(CASE WHEN a.offer_status_type_cd = 'FD' THEN 1 ELSE 0 END) as failed_deactive_count

, SUM(CASE WHEN a.offer_status_type_cd in ('FP', 'FI') THEN 1 ELSE 0 END) as failed_production_co

, SUM(CASE WHEN a.offer_status_type_cd = 'FV' THEN 1 ELSE 0 END) as failed_preview_count

, SUM(CASE WHEN a.offer_status_type_cd = 'LD' THEN 1 ELSE 0 END) as loaded_count

, SUM(CASE WHEN a.offer_status_type_cd in ('PE', 'PS') THEN 1 ELSE 0 END) as pending_count

, SUM(CASE WHEN a.offer_status_type_cd = 'PK' THEN 1 ELSE 0 END) as parked_count

, SUM(CASE WHEN a.offer_status_type_cd = 'SD' THEN 1 ELSE 0 END) as successfully_deactivated_count

, SUM(CASE WHEN a.offer_status_type_cd in ('SP','PI') THEN 1 ELSE 0 END) as successfully_loaded_to_prod

, SUM(CASE WHEN a.offer_status_type_cd = 'SV' THEN 1 ELSE 0 END) as successfully_loaded_to_preview

, SUM(CASE WHEN a.offer_status_type_cd in ('LD','PE','PS') THEN 1 ELSE 0 END) as total_pending_count

, SUM(CASE WHEN a.offer_status_type_cd in ('FD','FP','FV','FR','FI') THEN 1 ELSE 0 END) as failed_count

, COUNT(1) OVER(PARTITION BY 1) as total_rows

, ROW_NUMBER() OVER (ORDER BY ob.effective_end_dt desc) as row_nbr

, MAX(a.offer_effective_end_dt) as max_offer_effective_end_dt

, MIN(a.offer_effective_start_dt) as min_offer_effective_start_dt

, SUM(CASE WHEN a.offer_status_type_cd in ('AR','SR','SD') THEN 1 ELSE 0 END) as ended_count

, SUM(CASE WHEN a.offer_status_type_cd in ('CD') THEN 1 ELSE 0 END) as copient_delay_count

, SUM(CASE WHEN a.offer_status_type_cd in ('SR') THEN 1 ELSE 0 END) as rejected_count

, SUM(CASE WHEN a.offer_status_type_cd in ('LV', 'GV', 'CD', 'GA', 'GC', 'GD', 'GI', 'GP', 'GR', 'LA', 'LI', 'LP', 'LR', 'LV', 'LE')

THEN 1 ELSE 0 END) as processing_count

, a.store_banner_cd

, a.banner_nm

from

offer_bank ob

INNER JOIN offer_bank_status obs

ON ob.offer_bank_id = obs.offer_bank_id

INNER JOIN offer_bank_status_type obst

ON obs.offer_bank_status_type_cd = obst.offer_bank_status_type_cd

INNER JOIN promo_period pp

ON ob.promo_period_id = pp.promo_period_id

INNER JOIN offer_bank_type obt

ON ob.offer_bank_type_cd = obt.offer_bank_type_cd

LEFT OUTER JOIN

(select

o.offer_id

, o.offer_bank_id

, sb.store_banner_cd

, sb.banner_nm

, ost.offer_status_type_cd

, o.offer_effective_end_dt

, o.offer_effective_start_dt

from

offer o

INNER JOIN offer_store_banner osb

ON o.offer_id = osb.offer_id

INNER JOIN store_banner sb

ON osb.store_banner_cd = sb.store_banner_cd

INNER JOIN offer_status os

ON o.offer_id = os.offer_id

INNER JOIN offer_status_type ost

ON os.offer_status_type_cd = ost.offer_status_type_cd

AND os.effective_end_dt is null

) a

ON ob.offer_bank_id = a.offer_bank_id

where

obs.effective_end_dt is null

group by

ob.offer_bank_id

, ob.promo_period_id

, ob.offer_bank_nm

, obst.offer_bank_status_type_dsc

, ob.effective_start_dt

, ob.effective_end_dt

, obt.offer_bank_type_dsc

, obt.offer_bank_type_cd

, pp.promo_period_nm,

a.store_banner_cd,

a.banner_nm

order by

ob.effective_end_dt desc

) a

where

a.row_nbr > start;

我的错误这样说：

Error report:

SQL Error: ORA-01745: invalid host/bind variable name

01745. 00000 - "invalid host/bind variable name"

在此感谢一些帮助。我是新来绑定变量 在此先感谢

+0

您似乎没有在您发布的查询中使用绑定变量;你有'开始'，但不是'：开始'。正如jonearles所说，发布一个最小的，可重现的问题演示将帮助其他人帮助你。 –