Hi guys,
I've prepared a dynamic query to compute the sum of two values from a table partition. The partition name is computed at runtime:
BEGIN
...
select to_char(add_months(d_datetime,0),'yyyymm') into sys_current_date from dual;
select to_char(add_months(d_datetime,-1),'yyyymm') into sys_previous_date from dual;
partition_current_month := 'P'|| sys_current_date;
partition_previous_month := 'P'|| sys_previous_date;
EXECUTE IMMEDIATE 'SELECT sum (found_records) FROM (
SELECT count(*) as found_records FROM od_pv_trading_day_orders partition (' || partition_current_month || ') WHERE DATETIME = :1 AND orderid = :2 AND broker = :3
UNION
SELECT count(*) as found_records FROM od_pv_trading_day_orders partition (' || partition_previous_month || ') WHERE DATETIME = :1 AND orderid = :2 AND broker = :3
)' INTO n_record_
USING d_datetime, n_orderid, cur.broker;
...
END
It's my first time using this command and I'm not sure about its syntax, the error that pops up is ORA-01008: not all variables are bound. All the variables I used are declared in the head section of the procedure.
Can you tell me what's wrong with the code I wrote? I think there's something unaccepted with the use of "cur.broker" or something.
Thank you!