Solved

how do I fix the error: not all variables bound

Posted on 2011-09-28
2
321 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Schema creation in Oracle12c 6 48
Oracle Next Available Number 2 44
sort a spool into file output in oracle 1 30
pivot rows to columns 1 19
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…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

821 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