bschave2
asked on
how do I fix the error: not all variables bound
I have a dynamic sql query I am writing and I get a not all variables bound error. I think it has to do with me using some variables twice in the statement, but not sure.
PROCEDURE createRessuply(
v_Contract_Id IN NUMBER,
v_Call_Option_Type_Id IN NUMBER,
v_Trades_Deal_No IN NUMBER,
v_Begin_Date VARCHAR2,
v_DST_Date VARCHAR2,
v_ST_Date VARCHAR2,
v_Entry_User VARCHAR2)
IS
avail_Date VARCHAR2(20);
resupply_Cursor t_Cursor;
hours integer;
stmt1 varchar(4000);
BEGIN
for resupply_Cursor in (SELECT DISTINCT TO_CHAR(availability_date, 'mm/dd/yyyy') availability_date
FROM v_call_option_avail
WHERE availability_date >= to_date(v_Begin_Date, 'mm/dd/yyyy')
AND contract_id = v_contract_id
AND call_option_type_id = v_call_option_type_id)
loop
avail_Date := resupply_Cursor.availability_date;
--getting the number of hours to save for each date in the cursor.
if resupply_cursor.availability_date = v_DST_Date THEN
hours := 23;
elsif resupply_cursor.availability_date = v_ST_Date THEN
hours := 25;
else
hours := 24;
end if;
stmt1 := 'INSERT INTO V_CALL_OPTION_AVAIL (
CONTRACT_ID,
AVAILABILITY_DATE,
HOUR,
CALL_OPTION_TYPE_ID,
MW,
TRADES_DEAL_NO,
PRICE,
ENTRY_DATE,
ENTRY_USER,
SUPERSEDED_DATE) (
SELECT
NVL(a.contract_id, :a) contract_id,
NVL(a.availability_date, to_date(:f,''mm/dd/yyyy'')) availability_date,
h.hour,
NVL(a.call_option_type_id, :b) call_option_type_id,
NVL(a.mw, 0) MW,
NVL(a.trades_deal_no, :c) POR_ID,
NVL(a.price, to_char(TO_NUMBER(0.00), ''9D99'')) PRICE,
NVL(a.entry_date, null),
NVL(a.entry_user, :d),
NVL(a.superseded_date, null)
FROM (SELECT level AS hour FROM dual CONNECT BY level <= :e
) h
LEFT JOIN v_call_option_avail a
ON a.availability_date >= (TRUNC(sysdate) + (h.hour - 1)/:e)
AND a.availability_date <= (TRUNC(sysdate) + (h.hour)/:e)
AND a.call_option_type_id = :b
AND contract_id = :a)';
Execute Immediate stmt1 using in v_Contract_Id, v_Call_Option_Type_Id, v_Trades_Deal_No, v_Entry_User, hours, avail_Date;
end loop;
END createRessuply;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/dynamic.htm#BHCHIHEJ
This does confirm that EXECUTE IMMEDIATE in the way you are using it is bind by position.