Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    17

    Unanswered: 'Not all variables bound' error

    Hi,

    I have written this dynamic script, it runs through all the shifts in a fiscal week and reloads the data'

    However when I try to run it gives me an error:
    ORA-01008: not all variables bound

    As far as I can see all my variables have been assigned. If anyone knows where I have gone worng your help would be much appreciated. Thank You.


    code:

    Code:
    DECLARE 
    
    TYPE ty_shift_start IS TABLE OF VARCHAR2(18);
    l_partition VARCHAR2(50) := 'fis_wk_';
    l_spec_shift VARCHAR2(18) := '20100209 070000';
    l_fis_wk   VARCHAR2(6) := '201032';
    l_start_dt VARCHAR2(18);
    l_end_dt   VARCHAR2(18);
    l_sql_stmt VARCHAR2(32676);
    l_spec_load VARCHAR2(1) := 'Y';
    l_shift_start ty_shift_start;
    
    BEGIN
    
    l_partition := l_partition || l_fis_wk;
    
    IF l_spec_load = 'N' THEN 
    
    SELECT min(startdatetime)
    BULK COLLECT 
    INTO l_shift_start
    FROM wlos_owner.tbl_fiscal_calendar
    WHERE fiscalweek = l_fis_wk;
    
    
    FOR x IN 1 .. l_shift_start.count
    LOOP
    l_start_dt := l_shift_start(x);
    l_end_dt := to_char(to_date(l_start_dt,'yyyymmdd hh24miss')+0.5,'yyyymmdd hh24miss');
    
    l_sql_stmt := 'DELETE FROM wlos_owner.tbl_ct_step_by_shift WHERE SHIFT_DT = :l_spec_shift';
    EXECUTE IMMEDIATE l_sql_stmt;
    l_sql_stmt := 'EXEC wlos_owner.pkg_ctsuite.prc_get_custom_shift(:l_start_dt,:l_end_dt)'; 
    EXECUTE IMMEDIATE l_sql_stmt;
    
    COMMIT;
    END LOOP;
    
    ELSE 
    
    l_start_dt := l_spec_shift;
    l_end_dt := to_char(to_date(l_start_dt,'yyyymmdd hh24miss')+0.5,'yyyymmdd hh24miss');
    
    l_sql_stmt := 'DELETE FROM wlos_owner.tbl_ct_step_by_shift WHERE SHIFT_DT = :l_start_dt';
    EXECUTE IMMEDIATE l_sql_stmt;
    l_sql_stmt := 'EXEC wlos_owner.pkg_ctsuite.prc_get_custom_shift(:l_start_dt,:l_end_dt)'; 
    EXECUTE IMMEDIATE l_sql_stmt;
    
    END IF;
    END;

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by GerardMcL View Post
    As far as I can see all my variables have been assigned.
    Oh yeah? Where is that?

    Apart from the fact that you don't need dynamic SQL in your code, you do not bind variables: it is done with the USING clause of EXECUTE IMMEDIATE. Check examples in the manual.
    Code:
    l_sql_stmt := 'DELETE FROM wlos_owner.tbl_ct_step_by_shift WHERE SHIFT_DT = :l_spec_shift';
    EXECUTE IMMEDIATE l_sql_stmt;
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by GerardMcL View Post
    Code:
    l_sql_stmt := 'EXEC wlos_owner.pkg_ctsuite.prc_get_custom_shift(:l_start_dt,:l_end_dt)'; 
    EXECUTE IMMEDIATE l_sql_stmt;
    You are not assigning anything. As stated in PL/SQL User's Guide and Reference, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/:
    The string can also contain placeholders, arbitrary names preceded by a colon, for bind arguments. In this case, you specify which PL/SQL variables correspond to the placeholders with the INTO, USING, and RETURNING INTO clauses.
    Variable names in dynamic string are not taken into account, only their position. So, correctly you shall call it
    Code:
    EXECUTE IMMEDIATE l_sql_stmt USING l_start_dt, l_end_dt;
    Additionally, EXEC is not valid PL/SQL statement, you have to use anonymous block (BEGIN <statement(s)> END;) instead.

    Finally, the code you posted is static; there is no need to call it dynamically. So you should not use EXECUTE IMMEDIATE. If you have problems with missing grants, you shall grant them explicitly. Dynamic call is not the solution, it just moves the error from compile time to run time.

    [Edit: typo]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •