Solved

how do I fix the error: not all variables bound

Posted on 2011-09-28
2
318 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
  • 2
2 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 500 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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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…
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 video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now