Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how do I fix the error: not all variables bound

Posted on 2011-09-28
2
Medium Priority
?
327 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:bschave2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 36719166
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
 
LVL 35

Expert Comment

by:johnsone
ID: 36719201
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question