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;

Open in new window

bschave2Asked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
As I recall, EXECUTE IMMEDIATE binds by position.  If I counted correctly, you have 10 bind variables in your statement but only 6 in the using statement.  You need to have the same number in both places.  Also, binding is by position, so they have to be listed in the order that they appear in the statement.  Just because you have :a as the last variable, doesn't mean it will use the first variable to substitute in, it uses the last one.
0
 
johnsoneSenior Oracle DBACommented:
Here is a link to the documentation.

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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.