Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how do I fix the error: not all variables bound

Posted on 2011-09-28
2
Medium Priority
?
329 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 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.
Suggested Courses

972 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